Basically I need to compare order number and if it matches on the outstanding sheet, add it to sheet 1.
For example.
sheet 1 contains: OR1545 OR1687 OR898 OR0142
The outstanding sheet contains.
OR898 6684D8D OR0142 6544D OR0142 8489DD OR0142 897EEA
So the sheet1 will check the outstanding sheet and add the items to the OR column.
If there are more then 1 item to goes to the next column.
So the final output on sheet 1 would be:
OR0142 615 6544D 897EEA OR898 645DD 6684D8D
Here is using the formula:
=IFERROR(TRANSPOSE(INDEX(Sheet2!$B$1:$B$5,SMALL(IF(Sheet2!$A$1:$A$5=$A2,ROW(Sheet2!$A$1:$A$5)),COLUMN(A$1)))),"")
Above is an Array Formula entered using Ctrl+Shft+Enter in Cell C2 and copied to the remaining cells of interest.
Assuming your data is organized like below:
Sheet1:
Outstanding sheet:
Here is the code:
Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim myfilters
Dim myfilter
Dim rng1 As Range, rng2 As Range
Set ws1 = Sheet3
Set ws2 = Sheet2
Application.ScreenUpdating = False
With ws1
Set rng1 = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp))
myfilters = rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1)
End With
With ws2
.AutoFilterMode = False
Set rng2 = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp))
For Each myfilter In myfilters
rng2.AutoFilter Field:=1, Criteria1:=myfilter
rng2.Offset(1, 1).SpecialCells(xlCellTypeVisible).Copy
rng1.Find(myfilter, rng1(1)).Offset(0, 2).PasteSpecial xlPasteValuesAndNumberFormats, , , True
.AutoFilterMode = False
Next
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Not tested though.
So test it in a duplicate data for safety.
Hope this helps.
