merge fields across several columns

[gmeskil]

I have data that looks like this:

Column A Column B Column C
PT347-02010 Matrix 1901
(empty cell) Matrix 1902
(empty cell) Matrix 1911
(empty cell) Matrix 1912
(empty cell) Corolla 1801
(empty cell) Corolla 1802
(empty cell) Corolla 1811
(empty cell) Corolla 1812
PTS21-02030 Corolla 1801
(empty cell) Corolla 1802
(empty cell) Corolla 1811
(empty cell) Corolla 1812

and so on.
Column A is the actual part number. Column B is the vehicle that it fits and column C is the model code for that vehicle.
What I need to do is have the data come out like this.

PT347-02010 Matrix, Corolla 1901, 1902, 1911, 1912
PTS21-02030 Corolla 1801, 1802, 1811, 1812

I am sure that this will have to be done in several steps. Doing it by hand would take forever being that i have about 25,000 lines in the spreadsheet.

Greg

 

[gmeskil]

Done,

Greg

 

[rodp]

There you go - that should do it... and no VB / scripting too!! Let me know if you need me to explain anything in the sheet.

Cheers

Rodp

 

[rodp]

I was going to try and share some pictures (using MrExcelHTML) here. It worked initially but then the HTML was changed to just text so not sure what to do there. So if anyone wants the excel file version of this let me know.

 

[rodp]

This should be the raw table with some extra fields to manage the conversion - sorry if this doesn't work! You should be able to copy and paste the code into notepad, save as an htm save and open it up in a browser though.

Excel 2007ABCDEF1Concatenate_StringPart NumberSeries nameModel yearModel codemode_code2PT347-02010Matrix2008PT347-02010Matrix2008190113PT347-02010Matrix2008PT347-02010Matrix2008190224PT347-02010Matrix2008PT347-02010Matrix2008191135PT347-02010Matrix2008PT347-02010Matrix2008191246PTS21-02030Corolla2008PTS21-02030Corolla2008180117PTS21-02030Corolla2008PTS21-02030Corolla2008180228PTS21-02030Corolla2008PTS21-02030Corolla2008181139PTS21-02030Corolla2008PTS21-02030Corolla20081812410PTS21-02030Corolla2008PTS21-02030Corolla20081821511PTS21-02030Corolla2008PTS21-02030Corolla20081822612PTS21-12030Matrix2008PTS21-12030Matrix20081901113PTS21-12030Matrix2008PTS21-12030Matrix20081902214PTS21-12030Matrix2008PTS21-12030Matrix20081911315PTS21-12030Matrix2008PTS21-12030Matrix20081912416PTS21-02031Corolla2008PTS21-02031Corolla20081801117PTS21-02031Corolla2008PTS21-02031Corolla20081802218PTS21-02031Corolla2008PTS21-02031Corolla20081811319PTS21-02031Corolla2008PTS21-02031Corolla20081812420PTS21-02031Corolla2008PTS21-02031Corolla20081821521PTS21-02031Corolla2008PTS21-02031Corolla200818226[CENTER][COLOR=#161120][B]Sheet1[/B][/COLOR][/CENTER]

Worksheet FormulasCellFormulaA2=B2&C2&D2F3=F2+1F4=IF(B4&D4&C4=B3&D3&C3,F3+1,1)

 

[rodp]

And here's the pivot

[RANGE=cls:xl2bb-100][XR][XH=cs:16]Excel 2007[/XH][/XR][XR][XH][/XH][XH]I[/XH][XH]J[/XH][XH]K[/XH][XH]L[/XH][XH]M[/XH][XH]N[/XH][XH]O[/XH][XH]P[/XH][XH]Q[/XH][XH]R[/XH][XH]S[/XH][XH]T[/XH][XH]U[/XH][XH]V[/XH][XH]W[/XH][/XR][XR][XH]1[/XH][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]2[/XH][XD=h:l]Sum of mode_code[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:l]mode_code[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]3[/XH][XD=h:l]Part Number[/XD][XD=h:l]Series name[/XD][XD=h:l]Model year[/XD][XD=h:r]1[/XD][XD=h:r]2[/XD][XD=h:r]3[/XD][XD=h:r]4[/XD][XD=h:r]5[/XD][XD=h:r]6[/XD][XD=h:r]7[/XD][XD=h:r]8[/XD][XD=h:r]9[/XD][XD=h:r]10[/XD][XD=h:r]11[/XD][XD=h:r]12[/XD][/XR][XR][XH]4[/XH][XD=h:l]PT347-02010[/XD][XD=h:l]Matrix[/XD][XD=h:r]2008[/XD][XD=h:r]1[/XD][XD=h:r]2[/XD][XD=h:r]3[/XD][XD=h:r]4[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]5[/XH][XD=h:l]PTS21-02030[/XD][XD=h:l]Corolla[/XD][XD=h:r]2008[/XD][XD=h:r]1[/XD][XD=h:r]2[/XD][XD=h:r]3[/XD][XD=h:r]4[/XD][XD=h:r]5[/XD][XD=h:r]6[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]6[/XH][XD=h:l]PTS21-02031[/XD][XD=h:l]Corolla[/XD][XD=h:r]2008[/XD][XD=h:r]1[/XD][XD=h:r]2[/XD][XD=h:r]3[/XD][XD=h:r]4[/XD][XD=h:r]5[/XD][XD=h:r]6[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]7[/XH][XD=h:r][/XD][XD=h:l]Matrix[/XD][XD=h:r]2008[/XD][XD=h:r]1[/XD][XD=h:r]2[/XD][XD=h:r]3[/XD][XD=h:r]4[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]8[/XH][XD=h:l]PTS21-12030[/XD][XD=h:l]Matrix[/XD][XD=h:r]2008[/XD][XD=h:r]1[/XD][XD=h:r]2[/XD][XD=h:r]3[/XD][XD=h:r]4[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]9[/XH][XD=h:l]PTS21-89040[/XD][XD=h:l]4Runner[/XD][XD=h:r]2008[/XD][XD=h:r]1[/XD][XD=h:r]2[/XD][XD=h:r]3[/XD][XD=h:r]4[/XD][XD=h:r]5[/XD][XD=h:r]6[/XD][XD=h:r]7[/XD][XD=h:r]8[/XD][XD=h:r]9[/XD][XD=h:r]10[/XD][XD=h:r]11[/XD][XD=h:r]12[/XD][/XR][XR][XH]10[/XH][XD=h:r][/XD][XD=h:r][/XD][XD=h:r]2009[/XD][XD=h:r]1[/XD][XD=h:r]2[/XD][XD=h:r]3[/XD][XD=h:r]4[/XD][XD=h:r]5[/XD][XD=h:r]6[/XD][XD=h:r]7[/XD][XD=h:r]8[/XD][XD=h:r]9[/XD][XD=h:r]10[/XD][XD=h:r]11[/XD][XD=h:r]12[/XD][/XR][XR][XH]11[/XH][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH=cs:16][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

and this is the final part which works off the pivot table

[RANGE=cls:xl2bb-100][XR][XH=cs:16]Excel 2007[/XH][/XR][XR][XH][/XH][XH]Z[/XH][XH]AA[/XH][XH]AB[/XH][XH]AC[/XH][XH]AD[/XH][XH]AE[/XH][XH]AF[/XH][XH]AG[/XH][XH]AH[/XH][XH]AI[/XH][XH]AJ[/XH][XH]AK[/XH][XH]AL[/XH][XH]AM[/XH][XH]AN[/XH][/XR][XR][XH]2[/XH][XD=h:c]Copy paste values this area into a new
sheet and save as csv[/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][XD=h:c][/XD][/XR][XR][XH]3[/XH][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]4[/XH][XD=h:l|fw:b]PT347-02010[/XD][XD=h:l|fw:b]Matrix[/XD][XD=h:r]2008[/XD][XD=h:r|cls:fx][FORMULA==IF(L4="","",OFFSET($E$2,MATCH($Z4&$AA4&$AB4,$A$2:$A$49,FALSE)-2+L4,0))]1901[/FORMULA][/XD][XD=h:r]1902[/XD][XD=h:r]1911[/XD][XD=h:r]1912[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]5[/XH][XD=h:l|cls:fx][FORMULA==IF(I5="",Z4,I5)]PTS21-02030[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA==IF(J5="",AA4,J5)]Corolla[/FORMULA][/XD][XD=h:r|cls:fx][FORMULA==IF(K5="",AB4,K5)]2008[/FORMULA][/XD][XD=h:r|cls:fx][FORMULA==IF(L5="","",OFFSET($E$2,MATCH($Z5&$AA5&$AB5,$A$2:$A$49,FALSE)-2+L5,0))]1801[/FORMULA][/XD][XD=h:r]1802[/XD][XD=h:r]1811[/XD][XD=h:r]1812[/XD][XD=h:r]1821[/XD][XD=h:r]1822[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]6[/XH][XD=h:l]PTS21-02031[/XD][XD=h:l]Corolla[/XD][XD=h:r]2008[/XD][XD=h:r]1801[/XD][XD=h:r]1802[/XD][XD=h:r]1811[/XD][XD=h:r]1812[/XD][XD=h:r]1821[/XD][XD=h:r]1822[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]7[/XH][XD=h:l]PTS21-02031[/XD][XD=h:l]Matrix[/XD][XD=h:r]2008[/XD][XD=h:r]1901[/XD][XD=h:r]1902[/XD][XD=h:r]1911[/XD][XD=h:r]1912[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]8[/XH][XD=h:l]PTS21-12030[/XD][XD=h:l]Matrix[/XD][XD=h:r]2008[/XD][XD=h:r]1901[/XD][XD=h:r]1902[/XD][XD=h:r]1911[/XD][XD=h:r]1912[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]9[/XH][XD=h:l]PTS21-89040[/XD][XD=h:l]4Runner[/XD][XD=h:r]2008[/XD][XD=h:r]8642[/XD][XD=h:r]8646[/XD][XD=h:r]8648[/XD][XD=h:r]8664[/XD][XD=h:r]8666[/XD][XD=h:r]8668[/XD][XD=h:r]8672[/XD][XD=h:r]8676[/XD][XD=h:r]8678[/XD][XD=h:r]8682[/XD][XD=h:r]8686[/XD][XD=h:r]8688[/XD][/XR][XR][XH]10[/XH][XD=h:l]PTS21-89040[/XD][XD=h:l]4Runner[/XD][XD=h:r]2009[/XD][XD=h:r]8642[/XD][XD=h:r]8646[/XD][XD=h:r]8648[/XD][XD=h:r]8664[/XD][XD=h:r]8666[/XD][XD=h:r]8668[/XD][XD=h:r]8672[/XD][XD=h:r]8676[/XD][XD=h:r]8678[/XD][XD=h:r]8682[/XD][XD=h:r]8686[/XD][XD=h:r]8688[/XD][/XR][XR][XH=cs:16][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2|h:l|fw:b]Cell Formulas[/XH][/XR][XR][XH]Cell[/XH][XH]Formula[/XH][/XR][XR][XD]AC4[/XD][XD]=IF(L4="","",OFFSET($E$2,MATCH($Z4&$AA4&$AB4,$A$2:$A$49,FALSE)-2+L4,0))[/XD][/XR][XR][XD]AC5[/XD][XD]=IF(L5="","",OFFSET($E$2,MATCH($Z5&$AA5&$AB5,$A$2:$A$49,FALSE)-2+L5,0))[/XD][/XR][XR][XD]Z5[/XD][XD]=IF(I5="",Z4,I5)[/XD][/XR][XR][XD]AA5[/XD][XD]=IF(J5="",AA4,J5)[/XD][/XR][XR][XD]AB5[/XD][XD]=IF(K5="",AB4,K5)[/XD][/XR][/RANGE]

 

[gmeskil]

We are really close on this. I need to have the cells combined though.
In cell
A1 I need the part number.
B1 I need the years 2008, 2009 etc.
C1 I need the models 1831, 1832, 1833 etc.

If there is more than one model or year in the combined cells i need them separated by a comma and a space.

I guess i could take the copied data and do a join, i was just hoping there was a way to take a range of cells and do a join automatically.

Greg

 

[rodp]

Hi Greg,

Glad we're getting close. Can you post in the Google sheet what you want to see as the end result just so that I can be clear of what exactly you want to see. I'm guess below but would be good if you could provide a little more detail.

PTS21-12030

2008

Matrix

1901

1902

1911

1912

<tbody>
</tbody>

becomes
PTS21-12030,2008,Matrix,1901,1902,1911,1912

and

PTS21-89040	2008	4Runner	8642	8646	8648	8664	8666	8668	8672	8676	8678	8682	8686	8688
PTS21-89040	2009	4Runner	8642	8646	8648	8664	8666	8668	8672	8676	8678	8682	8686	8688

<tbody>
</tbody>

becomes
PTS21-89040,2008,2009,4Runner,8642,8646,8648,8664,8666,8668,8672,8676,8678,8682,8686,8688

If that's the case, we might need to setup a pivot of a pivot as the current example only combines ones field. We might need to employ a different method if you want it to be more flexible.

Question: do you need to differentiate between a part, year, series field and the multiple entries? ie do you need to use two types of delimeters in the csv file (ie comma and semicolon)?

ie. PTS21-89040;2008,2009;4Runner;8642,8646,8648,8664,8666,8668,8672,8676,8678,8682,8686,8688

Cheers

Rodp

PS - just looked at the data again and spotted this:

PTS21-02031	Corolla
PTS21-02031	Matrix

<colgroup><col style="width: 100px"><col width="64"></colgroup><tbody>
</tbody>

Does that mean you'll also want multiple series names for the same part number to be combined together too?

 

[gmeskil]

I have added what the outcome data would look like for us on lines 58 and 59 of the spread sheet.
The part number can have more than one instance because we separate them by model type.
Then under that model we need to have the model numbers combined into one field and model year combined into a separate field separated by a comma and then a space.

Greg

 

[rodp]

I've moved that to where the results bit is so that it's easier to check -see Z18.... can you work through a few more examples please so that we cover more scenarios... also your example doesn't exist in the raw data anyway!

Thanks

Rodp

 

[gmeskil]

that is exactly it. However this looks really advanced to me, how do i implement this on a sheet that has over 60K lines?

Greg