Merge cell contents based on a shared value

craigs85

New Member
Joined
Jan 14, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,150
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
ABCDE
1MakeModelSubmodalProduct Code
2FordFocusMK1000001Ford|Audi,Focus|A1,MK1|Mk2|Mk3|1.2,000001
3FordFocusMk2000001
4FordFocusMk3000001
5AudiA11.2000001
6AudiA41.4000002
7
Main
Cell Formulas
RangeFormula
E2E2=craigs(A2:D6,D2)
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

craigs85

New Member
Joined
Jan 14, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,150
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 14, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,150
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGH
1MakeModelSubmodalProduct Code
2FordFocusMK1000001Ford|AudiFocus|A1MK1|Mk2|Mk3|1.2000001
3FordFocusMk2000001
4FordFocusMk3000001
5AudiA11.2000001
6AudiA41.4000002
7
Main
Cell Formulas
RangeFormula
E2:H2E2=craigs(A2:D6,D2)
Press CTRL+SHIFT+ENTER to enter array formulas.


VBA Code:
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
 
Solution

craigs85

New Member
Joined
Jan 14, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,150
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You could use this in E2:H2 confirmed with Ctrl Shift Enter & then copy down
Excel Formula:
=IF(D2<>D1,craigs($A$2:$D$6,D2),"")
 

craigs85

New Member
Joined
Jan 14, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,150
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,017
Messages
5,599,362
Members
414,306
Latest member
Dennis_vdw

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