Creating a combined cells to reduce the number of rows

C

craigs85

New Member
Joined
Jan 14, 2017
Messages
26
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
So as an example for 2 parts, you can see there are 50 rows for 2 parts.

I want 2 rows, with a column that combines all makes (in this case only BMW as i've filtered it to BMW), another column to combine all Modes (seperated by a pipe), etc.

Master Fitment List 07-01-20 customer copy.xlsx
ABCDEFG
1MakeModelYear &/or CodeModel VarDescriptionCat/GroupPart Number
2BMW1 Series114D2011-on, 5 Door Hatch, F2010/2011-on, 2L 5 Door Hatch2NPART0001
3BMW1 Series114D2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch2NPART0001
4BMW1 Series114i2011-on, 5 Door Hatch, F2010/2011-on, 2L 5 Door Hatch2NPART0001
5BMW1 Series114i2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch2NPART0001
6BMW1 Series116D2011-on, 5 Door Hatch, F2012/2010-on, 10/2011-on, 2L 5 Door Hatch2NPART0001
7BMW1 Series116D2012-on, 3 Door Hatch, F2112/2011-on,10/2011-on, 2L 3 Door Hatch2NPART0001
8BMW1 Series116i2011-on, 5 Door Hatch, F2010/2011-on, 1.6lt 5 Door Hatch2NPART0001
9BMW1 Series116i2012-on, 3 Door Hatch, F2110/2011-on, 1.6lt 3 Door Hatch2NPART0001
10BMW1 Series118D2011-on, 5 Door Hatch, F2010/2011-on, 2L 5 Door Hatch (Inc X Drive)2NPART0001
11BMW1 Series118D2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch (Inc X Drive)2NPART0001
12BMW1 Series118i2011-on, 5 Door Hatch F2010/2011-on, 1.6lt 5 Door Hatch2NPART0001
13BMW1 Series118i2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch2NPART0001
14BMW1 Series120D2011-on, 5 Door Hatch, F2010/2011-on, 2L 5 Door Hatch (Inc X Drive)2NPART0001
15BMW1 Series120D2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch (Inc X Drive)2NPART0001
16BMW1 Series120i2011-on, 5 Door Hatch F2010/2011-on, 2L 5 Door Hatch2NPART0001
17BMW1 Series120i2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch2NPART0001
18BMW1 Series125D2011-on, 5 Door Hatch, F2010/2011-on, 2L 3 Door Hatch2NPART0001
19BMW1 Series125D2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch2NPART0001
20BMW1 Series114D2011-on, 5 Door Hatch, F2010/2011-on, 2L 5 Door Hatch7KPART0002
21BMW1 Series114D2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch7KPART0002
22BMW1 Series114i2011-on, 5 Door Hatch, F2010/2011-on, 2L 5 Door Hatch7KPART0002
23BMW1 Series114i2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch7KPART0002
24BMW1 Series116D2011-on, 5 Door Hatch, F2012/2010-on, 10/2011-on, 2L 5 Door Hatch7KPART0002
25BMW1 Series116D2012-on, 3 Door Hatch, F2112/2011-on,10/2011-on, 2L 3 Door Hatch7KPART0002
26BMW1 Series116i2011-on, 5 Door Hatch, F2010/2011-on, 1.6lt 5 Door Hatch7KPART0002
27BMW1 Series116i2012-on, 3 Door Hatch, F2110/2011-on, 1.6lt 3 Door Hatch7KPART0002
28BMW1 Series118D2011-on, 5 Door Hatch, F2010/2011-on, 2L 5 Door Hatch (Inc X Drive)7KPART0002
29BMW1 Series118D2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch (Inc X Drive)7KPART0002
30BMW1 Series118i2011-on, 5 Door Hatch F2010/2011-on, 1.6lt 5 Door Hatch7KPART0002
31BMW1 Series118i2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch7KPART0002
32BMW1 Series120D2011-on, 5 Door Hatch, F2010/2011-on, 2L 5 Door Hatch (Inc X Drive)7KPART0002
33BMW1 Series120D2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch (Inc X Drive)7KPART0002
34BMW1 Series120i2011-on, 5 Door Hatch F2010/2011-on, 2L 5 Door Hatch7KPART0002
35BMW1 Series120i2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch7KPART0002
36BMW1 Series125D2011-on, 5 Door Hatch, F2010/2011-on, 2L 3 Door Hatch7KPART0002
37BMW1 Series125D2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch7KPART0002
38BMW1 Series125i2011-on, 5 Door Hatch, F2008/2011-on, 2L 5 Door Hatch.7KPART0002
39BMW1 Series125I2012-on, 3 Door Hatch, F2110/2011-on, 2L 3 Door Hatch7KPART0002
40BMW2 Series220D2014-on, Coupe F2203/2014-on, 1.6 & 2L Turbo Coupe (Incl X Drive)7KPART0002
41BMW2 Series220i2013-on, Coupe F2210/2013-on, 2L Turbo Coupe7KPART0002
42BMW2 Series220i2015-on, Convertible F2309/2015-on, 2L Turbo Convertible7KPART0002
43BMW2 Series228i2014-on, Convertible F2311/2014-on, 2L Turbo Convertible7KPART0002
44BMW2 Series228i2014-on, Coupe F2207/2014-on, 2L Turbo Coupe7KPART0002
45BMW2 Series230i2014-on, Coupe F2207/2014-on, 2L Turbo Coupe7KPART0002
46BMW2 Series230i2015-on, Convertible F2309/2015-on, 2L Turbo Convertible2KPART0002
47BMWM SeriesM22013-on, M235i 2 Dr Coupe,F8710/2013-on, 3L 2 Door Coupe (Inc X Drive)2KPART0002
48BMWM SeriesM22015-on, M235i 2 Dr Coupe,F2307/2015-on, 3L 2 Door Coupe X Drive2KPART0002
49BMWM SeriesM22015-on, M240i 2 Dr Coupe,F2309/2015-on, 3L 2 Door Coupe X Drive7KPART0002
50BMWM SeriesM22015-on, M240i 2 Dr Coupe,F8709/2015-on, 3L 2 Door Coupe (Inc X Drive)7KPART0002
Sheet1
 
Upvote 0
is that what you want?
Part NumberMakeModelYearAndOrCodeModel VarDescriptionCat.Group
PART0001BMW1 Series114D|114i|116D|116i|118D|118i|120D|120i|125D2011-on, 5 Door Hatch, F20|2012-on, 3 Door Hatch, F21|2011-on, 5 Door Hatch F2010/2011-on, 2L 5 Door Hatch|10/2011-on, 2L 3 Door Hatch|12/2010-on, 10/2011-on, 2L 5 Door Hatch|12/2011-on,10/2011-on, 2L 3 Door Hatch|10/2011-on, 1.6lt 5 Door Hatch|10/2011-on, 1.6lt 3 Door Hatch|10/2011-on, 2L 5 Door Hatch (Inc X Drive)|10/2011-on, 2L 3 Door Hatch (Inc X Drive)2N
PART0002BMW1 Series|2 Series|M Series114D|114i|116D|116i|118D|118i|120D|120i|125D|125i|125I|220D|220i|228i|230i|M22011-on, 5 Door Hatch, F20|2012-on, 3 Door Hatch, F21|2011-on, 5 Door Hatch F20|2014-on, Coupe F22|2013-on, Coupe F22|2015-on, Convertible F23|2014-on, Convertible F23|2013-on, M235i 2 Dr Coupe,F87|2015-on, M235i 2 Dr Coupe,F23|2015-on, M240i 2 Dr Coupe,F23|2015-on, M240i 2 Dr Coupe,F8710/2011-on, 2L 5 Door Hatch|10/2011-on, 2L 3 Door Hatch|12/2010-on, 10/2011-on, 2L 5 Door Hatch|12/2011-on,10/2011-on, 2L 3 Door Hatch|10/2011-on, 1.6lt 5 Door Hatch|10/2011-on, 1.6lt 3 Door Hatch|10/2011-on, 2L 5 Door Hatch (Inc X Drive)|10/2011-on, 2L 3 Door Hatch (Inc X Drive)|08/2011-on, 2L 5 Door Hatch.|03/2014-on, 1.6 & 2L Turbo Coupe (Incl X Drive)|10/2013-on, 2L Turbo Coupe|09/2015-on, 2L Turbo Convertible|11/2014-on, 2L Turbo Convertible|07/2014-on, 2L Turbo Coupe|10/2013-on, 3L 2 Door Coupe (Inc X Drive)|07/2015-on, 3L 2 Door Coupe X Drive|09/2015-on, 3L 2 Door Coupe X Drive|09/2015-on, 3L 2 Door Coupe (Inc X Drive)7K|2K
 
Upvote 0
sandy666 said:
is that what you want?
Part NumberMakeModelYearAndOrCodeModel VarDescriptionCat.Group
PART0001BMW1 Series114D|114i|116D|116i|118D|118i|120D|120i|125D2011-on, 5 Door Hatch, F20|2012-on, 3 Door Hatch, F21|2011-on, 5 Door Hatch F2010/2011-on, 2L 5 Door Hatch|10/2011-on, 2L 3 Door Hatch|12/2010-on, 10/2011-on, 2L 5 Door Hatch|12/2011-on,10/2011-on, 2L 3 Door Hatch|10/2011-on, 1.6lt 5 Door Hatch|10/2011-on, 1.6lt 3 Door Hatch|10/2011-on, 2L 5 Door Hatch (Inc X Drive)|10/2011-on, 2L 3 Door Hatch (Inc X Drive)2N
PART0002BMW1 Series|2 Series|M Series114D|114i|116D|116i|118D|118i|120D|120i|125D|125i|125I|220D|220i|228i|230i|M22011-on, 5 Door Hatch, F20|2012-on, 3 Door Hatch, F21|2011-on, 5 Door Hatch F20|2014-on, Coupe F22|2013-on, Coupe F22|2015-on, Convertible F23|2014-on, Convertible F23|2013-on, M235i 2 Dr Coupe,F87|2015-on, M235i 2 Dr Coupe,F23|2015-on, M240i 2 Dr Coupe,F23|2015-on, M240i 2 Dr Coupe,F8710/2011-on, 2L 5 Door Hatch|10/2011-on, 2L 3 Door Hatch|12/2010-on, 10/2011-on, 2L 5 Door Hatch|12/2011-on,10/2011-on, 2L 3 Door Hatch|10/2011-on, 1.6lt 5 Door Hatch|10/2011-on, 1.6lt 3 Door Hatch|10/2011-on, 2L 5 Door Hatch (Inc X Drive)|10/2011-on, 2L 3 Door Hatch (Inc X Drive)|08/2011-on, 2L 5 Door Hatch.|03/2014-on, 1.6 & 2L Turbo Coupe (Incl X Drive)|10/2013-on, 2L Turbo Coupe|09/2015-on, 2L Turbo Convertible|11/2014-on, 2L Turbo Convertible|07/2014-on, 2L Turbo Coupe|10/2013-on, 3L 2 Door Coupe (Inc X Drive)|07/2015-on, 3L 2 Door Coupe X Drive|09/2015-on, 3L 2 Door Coupe X Drive|09/2015-on, 3L 2 Door Coupe (Inc X Drive)7K|2K
Click to expand...
Hi,

Thank you for the reply.

Yes I think it is! ?.
 
Upvote 0
craigs85 said:
Yes I think it is! ?.
Click to expand...

so use
Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    Ren1 = Table.RenameColumns(Source,{{"Cat/Group", "Cat.Group"}}),
    Ren2 = Table.RenameColumns(Ren1,{{"Year &/or Code", "YearAndOrCode"}}),
    Group = Table.Group(Ren2, {"Part Number"}, {{"Count", each _, type table}}),
    ListMake = Table.AddColumn(Group, "Make", each List.Distinct([Count][Make])),
    ExtractMake = Table.TransformColumns(ListMake, {"Make", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    ListModel = Table.AddColumn(ExtractMake, "Model", each List.Distinct([Count][Model])),
    ExtractModel = Table.TransformColumns(ListModel, {"Model", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    ListYearCode = Table.AddColumn(ExtractModel, "YearAndOrCode", each List.Distinct([Count][YearAndOrCode])),
    ExtractYearCode = Table.TransformColumns(ListYearCode, {"YearAndOrCode", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    ListVar = Table.AddColumn(ExtractYearCode, "Model Var", each List.Distinct([Count][Model Var])),
    ExtractVar = Table.TransformColumns(ListVar, {"Model Var", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    ListDesc = Table.AddColumn(ExtractVar, "Description", each List.Distinct([Count][Description])),
    ExtractDesc = Table.TransformColumns(ListDesc, {"Description", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    ListCatGroup = Table.AddColumn(ExtractDesc, "Cat.Group", each List.Distinct([Count][Cat.Group])),
    ExtractCatGroup = Table.TransformColumns(ListCatGroup, {"Cat.Group", each Text.Combine(List.Transform(_, Text.From), "|"), type text})
in
    ExtractCatGroup
 
Upvote 0
Solution
Thank you,
Where do I add this?

I'm using Excel 2016.

I've got Data -> New Query, Show Query etc..
 
Upvote 0
  1. first make your range as Excel Table ( Ctrl+T )
  2. check the name of this table
  3. New Query - From Other Sources - Blank Query
  4. Advanced Editor - replace all there with code copied from the post
  5. in first line of the code change the name of table from Table14 to name you checked in #2
  6. Done
  7. Close&Load
 
Upvote 0
Ah great. Will give that a go!
Is it also possible to keep the columns after G, as they are the same for each part number, and basically describes the part, has a price, and description etc.
 
Upvote 0
sure ( not G but Part Number - this is Power Query not worksheet functions)
there could be as many columns as you want but code will be bigger (two lines for one column)
 
Upvote 0
Can you help me understand what that query is doing that you have written so I could potentially re-use it in future with adaptations if required.

Also, how would I pull through the rest of the columns after Part Number?

Lets says H is Details, and I is Price.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Duplicates with different data to combine with VBA
Replies
2
Views
98
chipsworld
C
A
  • Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
46
AlfredDB
A
D
  • Question
Issue making macro or formula on my spreedsheet
Replies
2
Views
66
devan69
D
I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
79
ipbr21054
I
D
  • Question
VBA to hide rows when a cell is a specific value and then hide the associated worksheet
Replies
3
Views
345
NoSparks
N

Forum statistics

Threads
1,214,641
Messages
6,120,693
Members
448,979
Latest member
DET4492

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