Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Vlookup or other function can assist?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Vlookup or other function can assist?
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 -------- try that.. all the best..and incase u do get an answer to ur question, let me know. "freeguy" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please assist... | Excel Worksheet Functions | |||
Can't remember....please assist | Excel Discussion (Misc queries) | |||
Formula Assist | Excel Discussion (Misc queries) | |||
Rosters - is there a function or formula to assist with this | Excel Discussion (Misc queries) | |||
Please assist | Excel Worksheet Functions |