Look up particular row information if cell value matches specified value

[spill-the-beans]

Hi all

I have two worksheets, one named Data and one named Index. In column G of Data, there is an index number. In column G of Index, there is a list of special index numbers.

In columns A:F there is information about each special index numbers.

What I need is in Data, if the index number of column G exactly matches the value of a special index number in Index Column G, put the column A:F information for that row in the row with matching index number in Data.

Data Worksheet

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]index number
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]26
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]256
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]280
[/TD]
[/TR]
</tbody>[/TABLE]

Index Worksheet

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]special numbers
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]26
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]256
[/TD]
[/TR]
</tbody>[/TABLE]


What I need in Data Worksheet

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]index number
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]26
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]256
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]280
[/TD]
[/TR]
</tbody>[/TABLE]

I need to be able to put the formulas in A2 to F2 and just drag the formula down all ~5000 cells of Data, and only need the information put into the ~ 20 special rows, determined by the index number in column G.

Any help would be very much appreciated

 

[mumps]

Try:

Sub Test()
    Dim bottomG1 As Integer
    bottomG1 = Sheets("Data").Range("G" & Rows.Count).End(xlUp).Row
    Dim bottomG2 As Integer
    bottomG2 = Sheets("Index").Range("G" & Rows.Count).End(xlUp).Row
    Dim rng1 As Range
    Dim rng2 As Range
    For Each rng1 In Sheets("Data").Range("G2:G" & bottomG1)
        For Each rng2 In Sheets("Index").Range("G2:G" & bottomG2)
            If rng2 = rng1 Then
                  Sheets("Index").Range("A" & rng2.Row).Resize(, 6).Copy Sheets("Data").Range("A" & rng1.Row)
            End If
        Next rng2
    Next rng1
End Sub

 

[spill-the-beans]

Works perfectly - such a quick solution too!

I have a similar worksheet, I don't know if you would be able to help with that one too. There is a start index and an end index number in columns G and H respectively of the Index worksheet.

In the Data worksheet, I need the row information if the Data index number in column G is equal to or between the start and end index numbers in columns G and H in the index tab.

So if the start index was 1 and the end index was 5 in Index G2 and H2, in the Data worksheet, I would need each row with an index number of 1, 2, 3, 4 and 5 in the G column to have the A:F row Index information for the Index row 2.

Is that possible as well?

 

[mumps]

I'm glad it worked out for you. In regards to your second question, could you post samples as you did for the first question. I can't promise a solution, but it would be easier to understand.

 

[spill-the-beans]

[TABLE="width: 500"]
<tbody>[TR]
[TD]

[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]header row
[/TD]
[TD]start number
[/TD]
[TD]end number
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]7
[/TD]
[TD]9
[/TD]
[/TR]
</tbody>[/TABLE]

That would be in the Index worksheet, the start and end numbers in G and H (red) show what index numbers in the Data worksheet need which information in A:F (blue and purple).

So the Data worksheet needs to look like:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]header
[/TD]
[TD]header
[/TD]
[TD]header
[/TD]
[TD]header
[/TD]
[TD]header
[/TD]
[TD]header
[/TD]
[TD]index number
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]

Is that clear how I used the colours, or do I need to try and explain any more?

 

[mumps]

Change:

If rng2 = rng1 Then

to:

If rng1 >= rng2 And rng1 <= rng2.Offset(0, 1) Then