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.

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?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Small/Medium - Case of 672[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Small/Medium - Pack of 28[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Large/X-Large - Pack of 28[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Large/X-Large - Case of 672[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Small/Medium, Bulk - Case of 672[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Small/Medium, Bulk - Pack of 28[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Large/X-Large, Bulk - Pack of 28[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Large/X-Large, Bulk - Case of 672[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Small / Medium - Case of 50[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Small / Medium - Pack of 25[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Large - Case of 50[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Large - Pack of 25[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]X-Large - Pack of 25[/td][/tr]

[tr=bgcolor:#FFFFFF][td]X-Large - Case of 50[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]2X-Large - Pack of 25[/td][/tr]

[tr=bgcolor:#FFFFFF][td]2X-Large - Case of 50[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]DE - Box of 50[/td][/tr]

[tr=bgcolor:#FFFFFF][td]DM - Box of 50[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pack of 50[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Case of 1000[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pack of 5[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Case of 60[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pack of 30[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Case of 180[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pack of 10[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Case of 100[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Pack of 48[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Case of 576[/td][/tr]
[/table]

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