Consolidate Multiple columns into one Columns

[desibouy]

Hello All,

So I create product upload CSV and each product has a different choice such as a Size, Unit, Color, Size and Units, options etc. These choices are all on different columns however I need to consolidate all into one column at the end. The reason because I need to upload the choices before uploading the products. Sometimes these are over 300.

[table border="1"]

Matrix Option - SIZE AND UNITS	Matrix Option - Units
	Pack of 50
	Case of 1000
Small/Medium - Case of 672
Small/Medium - Pack of 28
Large/X-Large - Pack of 28
Large/X-Large - Case of 672
Small/Medium, Bulk - Case of 672
Small/Medium, Bulk - Pack of 28
Large/X-Large, Bulk - Pack of 28
Large/X-Large, Bulk - Case of 672
	Pack of 5
	Case of 60
	Pack of 30
	Case of 180
Small / Medium - Case of 50
Small / Medium - Pack of 25
Large - Case of 50
Large - Pack of 25
X-Large - Pack of 25
X-Large - Case of 50
2X-Large - Pack of 25
2X-Large - Case of 50
	Pack of 10
	Case of 100
	Pack of 48
	Case of 576
DE - Box of 50
DM - Box of 50

<tbody>
</tbody>

 

[desibouy]

bump. Anyone, please? I have over 15 columns.

 

[sandy666]

is that what you want?

Column1

Small/Medium - Case of 672

Small/Medium - Pack of 28

Large/X-Large - Pack of 28

Large/X-Large - Case of 672

Small/Medium, Bulk - Case of 672

Small/Medium, Bulk - Pack of 28

Large/X-Large, Bulk - Pack of 28

Large/X-Large, Bulk - Case of 672

Small / Medium - Case of 50

Small / Medium - Pack of 25

Large - Case of 50

Large - Pack of 25

X-Large - Pack of 25

X-Large - Case of 50

2X-Large - Pack of 25

2X-Large - Case of 50

DE - Box of 50

DM - Box of 50

Pack of 50

Case of 1000

Pack of 5

Case of 60

Pack of 30

Case of 180

Pack of 10

Case of 100

Pack of 48

Case of 576

if not , show expected result from example above

 

[desibouy]

Hi Sandy,

Yes like that. It didn't have to be like i.e. units at the bottom, but now thinking about it, it's better. The next part with this column is to add the ID of each of the choices, and the labels in a CSV along with Abbreviations.

So it would be easy in the sense of me not having to filter them i.e. Size and Units at the top, Units at the bottom etc.

Thanks

 

[sandy666]

You said: "consolidate multiple columns into one column" so I did it.
You didn't show expected result

here is M-code of PowerQuery

[SIZE=1]let
    Source = Table.ToColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1", each ([Column1] <> null))
in
    #"Filtered Rows"[/SIZE]

you can sort, filter or use header as separator for each column

 

[desibouy]

Hi Sandy,

I thought I replied to this, but my message wasn't sent.

Anyway, I've never done PowerQuery. I googled how to use PowerQuery long and hard but all the tutorials are showing me how to work with Tables but I haven't been able to find a way to use this. I thought it might be easy as selecting the 15 columns I have and running a Macro.

P.S - those other columns I mentioned like ID, Abbreviation etc, is not part of this file. I have to make this from scratch (hence why I didn't mention it) but helping this consolidation is really going to be helpful and appreciative.

Thanks

 

[sandy666]

so each column is on different file?
or all are together and you can use it as table?

single column can be a table
conglomerate few single columns can be a table

but...

if you wish to use VBA - no problem

have a nice day

 

[desibouy]

so each column is on different file?
or all are together and you can use it as table?

single column can be a table
conglomerate few single columns can be a table

but...

if you wish to use VBA - no problem

have a nice day

Hi,

Thanks for your reply. All columns are on one sheet. Right now I've got a file ready with 300 products using 6 columns different options. So I just want those 6 columns into one, so I can create a 2nd sheet for the option upload.

I have no idea, what to use. I've never heard of PowerQuery - I didn't even know it existed until you posted it could you help me where or how I need to run this.

Thanks

 

[sandy666]

Hm, could you:
1. show your excel version
2. paste link here to your excel file with raw data (desensitized)
3. which columns you want to "transform"

here is some information about PQ:

• Microsoft Power Query for Excel Help
• Getting Started with Get & Transform in Excel
• Excel Power Query (Get & Transform) Tutorial for Beginners

 

[desibouy]

Hm, could you:
1. show your excel version
2. paste link here to your excel file with raw data (desensitized)
3. which columns you want to "transform"

here is some information about PQ:

• Microsoft Power Query for Excel Help
• Getting Started with Get & Transform in Excel
• Excel Power Query (Get & Transform) Tutorial for Beginners

Hi Sandy,


1. Excel 365, Version 1810 (Build 11001.20108).

2. https://1drv.ms/x/s!AqhPK8CntHeag5woUFojLbWRl_XEbw

3. I would like to transform - Column AJ TO BG. I would like if you can also bring in Column BH as a separate field.

What I'm trying to achieve after consolidation is this spreadsheet - https://1drv.ms/u/s!AqhPK8CntHeag5wpuRUPlCE6s5IYSw

to clarify - COLUMN BH will be Column B (List Name), the Consolidation will be Column C. The other fields I have done and takes a few seconds to do.


Thanks