Look up values in a column and return ALL corresponding row number
 

I'm sure this is an easy one...

I have 2 worksheets (S1 & S2). Column 2 in S2 contains a list of ID's that
I want to search in column 8 of S1 for and return the row number for every
matching cell.

There may be some ID's that have 0 matches, 1 match, 2 matches, ... n matches

Any help would be appreciated.

Look up values in a column and return ALL corresponding row number
 

Try this little macro:

Sub idiot()
n1 = Sheets("s1").Range("H65536").End(xlUp).Row
n2 = Sheets("s2").Range("B65536").End(xlUp).Row

For i = 1 To n2
v = Sheets("s2").Cells(i, "B").Value
k = 3
For j = 1 To n1
w = Sheets("s1").Cells(j, "H").Value
If v = w Then
Sheets("s2").Cells(i, k) = j
k = k + 1
End If
Next
Next
End Sub

with this data is s2:
1
2
3
4
5
6
7
8
9
10
and this data in s1:
20
7
12
15
1
1
3
15
13
11
14
12
7
13
11
4
7
8
16
14
3
20
14
7
15
13
6
6
14
12
the macro produced the following:

1 5 6
2
3 7 21
4 16
5
6 27 28
7 2 13 17 24
8 18
9
10


--
Gary's Student


"Ima Nidiot" wrote:

I'm sure this is an easy one...

I have 2 worksheets (S1 & S2). Column 2 in S2 contains a list of ID's that
I want to search in column 8 of S1 for and return the row number for every
matching cell.

There may be some ID's that have 0 matches, 1 match, 2 matches, ... n matches

Any help would be appreciated.

Look up values in a column and return ALL corresponding row nu
 

Perfect! Thank's a bunch.



"Gary''s Student" wrote:

Try this little macro:

Sub idiot()
n1 = Sheets("s1").Range("H65536").End(xlUp).Row
n2 = Sheets("s2").Range("B65536").End(xlUp).Row

For i = 1 To n2
v = Sheets("s2").Cells(i, "B").Value
k = 3
For j = 1 To n1
w = Sheets("s1").Cells(j, "H").Value
If v = w Then
Sheets("s2").Cells(i, k) = j
k = k + 1
End If
Next
Next
End Sub

with this data is s2:
1
2
3
4
5
6
7
8
9
10
and this data in s1:
20
7
12
15
1
1
3
15
13
11
14
12
7
13
11
4
7
8
16
14
3
20
14
7
15
13
6
6
14
12
the macro produced the following:

1 5 6
2
3 7 21
4 16
5
6 27 28
7 2 13 17 24
8 18
9
10


--
Gary's Student


"Ima Nidiot" wrote:

I'm sure this is an easy one...

I have 2 worksheets (S1 & S2). Column 2 in S2 contains a list of ID's that
I want to search in column 8 of S1 for and return the row number for every
matching cell.

There may be some ID's that have 0 matches, 1 match, 2 matches, ... n matches

Any help would be appreciated.