Jan 30 2021 03:31 AM
First time on this feature so hoping someone can help me or point me in the right direction.
I have imported the information contained on a CSV file from a supplier onto a "Variations" spread sheet created in excel.
I now need to extract the various colours from the column and place them in a cell on a separate "Products Spreadsheet"
i.e.
18001 Dark green Small
18001 Dark Green Medium
18001 Dark Green Large
18001 Blue Small
18001 Blue Medium
18001 Blue large
etc
Number in Column A
Colour Column B
I need to capture these size variations in one cell on a "Products sheet" so S I M I L (with a vertical bar between) This is so that I can upload onto my web site.
Trouble is I don't know where to start to find the formula to write into the cell to get the information across
Any help would be greatly appreciated.
Jan 30 2021 04:03 AM
@A1anBaugh I suggest you look into "Get & Transform Date" (a.k.a. Power Query) on the Data ribbon. You can opt to connect your supplier's CSV file (or to the table that you already imported into your Excel sheet) and transform the data into the columns you need. If the structure of the data is really as consistent as you suggest, it's not very difficult. No complicated formulae required.
Are you familiar with Power Query?
Jan 30 2021 04:16 AM
Power Query could be an option, it looks like
In any case it's better if you provide sample file and indicate on which version of Excel you are.
Jan 30 2021 04:37 AM
Thank you for your help I am not familiar with Power Query - but I will now explore that feature.
Jan 30 2021 04:41 AM
Hello Sergei
Thank you for your response I will look into this feature. I am using excel 2016.
Jan 30 2021 07:52 AM
Power Query is built-in into 2016. However, if that's not an option, formula solution without dynamic arrays will be bit complicated. As variant that's to create helper columns with color and size, after that PivotTable.