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

	A	B	C	D	E	F	G
1	header row	header row	header row	header row	header row	header row	index number
2							26
3							100
4							256
5							280

<tbody>
</tbody>

Index Worksheet

	A	B	C	D	E	F	G
1	header row	header row	header row	header row	header row	header row	special numbers
2	1	1	0	1	0	0	26
3	1	0	0	1	0	1	256

<tbody>
</tbody>

What I need in Data Worksheet

	A	B	C	D	E	F	G
1	header row	header row	header row	header row	header row	header row	index number
2	1	1	0	1	0	0	26
3							100
4	1	0	0	1	0	1	256
5							280

<tbody>
</tbody>

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]

	A	B	C	D	E	F	G	H
1	header row	header row	header row	header row	header row	header row	start number	end number
2	1	0	1	0	0	1	3	5
3	1	0	0	1	1	1	7	9

<tbody>
</tbody>

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:

	A	B	C	D	E	F	G
1	header	header	header	header	header	header	index number
2							2
3	1	0	1	0	0	1	3
4	1	0	1	1	0	1	4
5	1	0	1	1	0	1	5
6							6
7	1	0	0	1	1	1	7
8	1	0	0	1	1	1	8
9	1	0	0	1	1	1	9
10							10

<tbody>
</tbody>

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