Putting Text into columns with values (not using pivot table) - NOOB go easy please :)

[jezrp222]

Hi All ,

This is my first post , so please go easy !

OK , so i have a list of values in column A and then a url value in column b , like the below

SKU URL
ab 1.jpg
ab 2.jpg
ab 3.jpg
abc 4.jpg
abc 5.jpg
abc 6.jpg


im trying to achieve the below

SKU ab ab ab abc abc abc
URL 1.jpg 2.jpg 3.jpg 4.jpg 5.jpg 6.jpg


hoepfully this makes , i tried using a pivot table but it doesnt work , im pulling my hair out

please help thanks

[Roel Jongman]

you can use paste special transpose for that.

1. select the range of cells and copy them
2. select the paste icon on the Ribbon and choose the paste special option (or go to right-click menu and find paste special)
3. in the bottom halve of the pop up screen pick transpose and press OK

your data should now be in columns instead of rows

[jezrp222]

hey roel , thanks for that , i just realised that this wont work for me now , i said it worng i need it to display like this

SKU url url
ab 1.jpg 2.jpg

so bascially i need to a row for each sku that then has all the values in columns ?

[GeoffW283]

Here is a formula based solution:

I have assumed your original data is in columns A and B.
Your re-arranged data is created starting at D2.

Two dynamic named ranges that cover the source data must be created:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Column-D derives the unique items from the source data SKUs in column-A
In d2:

Please Login or Register  to view this content.

In E2 commit as an array formula using CTRL-SHIFT-ENTER:

Please Login or Register  to view this content.

Copy E2 rightwards for at least the maximum number of URLs per SKU that you expect to have.

Copy all the column-D onwards formulas downwards for at least as many unique SKUs you have.

The attached workbook implements the above.