combine multiple rows to one row with blanks in some reference columns

J

JSWAIN

New Member
Joined
Jun 4, 2014
Messages
5
Hi, I scanned several other posts that deal with combining multiple rows of related data into one row but have not seen one that matches my situation. My experience level with excel only includes some basics like vlookup, match and recorded macros, although I have used vba code for a macro that was written by someone else where it met my needs.
I have a parts list for a PCB that has many different parts. Some of the parts are used multiple times across different reference designations("Reference" column in the file). I have shown a desired format and the original format below. Since the item number has blanks across the reference designators I haven't found a solution that would work for me. Any help would be appreciated.


Desired output:
[TABLE="width: 513"]
<COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 283pt; mso-width-source: userset; mso-width-alt: 13787" width=377><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" width=41><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><TBODY>[TR]
[TD="class: xl65, width: 30, bgcolor: #c5d9f1"]Item[/TD]
[TD="class: xl65, width: 50, bgcolor: #c5d9f1"]Quantity[/TD]
[TD="class: xl66, width: 377, bgcolor: #c5d9f1"]Reference[/TD]
[TD="class: xl67, width: 41, bgcolor: #c5d9f1"]Value[/TD]
[TD="class: xl65, width: 66, bgcolor: #c5d9f1"]TOLERANCE[/TD]
[TD="class: xl65, width: 54, bgcolor: #c5d9f1"]VOLTAGE[/TD]
[TD="class: xl65, width: 62, bgcolor: #c5d9f1"]Internal PN[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]1[/TD]
[TD="class: xl68, bgcolor: transparent"]18[/TD]
[TD="class: xl69, width: 377, bgcolor: transparent"]C1,C2,C3,C4,C7,C8,C13,C14,C15,C18,C21,C22,C25,C28,C29,C32,C37,C38,C39[/TD]
[TD="class: xl70, bgcolor: transparent"]0.10uF[/TD]
[TD="class: xl71, bgcolor: transparent"]10%[/TD]
[TD="class: xl68, bgcolor: transparent"]25V[/TD]
[TD="class: xl68, bgcolor: transparent"]123456[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]2[/TD]
[TD="class: xl68, bgcolor: transparent"]10[/TD]
[TD="class: xl69, width: 377, bgcolor: transparent"]C5,C9,C11,C16,C19,C23,C26,C30,C33,C35[/TD]
[TD="class: xl70, bgcolor: transparent"]150pF[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"]500V[/TD]
[TD="class: xl68, bgcolor: transparent"]123457[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl68, bgcolor: transparent"]4[/TD]
[TD="class: xl69, width: 377, bgcolor: transparent"]C6,C10,C12,C445[/TD]
[TD="class: xl70, bgcolor: transparent"]150pF[/TD]
[TD="class: xl71, bgcolor: transparent"]5%[/TD]
[TD="class: xl68, bgcolor: transparent"]50V[/TD]
[TD="class: xl68, bgcolor: transparent"]123458[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]4[/TD]
[TD="class: xl68, bgcolor: transparent"]7[/TD]
[TD="class: xl69, width: 377, bgcolor: transparent"]C17,C20,C24,C27,C31,C34,C36[/TD]
[TD="class: xl70, bgcolor: transparent"]150pF[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"]50V[/TD]
[TD="class: xl68, bgcolor: transparent"]123459[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]5[/TD]
[TD="class: xl68, bgcolor: transparent"]3[/TD]
[TD="class: xl69, width: 377, bgcolor: transparent"]C43,C139,C144[/TD]
[TD="class: xl70, bgcolor: transparent"]22pF[/TD]
[TD="class: xl71, bgcolor: transparent"]5%[/TD]
[TD="class: xl68, bgcolor: transparent"]200V[/TD]
[TD="class: xl68, bgcolor: transparent"]123460[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]6[/TD]
[TD="class: xl68, bgcolor: transparent"]1[/TD]
[TD="class: xl69, width: 377, bgcolor: transparent"]C44[/TD]
[TD="class: xl70, bgcolor: transparent"]820pF[/TD]
[TD="class: xl71, bgcolor: transparent"]5%[/TD]
[TD="class: xl68, bgcolor: transparent"]50V[/TD]
[TD="class: xl68, bgcolor: transparent"]123461[/TD]
[/TR]
</TBODY>[/TABLE]



Original output:
[TABLE="width: 445"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY>[TR]
[TD="class: xl68, width: 64, bgcolor: #c5d9f1"]Item[/TD]
[TD="class: xl68, width: 64, bgcolor: #c5d9f1"]Quantity[/TD]
[TD="class: xl69, width: 135, bgcolor: #c5d9f1"]Reference[/TD]
[TD="class: xl70, width: 51, bgcolor: #c5d9f1"]Value[/TD]
[TD="class: xl68, width: 98, bgcolor: #c5d9f1"]TOLERANCE[/TD]
[TD="class: xl68, width: 86, bgcolor: #c5d9f1"]VOLTAGE[/TD]
[TD="class: xl68, width: 94, bgcolor: #c5d9f1"]Internal PN[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]1[/TD]
[TD="class: xl71, bgcolor: transparent"]18[/TD]
[TD="class: xl72, width: 135, bgcolor: transparent"]C1,C2,C3,C4,C7,C8,C13,[/TD]
[TD="class: xl73, bgcolor: transparent"]0.10uF[/TD]
[TD="class: xl74, bgcolor: transparent"]10%[/TD]
[TD="class: xl71, bgcolor: transparent"]25V[/TD]
[TD="class: xl71, bgcolor: transparent"]123456[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl72, width: 135, bgcolor: transparent"]C14,C15,C18,C21,C22,C25,[/TD]
[TD="class: xl73, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl72, width: 135, bgcolor: transparent"]C28,C29,C32,C37,C38,C39,[/TD]
[TD="class: xl73, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]2[/TD]
[TD="class: xl71, bgcolor: transparent"]10[/TD]
[TD="class: xl72, width: 135, bgcolor: transparent"]C5,C9,C11,C16,C19,C23,[/TD]
[TD="class: xl73, bgcolor: transparent"]150pF[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"]500V[/TD]
[TD="class: xl71, bgcolor: transparent"]123457[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl72, width: 135, bgcolor: transparent"]C26,C30,C33,C35[/TD]
[TD="class: xl73, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]3[/TD]
[TD="class: xl71, bgcolor: transparent"]4[/TD]
[TD="class: xl72, width: 135, bgcolor: transparent"]C6,C10,C12,C445[/TD]
[TD="class: xl73, bgcolor: transparent"]150pF[/TD]
[TD="class: xl74, bgcolor: transparent"]5%[/TD]
[TD="class: xl71, bgcolor: transparent"]50V[/TD]
[TD="class: xl71, bgcolor: transparent"]123458[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]4[/TD]
[TD="class: xl71, bgcolor: transparent"]7[/TD]
[TD="class: xl72, width: 135, bgcolor: transparent"]C17,C20,C24,C27,C31,C34,[/TD]
[TD="class: xl73, bgcolor: transparent"]150pF[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"]50V[/TD]
[TD="class: xl71, bgcolor: transparent"]123459[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl72, width: 135, bgcolor: transparent"]C36[/TD]
[TD="class: xl73, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]5[/TD]
[TD="class: xl71, bgcolor: transparent"]3[/TD]
[TD="class: xl72, width: 135, bgcolor: transparent"]C43,C139,C144[/TD]
[TD="class: xl73, bgcolor: transparent"]22pF[/TD]
[TD="class: xl74, bgcolor: transparent"]5%[/TD]
[TD="class: xl71, bgcolor: transparent"]200V[/TD]
[TD="class: xl71, bgcolor: transparent"]123460[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]6[/TD]
[TD="class: xl71, bgcolor: transparent"]1[/TD]
[TD="class: xl72, width: 135, bgcolor: transparent"]C44[/TD]
[TD="class: xl73, bgcolor: transparent"]820pF[/TD]
[TD="class: xl74, bgcolor: transparent"]5%[/TD]
[TD="class: xl71, bgcolor: transparent"]50V[/TD]
[TD="class: xl71, bgcolor: transparent"]123461[/TD]
[/TR]
</TBODY>[/TABLE]
 
Sort by date Sort by votes
The Line items should be: 1 2 3 4 5 6 7. They are 1 2 3 4 56 6 7 and some of the data on line 56 is doubled up, like the qty, value, etc...
 
Last edited:
Upvote 0

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
You must log in or register to reply here.

Similar threads

H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
S
  • Solved
Tranpose with Editing
Replies
7
Views
1K
Rick Rothstein
Rick Rothstein
N
  • Question Question
How to Create Multiple Reports from One Sheet
Replies
1
Views
2K
Logit
Logit
N
  • Question Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff
M
  • Question Question
Index Match formula with multiple criteria
Replies
5
Views
777
DanteAmor
DanteAmor

Forum statistics

Threads
1,222,752
Messages
6,168,007
Members
452,160
Latest member
Bekerinik

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