Using Vlookup or other function can assist?

[freeguy]

Does vlookup return with multiple value?

Excel sheet 1 have data in column A as Airwaybill number
Excel sheet 2 have data in column A as Airwaybill number and in column B
Order number.

E.g.
Excel 1
Airwaybill number
1
2
3

Excel 2
Airwaybill number Order number
1 1
2
3
4
5
2 1
2
3
4

But in a single Airwaybill number, there are multiple order number.
Normally when vlookup, the return value is always single value and/or the
first row of the airways. In what way can i have vlookup to reflect the
multiple order number of the single airwaybill number? Or is there any
function in Excel that can be use to perform this?

Any advice will be greatly appreciate.
Thank you.

 

[Fuzzy]

Hi..
I had a similar problem where i wanted the result to look like
Column A Column B
Airway bill num Order 1, Order 2, etc...

forget the vlook up..coz nobody helped with that... try this macro and
hopefully, it would solve ur problem... do this macro on your first sheet and
ensure the first column is the airway bill num and column B contains the
order number....

Sub ConcatData()

Dim X As Double

Dim DataArray(5000, 2) As Variant

Dim NbrFound As Double

Dim Y As Double

Dim Found As Integer

Dim NewWks As Worksheet



Cells(1, 1).Select

Let X = ActiveCell.Row

Do While True

If Len(Cells(X, 1).Value) = Empty Then

Exit Do

End If

If NbrFound = 0 Then

NbrFound = 1

DataArray(1, 1) = Cells(X, 1)

DataArray(1, 2) = Cells(X, 2)

Else

For Y = 1 To NbrFound

Found = 0

If DataArray(Y, 1) = Cells(X, 1).Value Then

DataArray(Y, 2) = DataArray(Y, 2) & ", " & Cells(X, 2)

Found = 1

Exit For

End If

Next

If Found = 0 Then

NbrFound = NbrFound + 1

DataArray(NbrFound, 1) = Cells(X, 1).Value

DataArray(NbrFound, 2) = Cells(X, 2).Value

End If

End If

X = X + 1

Loop



Set NewWks = Worksheets.Add

NewWks.Name = "SummarizedData"

Cells(1, 1).Value = "Names"

Cells(1, 2).Value = "Results"

X = 2

For Y = 1 To NbrFound

Cells(X, 1).Value = DataArray(Y, 1)

Cells(X, 2).Value = DataArray(Y, 2)

X = X + 1

Next

Beep

MsgBox ("Summary is done!")

End Sub