Combine duplicate records in one row - In desperate need!!!
 

I need to know how to combine duplicate records into one row. I have the
following data:

Cust#; Company Name; Part#; BillCode; PCode; Date
072552; Midwest Laboratories;66-WP2;9;B;12/30/05
072522; Midwest Laboratories;66-WP2;5;B;12/30/05
072522; Midwest Laboratories;60-IJ25NV;4;1/13/06

I want it to look like this, all on one line:

Cust#; Company Name; Part#; BillCode; PCode; Date
072552; Midwest
Laboratories;66-WP2;9;B;12/30/05;66-WP2;5;B;12/30/05;60-IJ25NV;4;1/13/06

I have the following macro that I found online, however, it's not really
doing what I'm looking for. It is only moving the part# column over into one
row but I also need the other columns to populate as well.

Sub Macro1()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim iRow As Integer
Dim iRow2 As Integer
Set ws = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
iRow = 2
iRow2 = 1
Do
If ws.Cells(iRow, 1) = ws.Cells(iRow - 1, 1) And _
ws.Cells(iRow, 2) = ws.Cells(iRow - 1, 2) Then
iCol = iCol + 1
ws2.Cells(iRow2, iCol) = ws.Cells(iRow, 3)
Else
iRow2 = iRow2 + 1
ws2.Cells(iRow2, 1) = ws.Cells(iRow, 1)
ws2.Cells(iRow2, 2) = ws.Cells(iRow, 2)
ws2.Cells(iRow2, 3) = ws.Cells(iRow, 3)
iCol = 3
End If
iRow = iRow + 1
Loop Until ws.Cells(iRow, 1) = ""
End Sub

Combine duplicate records in one row - In desperate need!!!
 

i put all your data in one column (i hope that is correct)

then i used the formula:
=IF(LEFT(A2,FIND(";",A2)-1)=LEFT(A1,FIND(";",A1)-1),B1&";"&A2,A2)

this would only work if a sort has already been done on the data.... so
that all cust# information lined up one after the other


"ceci" wrote:

I need to know how to combine duplicate records into one row. I have the
following data:

Cust#; Company Name; Part#; BillCode; PCode; Date
072552; Midwest Laboratories;66-WP2;9;B;12/30/05
072522; Midwest Laboratories;66-WP2;5;B;12/30/05
072522; Midwest Laboratories;60-IJ25NV;4;1/13/06

I want it to look like this, all on one line:

Cust#; Company Name; Part#; BillCode; PCode; Date
072552; Midwest
Laboratories;66-WP2;9;B;12/30/05;66-WP2;5;B;12/30/05;60-IJ25NV;4;1/13/06

I have the following macro that I found online, however, it's not really
doing what I'm looking for. It is only moving the part# column over into one
row but I also need the other columns to populate as well.

Sub Macro1()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim iRow As Integer
Dim iRow2 As Integer
Set ws = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
iRow = 2
iRow2 = 1
Do
If ws.Cells(iRow, 1) = ws.Cells(iRow - 1, 1) And _
ws.Cells(iRow, 2) = ws.Cells(iRow - 1, 2) Then
iCol = iCol + 1
ws2.Cells(iRow2, iCol) = ws.Cells(iRow, 3)
Else
iRow2 = iRow2 + 1
ws2.Cells(iRow2, 1) = ws.Cells(iRow, 1)
ws2.Cells(iRow2, 2) = ws.Cells(iRow, 2)
ws2.Cells(iRow2, 3) = ws.Cells(iRow, 3)
iCol = 3
End If
iRow = iRow + 1
Loop Until ws.Cells(iRow, 1) = ""
End Sub

Combine duplicate records in one row - In desperate need!!!
 

Thanks for the reply, however my data is actually in different columns like
the following:

Cust# Company Name Part# BILL PL Date
072552 Midwest 66-WP2 9 B 12/30/05
072552 Midwest 60-IJ25 4 0
4/13/06

Hopefully you can help me in creating a macro so that I can have it looking
like this

Cust# Company Name Part# BILL PL Date Part#2 BILL PL
Date
072552 Midwest 66-WP2 9 B 12/30/05 60-IJ25 4
0 4/13/06

Thanks in advance for the help =)
"ogerriz" wrote:

i put all your data in one column (i hope that is correct)

then i used the formula:
=IF(LEFT(A2,FIND(";",A2)-1)=LEFT(A1,FIND(";",A1)-1),B1&";"&A2,A2)

this would only work if a sort has already been done on the data.... so
that all cust# information lined up one after the other


"ceci" wrote:

I need to know how to combine duplicate records into one row. I have the
following data:

Cust#; Company Name; Part#; BillCode; PCode; Date
072552; Midwest Laboratories;66-WP2;9;B;12/30/05
072522; Midwest Laboratories;66-WP2;5;B;12/30/05
072522; Midwest Laboratories;60-IJ25NV;4;1/13/06

I want it to look like this, all on one line:

Cust#; Company Name; Part#; BillCode; PCode; Date
072552; Midwest
Laboratories;66-WP2;9;B;12/30/05;66-WP2;5;B;12/30/05;60-IJ25NV;4;1/13/06

I have the following macro that I found online, however, it's not really
doing what I'm looking for. It is only moving the part# column over into one
row but I also need the other columns to populate as well.

Sub Macro1()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim iRow As Integer
Dim iRow2 As Integer
Set ws = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
iRow = 2
iRow2 = 1
Do
If ws.Cells(iRow, 1) = ws.Cells(iRow - 1, 1) And _
ws.Cells(iRow, 2) = ws.Cells(iRow - 1, 2) Then
iCol = iCol + 1
ws2.Cells(iRow2, iCol) = ws.Cells(iRow, 3)
Else
iRow2 = iRow2 + 1
ws2.Cells(iRow2, 1) = ws.Cells(iRow, 1)
ws2.Cells(iRow2, 2) = ws.Cells(iRow, 2)
ws2.Cells(iRow2, 3) = ws.Cells(iRow, 3)
iCol = 3
End If
iRow = iRow + 1
Loop Until ws.Cells(iRow, 1) = ""
End Sub