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>

 

[sandy666]

1. enough Excel 365 on PC (or Mac)
2/3. here is a file with consolidated columns example

I don't understand the second link, sorry

btw. Mac doesn't support PowerQuery

 

[desibouy]

1. enough Excel 365 on PC (or Mac)
2/3. here is a file with consolidated columns example

I don't understand the second link, sorry

btw. Mac doesn't support PowerQuery

Hi,

Thanks for that, that's exactly what I need. Don't worry about point 3 or the 2nd link. I was only showing you what my next steps were - thought it might give you an idea. So please tell me how do I run this, if you don't mind step by step. It's always going to be these columns.


Thanks

 

[sandy666]

1. load whole range (not a whole sheet!) into PowerQuery (Get&Transform)
2. with Ctrl key select these columns with data what you need only(*)
3. right click and Remove Other Columns
4. because columns doesn't contain data in the same row, again select columns what you need to be together (except NOT IN USE) and use Merge Columns
5. filter Merged column by blank
6. in Merged column Replace Values #(lf) with space
7. with Ctrl key select both columns and Remove Duplicates
8. sort NOT IN USE column as you wish (optional)
9. select NOT IN USE column and Move - To Beginning
10. after that select Close&Load from the ribbon

you need to find bolded functions on the ribbon or under right click

(*): Matrix Option - SIZE, Matrix Option - SIZE AND UNITS, Matrix Option - Units and Item Options.NOT IN USE