Lookup last pattern (ant list the row nº+interval)

Kishan · Aug 4, 2016

Hi,

My patterns in range N5:W5 I want lookup each pattern in column C:G and if find last in the column list row+interval in the range N3:W4

For example pattern 1|1|1|1|1 find in columns C+D+E+F+G in the rows 7 , 9, 14, 19, 21 & last in the row 24 so I want note the Row nº in cell N3 & N4 = 3 the interval show in cell H24 (in the same pattern row column H)
The same method applied to rest patterns

Example results

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

1

2

3

Row n

24

47

16

32

41

29

15

30

59

4

Interval

3

39

4

0

15

12

0

7

13

5

P1

P2

P3

P4

P5

Interval

1|1|1|1|1

1|1|1|1|X

1|1|1|1|2

1|1|1|X|1

1|1|1|X|X

1|1|1|X|2

1|1|1|2|1

1|1|1|2|X

1|1|1|2|2

1|1|X|1|1

6

P1

P2

P3

P4

P5

Interval

7

1

0

8

1

X

0

9

1

2

10

1

2

0

11

1

X

1

X

1

0

12

1

2

0

13

X

1

2

1

0

14

1

5

15

1

2

X

0

16

1

2

4

17

1

2

1

0

18

1

2

8

19

1

5

20

X

1

X

0

21

1

2

22

1

2

4

23

1

2

1

24

1

3

25

X

1

2

1

12

26

1

X

0

27

X

1

0

28

1

2

X

0

29

1

2

1

12

30

1

2

7

31

X

2

X

2

0

32

1

X

1

0

33

1

X

1

2

0

34

2

1

X

0

35

1

X

1

2

1

0

36

1

X

1

0

37

1

X

2

1

0

38

X

1

11

39

X

2

1

X

0

40

X

1

2

1

2

0

41

1

X

15

42

X

1

0

43

2

X

2

1

X

0

44

X

1

2

X

0

45

X

1

X

1

0

46

1

X

1

10

47

1

X

39

48

2

X

1

0

49

1

2

1

X

2

0

50

X

1

X

1

2

0

51

X

1

13

52

X

1

X

1

0

53

X

1

X

0

54

X

1

2

0

55

2

1

X

2

1

0

56

X

2

1

X

0

57

1

X

2

X

1

0

58

X

2

X

1

0

59

1

X

1

13

Row+Interval

Thank you in advance

Using Excel 2000
Regards,
Kishan

wideboydixon · Aug 4, 2016

Code:

Public Sub LookupLastPattern()

Dim rowIntervals As New Collection
Dim patternCell As Range
Dim lastRow As Long
Dim pattern As String
Dim currentValue As String
Dim thisRow As Long
Dim thisCol As Long

On Error Resume Next

lastRow = Cells(Rows.Count, 3).End(xlUp).Row
For thisRow = 7 To lastRow
    pattern = ""
    For thisCol = 3 To 7
        pattern = pattern & "|" & CStr(Cells(thisRow, thisCol))
    Next
    pattern = Mid$(pattern, 2)
    Err.Clear
    currentValue = rowIntervals.Item(pattern)
    If Err.Number <> 0 Then
        rowIntervals.Add "@", pattern
        currentValue = "@"
    End If
    
    If currentValue = "@" Then
        Cells(thisRow, 8).Value = 0
        rowIntervals.Remove pattern
        rowIntervals.Add CStr(thisRow) & ",0", pattern
    Else
        Cells(thisRow, 8).Value = thisRow - CLng(Split(currentValue, ",")(0))
        rowIntervals.Remove pattern
        rowIntervals.Add CStr(thisRow) & "," & CStr(Cells(thisRow, 8).Value), pattern
    End If
Next

For thisCol = 14 To 23
    Err.Clear
    currentValue = rowIntervals.Item(Cells(5, thisCol).Value)
    If Err.Number <> 0 Then
        Cells(3, thisCol).ClearContents
        Cells(4, thisCol).ClearContents
    Else
        Cells(3, thisCol) = Split(currentValue, ",")(0)
        Cells(4, thisCol) = Split(currentValue, ",")(1)
    End If
Next

End Sub

WBD

Rick Rothstein · Aug 4, 2016

Assuming you have less than 65,536 rows of data, here is another macro for you to consider...

Code:

[table="width: 500"]
[tr]
	[td]Sub LastPattern()
  Dim C As Long, LastRow As Long, Position As Long
  Dim Found As String, Combined As String
  Dim Data As Variant, Patterns As Variant, Result As Variant
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  ReDim Result(1 To 2, 1 To 10)
  Range("N3:W4").Clear
  Combined = Join(Application.Transpose(Evaluate(Replace("C7:C#&""|""&D7:D#&""|""&E7:E#&""|""&F7:F#&""|""&G7:G#&""@""&H7:H#&""@""&ROW(7:#)", "#", LastRow))), "@")
  Patterns = Range("N5:W5")
  For C = 1 To UBound(Patterns, 2)
    Position = InStrRev(Combined, Patterns(1, C))
    If Position Then
    Data = Split(Mid(Combined, Position), "@")
      Result(1, C) = Data(2)
      Result(2, C) = Data(1)
    End If
  Next
  Range("N3").Resize(2, 10) = Result
End Sub[/td]
[/tr]
[/table]

Kishan · Aug 5, 2016

wideboydixon said:

Code:

Public Sub LookupLastPattern()

Dim rowIntervals As New Collection
Dim patternCell As Range
Dim lastRow As Long
Dim pattern As String
Dim currentValue As String
Dim thisRow As Long
Dim thisCol As Long

On Error Resume Next

lastRow = Cells(Rows.Count, 3).End(xlUp).Row
For thisRow = 7 To lastRow
    pattern = ""
    For thisCol = 3 To 7
        pattern = pattern & "|" & CStr(Cells(thisRow, thisCol))
    Next
    pattern = Mid$(pattern, 2)
    Err.Clear
    currentValue = rowIntervals.Item(pattern)
    If Err.Number <> 0 Then
        rowIntervals.Add "@", pattern
        currentValue = "@"
    End If
    
    If currentValue = "@" Then
        Cells(thisRow, 8).Value = 0
        rowIntervals.Remove pattern
        rowIntervals.Add CStr(thisRow) & ",0", pattern
    Else
        Cells(thisRow, 8).Value = thisRow - CLng(Split(currentValue, ",")(0))
        rowIntervals.Remove pattern
        rowIntervals.Add CStr(thisRow) & "," & CStr(Cells(thisRow, 8).Value), pattern
    End If
Next

For thisCol = 14 To 23
    Err.Clear
    currentValue = rowIntervals.Item(Cells(5, thisCol).Value)
    If Err.Number <> 0 Then
        Cells(3, thisCol).ClearContents
        Cells(4, thisCol).ClearContents
    Else
        Cells(3, thisCol) = Split(currentValue, ",")(0)
        Cells(4, thisCol) = Split(currentValue, ",")(1)
    End If
Next

End Sub

WBD

Brilliant!! WBD, it is resulting as treat.

I appreciate your kind support

Regards,
Kishan

Kishan · Aug 5, 2016

Rick Rothstein said:
Assuming you have less than 65,536 rows of data, here is another macro for you to consider...

Hi,

Rick Rothstein Thanks. I appreciate your help.

Code works perfect with sample data when I tried with original data it stop at the line below and highlights the line in colour yellow

Code:

 Combined = Join(Application.Transpose(Evaluate(Replace("C7:C#&""|""&D7:D#&""|""&E7:E#&""|""&F7:F#&""|""&G7:G#&""@""&H7:H#&""@""&ROW(7:#)", "#", LastRow))), "@")

Please coluld you try the code after multiplying rows and see does the macro reproduce error with you. May be it is small adjustment to do.

Sample data there are 53 rows (53*1000 = 53000 + 6 = 53006) copy 53 rows, select range C60:G53006 and paste them then run the macro.

Thank you

Regards,
Kishan

Rick Rothstein · Aug 5, 2016

Kishan said:
Sample data there are 53 rows (53*1000 = 53000 + 6 = 53006) copy 53 rows, select range C60:G53006 and paste them then run the macro.

I took your sample data and repeatedly copied it to create a table which started at C7 and ended at G53006 and the code I posted worked fine for that enlarged data set.... so I cannot recreate your problem here. I notice you wrote the start as of your data as C60, not C7 as your sample data showed. Is C60 the real start of your data? If so, then perhaps you have values in cells C7:G59 that are affecting my code in some way. Assuming your data really starts at row 60, see if this modification to my code works for that setup (I still assumed your patterns were in cells N5:W5)...

Code:

[table="width: 500"]
[tr]
	[td]Sub LastPattern()
  Dim C As Long, LastRow As Long, Position As Long
  Dim Found As String, Combined As String
  Dim Data As Variant, Patterns As Variant, Result As Variant
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  ReDim Result(1 To 2, 1 To 10)
  Range("N3:W4").Clear
  Combined = Join(Application.Transpose(Evaluate(Replace("C60:C#&""|""&D60:D#&""|""&E60:E#&""|""&F60:F#&""|""&G60:G#&""@""&H60:H#&""@""&ROW(60:#)", "#", LastRow))), "@")
  Patterns = Range("N5:W5")
  For C = 1 To UBound(Patterns, 2)
    Position = InStrRev(Combined, Patterns(1, C))
    If Position Then
    Data = Split(Mid(Combined, Position), "@")
      Result(1, C) = Data(2)
      Result(2, C) = Data(1)
    End If
  Next
  Range("N3").Resize(2, 10) = Result
End Sub[/td]
[/tr]
[/table]

Kishan · Aug 5, 2016

Rick Rothstein said:
I took your sample data and repeatedly copied it to create a table which started at C7 and ended at G53006 and the code I posted worked fine for that enlarged data set.... so I cannot recreate your problem here. I notice you wrote the start as of your data as C60, not C7 as your sample data showed. Is C60 the real start of your data? If so, then perhaps you have values in cells C7:G59 that are affecting my code in some way. Assuming your data really starts at row 60, see if this modification to my code works for that setup (I still assumed your patterns were in cells N5:W5)...

Hi,

Rick Rothstein, my data start in C7 I said (multiplying rows) copy 53 sample data rows from C60 to down
Tried modified code using data starts as C60 but code stop at the same line

As post#3 code was working perfect with sample data to I wanted to check now till what end of the row code work, found It works with last row 5467 that give me impression (your code is nice) but as I am not update with version Excel 2000 does not support transpose row more than 5460 it permits only max rows 5460 (5467-7 as starting row)

Rick, I am sorry to making you work twice it is not your code it is version problem which I am using

Thank you once again for your kind help

Regards,
Kishan

Rick Rothstein · Aug 5, 2016

Kishan said:
Rick, I am sorry to making you work twice it is not your code it is version problem which I am using...

What version are you using?

Kishan · Aug 5, 2016

Rick Rothstein said:
What version are you using?

Hi,

Rick Rothstein, it is specified at the bottom of post#1 Using Excel 2000

Thank you

Regards,
Kishan

Rick Rothstein · Aug 5, 2016

Kishan said:
Rick Rothstein, it is specified at the bottom of post#1 Using Excel 2000

I completely missed that

Lookup last pattern (ant list the row nº+interval)

Kishan

Well-known Member

Excel Facts

wideboydixon

Well-known Member

Rick Rothstein

MrExcel MVP

Kishan

Well-known Member

Kishan

Well-known Member

Rick Rothstein

MrExcel MVP

Kishan

Well-known Member

Rick Rothstein

MrExcel MVP

Kishan

Well-known Member

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Lookup last pattern (ant list the row nº+interval)

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock