Find multiple instances

[BennyH]

Hi,

this is driving me nuts. I want lookup 1 record-Op Number from a sheet and combine multiple instances onto the next sheet. Tried using Vlookup but only returns the first instance. Example:

Op Num <tbody> </tbody><colgroup><col></colgroup>	ID
2TT3 <tbody> </tbody><colgroup><col></colgroup>	33804
2TT3 <tbody> </tbody><colgroup><col></colgroup>	33805

<tbody>
</tbody>

Op Num ID1 ID2 2TT3 33804 33805 <tbody> </tbody>
Any help would be appreciated.

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[jorismoerings]

Hi,

Take a look at this if this helps you:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E
1	Op Num	ID
2	2TT3	33804
3	2TT3	33805
4	2TT4	12345
5	2TT4	23456
6	2TT3	33806
7	2TT3	33807
8
9
10
11
12	Op Num	ID1	ID2	ID3	ID4
13	2TT3	33804	33805	33806	33807
Sheet1

Cell Formulas
Range	Formula
B13	{=INDEX($B$2:$B$7,SMALL(IF($A$13=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),RIGHT(B12,1)))}
C13	{=INDEX($B$2:$B$7,SMALL(IF($A$13=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),RIGHT(C12,1)))}
D13	{=INDEX($B$2:$B$7,SMALL(IF($A$13=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),RIGHT(D12,1)))}
E13	{=INDEX($B$2:$B$7,SMALL(IF($A$13=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),RIGHT(E12,1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[daveronan]

Hi,

This should work if you need a VBA answer just replace the sheet numbers (Sheet1 = your data sheet ,Sheet2 = Output sheet) with your sheet names. I assuming your OP No's are in column "A".

Public Sub instances()
Dim inst_1 As Integer, lrow As Integer, insert_row As Integer, val As Integer, x As Integer, lrow1 As Integer
lrow = Sheets("sheet1").Range("a1").End(xlDown).Row
If Sheets("sheet2").Cells(1, 1).Value = "" Then
lrow1 = Sheets("sheet2").Cells(1, 1).Row - 1
Else
lrow1 = Sheets("sheet2").Cells(1, 1).End(xlDown).Row
End If
input1 = InputBox("Op Number")
For x = 1 To lrow
If Sheets("sheet1").Cells(x, 1).Value = input1 Then
op = input1
End If
Next x

If op = "" Then
MsgBox "No Op Number Found!"
Exit Sub
Else
rows_no = WorksheetFunction.CountIf(Sheets("Sheet1").Range("a2:a" & lrow), op)

Dim array1()
ReDim array1(rows_no - 1)

insert_row = 0

For i = 0 To lrow
If Sheets("Sheet1").Cells(i + 1, 1).Value = op Then
array1(insert_row) = Sheets("sheet1").Cells(i + 1, 2).Value
insert_row = insert_row + 1
End If
Next

insert_row = 0

Sheets("sheet2").Cells(lrow1 + 1, 1).Value = op

For i = 1 To rows_no
Sheets("sheet2").Cells(lrow1 + 1, i + 1).Value = array1(insert_row)
insert_row = insert_row + 1
Next
End If
End Sub

 

[daveronan]

There is a bug in the last script use this if you need to do this with multiple Op No.'s

Public Sub instances()
Dim inst_1 As Integer, lrow As Integer, insert_row As Integer, val As Integer, x As Integer, lrow1 As Integer
lrow = Sheets("sheet1").Range("a1").End(xlDown).Row
If Sheets("sheet2").Cells(1, 1).Value = "" Then
lrow1 = Sheets("sheet2").Cells(1, 1).Row - 1
ElseIf Sheets("sheet2").Cells(2, 1).Value = "" Then
lrow1 = Sheets("sheet2").Cells(1, 1).End(xlUp).Row
Else
lrow1 = Sheets("sheet2").Cells(1, 1).End(xlDown).Row
End If
input1 = InputBox("Op Number")
For x = 1 To lrow
If Sheets("sheet1").Cells(x, 1).Value = input1 Then
op = input1
End If
Next x

If op = "" Then
MsgBox "No Op Number Found!"
Exit Sub
Else
rows_no = WorksheetFunction.CountIf(Sheets("Sheet1").Range("a2:a" & lrow), op)

Dim array1()
ReDim array1(rows_no - 1)

insert_row = 0

For i = 0 To lrow
If Sheets("Sheet1").Cells(i + 1, 1).Value = op Then
array1(insert_row) = Sheets("sheet1").Cells(i + 1, 2).Value
insert_row = insert_row + 1
End If
Next

insert_row = 0

Sheets("sheet2").Cells(lrow1 + 1, 1).Value = op

For i = 1 To rows_no
Sheets("sheet2").Cells(lrow1 + 1, i + 1).Value = array1(insert_row)
insert_row = insert_row + 1
Next
End If
End Sub

 

[BennyH]

Hi,

Take a look at this if this helps you:

Excel 2016 (Windows) 64 bit

	A	B	C	D	E
1	Op Num	ID
2	2TT3	33804
3	2TT3	33805
4	2TT4	12345
5	2TT4	23456
6	2TT3	33806
7	2TT3	33807
8
9
10
11
12	Op Num	ID1	ID2	ID3	ID4
13	2TT3	33804	33805	33806	33807

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B13 {=INDEX($B$2:$B$7,SMALL(IF($A$13=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),RIGHT(B12,1)))} C13 {=INDEX($B$2:$B$7,SMALL(IF($A$13=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),RIGHT(C12,1)))} D13 {=INDEX($B$2:$B$7,SMALL(IF($A$13=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),RIGHT(D12,1)))} E13 {=INDEX($B$2:$B$7,SMALL(IF($A$13=$A$2:$A$7,ROW($A$2:$A$7)-ROW($A$2)+1),RIGHT(E12,1)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks but it didn't quite work-appreciate the reply. I found an old example from Chandoo, that works using a helper column.