Find multiple instances

B

BennyH

New Member
Joined
Apr 27, 2015
Messages
11
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:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD]Op Num[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD]2TT3[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]33804[/TD]
[/TR]
[TR]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD]2TT3[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]33805[/TD]
[/TR]
</tbody>[/TABLE]

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


[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Op Num[/TD]
[TD]ID1[/TD]
[TD]ID2[/TD]
[/TR]
[TR]
[TD]2TT3[/TD]
[TD]33804[/TD]
[TD]33805[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]
Any help would be appreciated.
[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Hi,

Take a look at this if this helps you:


Excel 2016 (Windows) 64 bit
ABCDE
1Op NumID
22TT333804
32TT333805
42TT412345
52TT423456
62TT333806
72TT333807
8
9
10
11
12Op NumID1ID2ID3ID4
132TT333804338053380633807
Sheet1
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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
Click to expand...
 
Upvote 0
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
Click to expand...
 
Upvote 0
jorismoerings said:
Hi,

Take a look at this if this helps you:

Excel 2016 (Windows) 64 bit
ABCDE
Op NumID
2TT3
2TT3
2TT4
2TT4
2TT3
2TT3
Op NumID1ID2ID3ID4
2TT3

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]33804[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]33805[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12345[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]23456[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]33806[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]33807[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]33804[/TD]
[TD="align: right"]33805[/TD]
[TD="align: right"]33806[/TD]
[TD="align: right"]33807[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B13[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C13[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D13[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E13[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Thanks but it didn't quite work-appreciate the reply. I found an old example from Chandoo, that works using a helper column.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Excel VBA - Check For Duplicates and Return the Count on a Large Data Set
Replies
4
Views
262
Kaper
Kaper
N
  • Question Question
Using formula to look up data where the data tab has the required data listed multiple times
Replies
1
Views
497
mumps
mumps
A
  • Question Question
Xlookup, Vlookup and/or VBA for finding, matching and inputting data to separate sheet
Replies
0
Views
822
AlfredDB
A
Q
  • Question Question
Macros help on multiple files to be created from a set of large data
Replies
0
Views
486
questforexcel
Q
J
  • Locked
  • Question Question
Vlookup from multiselect data validation list
Replies
2
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top