Merge cell contents based on a shared value

[craigs85]

Hi All,

I am trying to do something reasonably complicated in excel, and not sure what way I should do it.

I basically have a list of car parts but there are a lot of part numbers that apply to multiple vehicles.

I have a Make, Model, Submodel, Product Code Column (plus lots more - but these 4 are the main ones of concern.

An example will be:

Make,Model,Submodal,Product Code
Ford,Focus,Mk1,000001
Ford,Focus,MK2,000001
Ford,Focus,MK3,000001
Audi,A1,1.2,000001
Audi,A4,1.4,000001

And so on.

What I would like, is to merge all of the makes, models, and submodals so they appear pipe seperated in a single cell (for each), where they have the same product code).

So the example above would look like.

Make,Model,Submodal,Product Code
Ford|Audi,Focus|A1|A4,Mk1|MK2|MK3|1.2|1.4,000001

Does that make sense?

Can anyone suggest what I can do please?

Thanks so much in advance!

Craig

 

[tyija1995]

Hi,

Can you try this (formula is sort of big but will work if you have O365):

Book1
	A	B	C
1	Make,Model,Submodal,Product Code	Make,Model,Submodal,Product Code	Make,Model,Submodal,Product Code
2	Ford,Focus,Mk1,000001	000001	Ford,Audi|Focus,A1|Mk1,MK2,MK3,1.2|000001
3	Ford,Focus,MK2,000001	000002	Audi|A4|1.4|000002
4	Ford,Focus,MK3,000001
5	Audi,A1,1.2,000001
6	Audi,A4,1.4,000002
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=UNIQUE(TRIM(RIGHT(SUBSTITUTE(A2:A6,",",REPT(" ",LEN(A2:A6))),LEN(A2:A6))))
C2:C3	C2	=TEXTJOIN("|",TRUE,TEXTJOIN(",",TRUE,UNIQUE(TRIM(MID(SUBSTITUTE(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6)))),",",REPT(" ",LEN(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6))))))),(1-1)*LEN(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6)))))+1,LEN(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6))))))))),TEXTJOIN(",",TRUE,UNIQUE(TRIM(MID(SUBSTITUTE(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6)))),",",REPT(" ",LEN(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6))))))),(2-1)*LEN(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6)))))+1,LEN(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6))))))))),TEXTJOIN(",",TRUE,UNIQUE(TRIM(MID(SUBSTITUTE(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6)))),",",REPT(" ",LEN(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6))))))),(3-1)*LEN(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6)))))+1,LEN(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6))))))))),TEXTJOIN(",",TRUE,UNIQUE(TRIM(MID(SUBSTITUTE(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6)))),",",REPT(" ",LEN(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6))))))),(4-1)*LEN(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6)))))+1,LEN(FILTER($A$2:$A$6,B2=TRIM(RIGHT(SUBSTITUTE($A$2:$A$6,",",REPT(" ",LEN($A$2:$A$6))),LEN($A$2:$A$6))))))))))
Dynamic array formulas.

 

[craigs85]

Thanks for much for getting back to me.
I don't think I explained very well initially.

Make,Model,Submodal,Product Code are excel columns, not all contents of a single column and cell.

I didn't know how to post if how you have done..

Column A is Make, B is Model, C is Submodal and D is Product Code.

Does that make sense?

 

[Fluff]

Hi & welcome to MrExcel.
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

You can post sample data to the board like tyija1995 did by using the XL2BB add-in. See my signature for a link.

 

[tyija1995]

OK now I see that they are different columns each, then try this:

Book1
	A	B	C	D	E	F	G	H
1	Make	Model	Submodal	Product Code		MASTER		Unique Product Codes
2	Ford	Focus	Mk1	000001		Ford,Audi|Focus,A1|Mk1,Mk2,Mk3,1.2|000001		000001
3	Ford	Focus	Mk2	000001		Audi|A4|1.4|000002		000002
4	Ford	Focus	Mk3	000001
5	Audi	A1	1.2	000001
6	Audi	A4	1.4	000002
Sheet1 (2)

Cell Formulas
Range	Formula
H2:H3	H2	=UNIQUE(D2:D6)
F2:F3	F2	=TEXTJOIN("|",TRUE,TEXTJOIN(",",TRUE,UNIQUE(FILTER(A$2:A$6,$D$2:$D$6=$H2))),TEXTJOIN(",",TRUE,UNIQUE(FILTER(B$2:B$6,$D$2:$D$6=$H2))),TEXTJOIN(",",TRUE,UNIQUE(FILTER(C$2:C$6,$D$2:$D$6=$H2))),TEXTJOIN(",",TRUE,UNIQUE(FILTER(D$2:D$6,$D$2:$D$6=$H2))))
Dynamic array formulas.

Again it requires O365 (so I am really hoping you are using that, otherwise I will not be able to help answer your question)

In this method I have generated a distinct list of product codes to help aid the formulae used

 

[craigs85]

Thanks very much, Profile updated.

Here is my sheet, using 2016 Excel.

price-list-export-29-11-2020.xlsx
	A	B	C	D
1	Make	Model	Submodal	Product Code
2	Ford	Focus	MK1	000001
3	Ford	Focus	Mk2	000001
4	Ford	Focus	Mk3	000001
5	Audi	A1	1.2	000001
6	Audi	A4	1.4	000001
Sheet2

 

[Fluff]

Profile updated.

Thanks for that.

Unfortunately what you want will be very difficult to do in your version of Excel, with functions alone.
Are you happy to have a VBA solution?

 

[craigs85]

Thanks for that.

Unfortunately what you want will be very difficult to do in your version of Excel, with functions alone.
Are you happy to have a VBA solution?

Yeah definitely ?.
I would be happy to have any solution haha.

The sheet I have has about 4000 rows like the one I provided, and with extra columns on the end like description and price etc, but these are all the same for per product code, but I would need them on my 'new' bits.

Does that make sense?

 

[Fluff]

Ok, I'm off now, but will have a look tomorrow if no-one else jumps in.

 

[craigs85]

Ok, I'm off now, but will have a look tomorrow if no-one else jumps in.

Much appreciated ?