Need macro to combine multiple rows into single row

S

supdawg

Well-known Member
Joined
Mar 18, 2007
Messages
608
Need some help in coming up with a macro which will take my data and combine rows of data if a duplicate order # is present in column A. I tried searching, but I had no luck with this.

Sample data (pre macro)

E2Queue

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 126px"><COL style="WIDTH: 165px"><COL style="WIDTH: 126px"><COL style="WIDTH: 126px"><COL style="WIDTH: 126px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Order #</TD><TD style="TEXT-ALIGN: center">Part</TD><TD style="TEXT-ALIGN: center">Sts</TD><TD style="TEXT-ALIGN: center">Qty</TD><TD style="TEXT-ALIGN: center">Subd For</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">532511842</TD><TD style="TEXT-ALIGN: center">H253H</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">XX106</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">532511842</TD><TD style="TEXT-ALIGN: center">XT213</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">532510083</TD><TD style="TEXT-ALIGN: center">D449C</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">532225674</TD><TD style="TEXT-ALIGN: center">5GTT7</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">532230096</TD><TD style="TEXT-ALIGN: center">2MMJP</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">532234205</TD><TD style="TEXT-ALIGN: center">5GTT7</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">532234890</TD><TD style="TEXT-ALIGN: center">UX932</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">532301335</TD><TD style="TEXT-ALIGN: center">N0WV7</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center">532472342</TD><TD style="TEXT-ALIGN: center">U185D</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">ZZSSA</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center">532472342</TD><TD style="TEXT-ALIGN: center">F491C</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">TT123</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center">532577181</TD><TD style="TEXT-ALIGN: center">5GTT7</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center">532577181</TD><TD style="TEXT-ALIGN: center">U217R</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center">532577181</TD><TD style="TEXT-ALIGN: center">Z1R4T</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">2</TD><TD></TD></TR></TBODY></TABLE>


And what it would look like after the macro is ran:

E2Queue

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 126px"><COL style="WIDTH: 149px"><COL style="WIDTH: 126px"><COL style="WIDTH: 126px"><COL style="WIDTH: 126px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center">Order #</TD><TD style="TEXT-ALIGN: center">Part</TD><TD style="TEXT-ALIGN: center">Sts</TD><TD style="TEXT-ALIGN: center">Qty</TD><TD style="TEXT-ALIGN: center">Subd For</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">532511842</TD><TD style="TEXT-ALIGN: center">H253H;XT213</TD><TD style="TEXT-ALIGN: center">B;B</TD><TD style="TEXT-ALIGN: center">1;1</TD><TD style="TEXT-ALIGN: center">XX106</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">532510083</TD><TD style="TEXT-ALIGN: center">D449C</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">532225674</TD><TD style="TEXT-ALIGN: center">5GTT7</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">532230096</TD><TD style="TEXT-ALIGN: center">2MMJP</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">532234205</TD><TD style="TEXT-ALIGN: center">5GTT7</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">532234890</TD><TD style="TEXT-ALIGN: center">UX932</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">532301335</TD><TD style="TEXT-ALIGN: center">N0WV7</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">1</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">532472342</TD><TD style="TEXT-ALIGN: center">U185D;F491C</TD><TD style="TEXT-ALIGN: center">B;B</TD><TD style="TEXT-ALIGN: center">1;1</TD><TD style="TEXT-ALIGN: center">ZZSSA;TT123</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center">532577181</TD><TD style="TEXT-ALIGN: center">5GTT7;U217R;Z1R4T</TD><TD style="TEXT-ALIGN: center">B;B;B</TD><TD style="TEXT-ALIGN: center">1;1;2</TD><TD></TD></TR></TBODY></TABLE>

Anything anyone could do to get me going in the right direction would be much appreciated.

TIA!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Will they always be contigious?

You could determine the range (which you can do with a countif and offset command) until a change and read in to an array

Then use the Join command with ";" as the delimeter, write that out to the top row in the range and delete the remaining rows in the range.

I would think that would be the fastest way. You are not doing any specific cell based transactions that way until you populate the combined data. The only other methods I can think of would involve cell based loops which can be CPU intensive

Let me know if that doesn't make sense and I will help you with the code.
 
Upvote 0
I have assumed that any repeated Order #s will be together in column G as in the sample. If not, presumably a 'sort first' section could be included in the code.

Test in a copy fo your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CombineRows()<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    LR = Range("G" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> r = LR <SPAN style="color:#00007F">To</SPAN> 3 <SPAN style="color:#00007F">Step</SPAN> -1<br>        <SPAN style="color:#00007F">With</SPAN> Cells(r, "G")<br>            <SPAN style="color:#00007F">If</SPAN> .Value = .Offset(-1).Value <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">For</SPAN> c = 1 <SPAN style="color:#00007F">To</SPAN> 4<br>                    <SPAN style="color:#00007F">If</SPAN> .Offset(, c).Value <> "" <SPAN style="color:#00007F">Then</SPAN><br>                        .Offset(-1, c).Value = .Offset(-1, c).Value _<br>                            & ";" & .Offset(, c).Value<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">Next</SPAN> c<br>                Rows(r).Delete<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Columns("H:K").AutoFit<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
Hi Peter SS, thank you for the code. It worked great.

Blade Hunter, thank you also for the reply as well.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
429
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
k3yn0t3
  • Solved
INDEX(MATCH()) pulling wrong row data & Macro to duplicate sheets with formulas
Replies
4
Views
381
Alex Blakenburg
A
V
  • Question
Issue with scraping with VBA
Replies
1
Views
508
John_w
J
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
349
esben
E

Forum statistics

Threads
1,214,954
Messages
6,122,462
Members
449,085
Latest member
ExcelError

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top