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

 

[MickG]

In your Results (first line) there appears to be some "Corolla" numbers missing, is that meant to be like that, Please explain !!!!

 

[rodp]

I think you can do this as a pivot table but you need to fill down the data in column A first. Then A B and C are your row headings (or is it column heading... you'll be able to see when you do it) and then just use a dummy entry for the Sum; eg count of column A or something.

a simple if statement can be used to create a populated column A.

Does that help?

Rodp

 

[gmeskil]

I thought about a pivot table. But i am not sure a pivot table will accomplish totally what i am trying to do. I dont understand the full capabilities of pivot tables.
I basically need to create a text delimited file that I upload to a website. Column A will have to have the part number while column B and C will have to contain all of the data that is applicable to that one part number.
Doesn't a pivot table only work with one record at a time?

I do have the data with column A containing all of the data filled down. I can send a sample.

Greg

 

[gmeskil]

for ease of reading i removed all duplicate numbers. I can easily put that back in the table.

 

[rodp]

send me a sample / set something up on Google sheets (as that does pivots too) and I'll try to set it up - can I assume that this is a one off or something that needs to be updated on a regular basis?

re the conversion to a csv, all you do is simply copy paste the data (as values) from the pivot table and then paste it into a new sheet and then save that as a csv.

Thanks

Rodp

 

[gmeskil]

I have created the document. Where do you want me to send it? I can share it with you through Google but i don't have your email address.

Greg

 

[gmeskil]

here is the sharable link.

https://docs.google.com/spreadsheets/d/1qr9n6WP57aCdLoHXSkqhnhuwXxQ***mh4RYVOV4vnMA/edit?usp=sharing

 

[gmeskil]

Here is the link that actually works.

https://docs.google.com/spreadsheets/d/1lcwLwQZ7y5iYikUFi7ilYwIGC54MjsTLwt9yYEuEjWU/edit?usp=sharing

Greg

 

[rodp]

Here is the link that actually works.

https://docs.google.com/spreadsheets/d/1lcwLwQZ7y5iYikUFi7ilYwIGC54MjsTLwt9yYEuEjWU/edit?usp=sharing

Greg

Hey Greg, can you give me write access? I can't do anything in view only mode I'm afraid. If you want to share it privately then send me a PM.

Thanks

Rodp