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

 

[Fluff]

How about

Function craigs(Rng As Range, ProdCode As String) As String
   Dim Cl As Range
   Dim strA As String, strB As String, strC As String
   
   For Each Cl In Rng.Columns(1).Cells
      If Cl.Offset(, 3).Value2 = ProdCode Then
         If InStr(1, strA, Cl.Value, 1) = 0 Then strA = strA & "|" & Cl.Value
         If InStr(1, strB, Cl.Offset(, 1).Value, 1) = 0 Then strB = strB & "|" & Cl.Offset(, 1).Value
         If InStr(1, strC, Cl.Offset(, 2).Value, 1) = 0 Then strC = strC & "|" & Cl.Offset(, 2).Value
      End If
   Next Cl
   craigs = Mid(strA, 2) & "," & Mid(strB, 2) & "," & Mid(strC, 2) & "," & ProdCode
End Function

Used like

+Fluff v2.xlsm
	A	B	C	D	E
1	Make	Model	Submodal	Product Code
2	Ford	Focus	MK1	000001	Ford|Audi,Focus|A1,MK1|Mk2|Mk3|1.2,000001
3	Ford	Focus	Mk2	000001
4	Ford	Focus	Mk3	000001
5	Audi	A1	1.2	000001
6	Audi	A4	1.4	000002
7
Main

Cell Formulas
Range	Formula
E2	E2	=craigs(A2:D6,D2)

 

[craigs85]

Thank you for taking the time to help me with this, its really appreciated!

I have added the function to the workbook, and then used the formula on the sheet, but i get #NAME? in the cells.

This is my first time using VBA in excel, so I apologise for my lack of knowledge here.

 

[Fluff]

You need to put the code in a standard module, in the Editor select Insert then Module & put the code in the window that opens up.

 

[craigs85]

Wow, that looks great! Thanks so much!

It would make it a tiny bit easier for me if the string you generate was split into seperate cells based on the comma.

So all of the Makes together in one column, the models in another, then the sub category, and then the product code.

But if thats a lot more work, no worries! Really grateful for what you have done. One day I might get my head around the function to see how its actually working! haha.

 

[Fluff]

For this to work you will need to select E2:H2, then put the formula in the Formula bar & confirm with Ctrl Shift Enter, not just enter.

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H
1	Make	Model	Submodal	Product Code
2	Ford	Focus	MK1	000001	Ford|Audi	Focus|A1	MK1|Mk2|Mk3|1.2	000001
3	Ford	Focus	Mk2	000001
4	Ford	Focus	Mk3	000001
5	Audi	A1	1.2	000001
6	Audi	A4	1.4	000002
7
Main

Cell Formulas
Range	Formula
E2:H2	E2	=craigs(A2:D6,D2)
Press CTRL+SHIFT+ENTER to enter array formulas.

Function craigs(Rng As Range, ProdCode As String) As Variant
   Dim Cl As Range
   Dim strA As String, strB As String, strC As String

   For Each Cl In Rng.Columns(1).Cells
      If Cl.Offset(, 3).Value2 = ProdCode Then
         If InStr(1, strA, Cl.Value, 1) = 0 Then strA = strA & "|" & Cl.Value
         If InStr(1, strB, Cl.Offset(, 1).Value, 1) = 0 Then strB = strB & "|" & Cl.Offset(, 1).Value
         If InStr(1, strC, Cl.Offset(, 2).Value, 1) = 0 Then strC = strC & "|" & Cl.Offset(, 2).Value
      End If
   Next Cl
   craigs = Array(Mid(strA, 2), Mid(strB, 2), Mid(strC, 2), ProdCode)
End Function

 

[craigs85]

Ah great! That looks good thank you.

So hopefully my final question.
My spreadsheet has just under 5000 rows.

Just copying those 4 calculated rows down to the bottom isn't right is it, as its building the list as it goes, so the furthest one down will only have one value in each (for each product code).

Is there a way I copy that formula to every unique code nearest to the top

Does that even make sense!!?

 

[Fluff]

You could use this in E2:H2 confirmed with Ctrl Shift Enter & then copy down

=IF(D2<>D1,craigs($A$2:$D$6,D2),"")

 

[craigs85]

Brill thanks for that.
Thats now copied it all the waydown, and kept it consistant.
So I can just remove duplicates and thats it I think.

I think I will be able to re-use this function for other sheets.

I wouldn't mind trying to understand it so i can do this.
Other sheets will have more columns I want to combine, or will have columns in between that I am not interesting in combining.

 

[Fluff]

You're welcome & thanks for the feedback.