Somebody please help me!!

i have two sheets - both sheets having the customer name common.
the first sheet has one column with information regarding the customer...

eg:
Cust A - Contact A
Cust A - Address A
Cust A - ZipCode A
Cust B - Contact B
Cust B - Address B
Cust C - Contact C1
Cust C - Contact C2
Cust C - Address C
Cust C - Zipcode C

in my next sheet, i want the vlookup to work in such a way, that one
customer's details get aligned into one cell - like below

Cust A - Contact A,Address A,ZipCode A
Cust B - Contact B,Address B
Cust C - Contact C1,Contact C2,Address C,Zipcode C

Can somebody please help me....sometimes, one customer can have atleast 10
details...

Probably the easiest way of doing this; run this macro:
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

I didn't come up with this idea (it is a little beyond my skills set)!! I
found it on this DG a while back...

Regards,
Ryan---

--
RyGuy


"Fuzzy" wrote:

Somebody please help me!!

i have two sheets - both sheets having the customer name common.
the first sheet has one column with information regarding the customer...

eg:
Cust A - Contact A
Cust A - Address A
Cust A - ZipCode A
Cust B - Contact B
Cust B - Address B
Cust C - Contact C1
Cust C - Contact C2
Cust C - Address C
Cust C - Zipcode C

in my next sheet, i want the vlookup to work in such a way, that one
customer's details get aligned into one cell - like below

Cust A - Contact A,Address A,ZipCode A
Cust B - Contact B,Address B
Cust C - Contact C1,Contact C2,Address C,Zipcode C

Can somebody please help me....sometimes, one customer can have atleast 10
details...

oh My God Ryan...
when i saw ur code, it just went over my head and i dint bother trying...
since i was pretty desperate, i tried the macro just now!! and it worked like
GREAT!!!

Thanks for coming to my rescue!

"ryguy7272" wrote:

Probably the easiest way of doing this; run this macro:
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

I didn't come up with this idea (it is a little beyond my skills set)!! I
found it on this DG a while back...

Regards,
Ryan---

--
RyGuy


"Fuzzy" wrote:

Somebody please help me!!

i have two sheets - both sheets having the customer name common.
the first sheet has one column with information regarding the customer...

eg:
Cust A - Contact A
Cust A - Address A
Cust A - ZipCode A
Cust B - Contact B
Cust B - Address B
Cust C - Contact C1
Cust C - Contact C2
Cust C - Address C
Cust C - Zipcode C

in my next sheet, i want the vlookup to work in such a way, that one
customer's details get aligned into one cell - like below

Cust A - Contact A,Address A,ZipCode A
Cust B - Contact B,Address B
Cust C - Contact C1,Contact C2,Address C,Zipcode C

Can somebody please help me....sometimes, one customer can have atleast 10
details...