Creating a combined cells to reduce the number of rows

craigs85

New Member
Joined
Jan 14, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone.

I have a large sheet that consists of car parts from a particular brand. Rather than it being one row per part number, the part number is on multiple rows, one for each different vehicle variant that it can fit.

There are around 165000 rows of parts that are available for various makes, models of cars etc.

Columns A to F are the car makes, models, years etc.
Column G is the Part Number.

Is it then followed by a load more other columns that are the same for each part number.

I want one row for each unique Part Number, but then a cell on each row that is a pipe seperated list of all the combined values or row a (makes) that have the same part number, then another cell with a list of all the models and so on.

This is very similar to a topic I raised late last year where it was resolved by a very helpful member on this community. (Merge cell contents based on a shared value)

Not being a programmer, I couldn't understand it enough to adapt it for this new file, although I would love to be able to.

If anyone can help,that would be great.
Can provide more info if needed!

Thanks in advance,
Craig
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
don't use special characters in column names - stay with text and numbers
in Power Query Editor on right side you can see steps, click each step then you can see how your table will change
almost each step has a little gear on the end of the step name, try to click it and see what is inside this step
so you can do similar steps for Details and Price - just add Custom Column and use the same code and change the columns name in the code accordingly
after that do this
steps.png
 

Some videos you may like

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.

craigs85

New Member
Joined
Jan 14, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Ok will give it a good read.

Thank you so much for your help. I really appreciate it.
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
You are welcome and thanks for the feedback
Have a nice day (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,596
Messages
5,625,714
Members
416,130
Latest member
galgozzi

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
Top