Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete records when certain records have duplicate column data | New Users to Excel | |||
How can I combine multiple csv files with different number of records? | Excel Discussion (Misc queries) | |||
How do I combine multiple records for a single item? | Excel Discussion (Misc queries) | |||
How to combine Excel 2002 files and remove duplicate records? | New Users to Excel | |||
Combine several records into one | Excel Discussion (Misc queries) |