Re-arrange data from list to table

[Etsebjd]

First time poster here and not a native speaker, so I might lack the correct expressions sometimes - please bear with me.

I very often come across the problem to transfer a list of value pairs into a table.
To be more specific, the value pairs consists of a product id and another string (e.g. file name or URL).
What I need is a table of all product ids with all possible strings in the columns behind. It is of no consequence if a string is paired to multiple ids.

What I have:

product id > string
A > 123.jpg
A > 124.jpg
B > 223.jpg
B > 224.jpg
A > 125.jpg
C > 123.jpg
C > 323.jpg

What I need:

product id > string_1 > string_2 > string_3 > ?
A > 123.jpg > 124.jpg > 125.jpg
B > 223.jpg > 224.jpg
C > 123.jpg > 323.jpg

Would be great if you could show me a way to achieve this with Excel.

Cheers

[TMS]

If you have the functions in Excel for Mac, try:

Please Login or Register  to view this content.

[TMS]

Note: you might not be able to put the formula directly into a Structured Table.

If you need to do that, put the formula in a cell OUTSIDE (and not next to the Table). Then copy and Paste Special | Values into the Table.

[alansidman]

An alternative is to pivot the data in Power Query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G
1	product id	string		product id	1	2	3
2	A	123.jpg		A	123.jpg	124.jpg	125.jpg
3	A	124.jpg		B	223.jpg	224.jpg
4	B	223.jpg		C	123.jpg	323.jpg
5	B	224.jpg
6	A	125.jpg
7	C	123.jpg
8	C	323.jpg

Sheet: Sheet1

[Etsebjd]

Hi Trevor, will try this - thanks a lot!

[Etsebjd]

Hi Alan,
Seems like I should really dig into the power query topic - tried to avoid it, but there are no excuses any more.
Thank you.