Combining multiple columns within 1 table

[Qliky66]

Hi,

I have a list that is broken down by Transaction, but I need to change the orientation of it so the list is by the product (even if there are duplicate items) with all of the corresponding information from that specific transaction.
Since I'm not great at explaining I'll just get right into the example. I would really appreciate if someone could help me to figure out how to do this.

I have this table: information about the transaction/purchase, and then which product was purchased. Sometimes there's multiple products in multiple columns, sometimes there's just a single product

Buyer	Transaction	Time	Place	Product 1	Product 2	Product 3	Product 4
Joe	301	11:00	City1	Orange	Banana	Apple	Water
Bob	304	12:00	City2	Apple	Cat
Randy	308	11:30	City1	Dog
Joe	310	11:23	City3	Orange	Banana	Apple

<tbody>
</tbody>

So I would like to create a table using a formula/formulas to show this same table but oriented around the products -- with their corresponding Buyer, Transaction, Time, and Place information.

Its fine if the same product shows up twice, since it will have different transaction data. Like "orange" in this example".

Product	Buyer	Transaction	Time	Place
Orange	Joe	301	11:00	City1
Banana	Joe	301	11:00	City1
Apple	Joe	301	11:00	City1
Water	Joe	301	11:00	City1
Apple	Bob	304	12:00	City2
Cat	Bob	304	12:00	City2
Dog	Randy	308	11:30	City1
Orange	Joe	310	11:23	City3
Banana	Joe	310	11:23	City3
Apple	Joe	310	11:23	City3

<tbody>
</tbody>

Thanks for any help you can provide!

 

[Joe4]

So I would like to create a table using a formula/formulas to show this same table but oriented around the products

I don't know that I would be able to do that via formula. I would probably use VBA to do it. Is that an acceptable solution?

 

[Eric W]

I agree with Joe4, this kind of reorganization is usually better left to a macro. Nevertheless, here is one way to do it with formulas:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Buyer	Transaction	Time	Place	Product 1	Product 2	Product 3	Product 4		Product	Buyer	Transaction	Time	Place
2	Joe	301	11:00	City1	Orange	Banana	Apple	Water		Orange	Joe	301	11:00	City1
3	Bob	304	12:00	City2	Apple	Cat				Banana	Joe	301	11:00	City1
4	Randy	308	11:30	City1	Dog					Apple	Joe	301	11:00	City1
5	Joe	310	11:23	City3	Orange	Banana	Apple			Water	Joe	301	11:00	City1
6										Apple	Bob	304	12:00	City2
7										Cat	Bob	304	12:00	City2
8										Dog	Randy	308	11:30	City1
9										Orange	Joe	310	11:23	City3
10										Banana	Joe	310	11:23	City3
11										Apple	Joe	310	11:23	City3
12
13

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula J2 {=IFERROR(INDIRECT(TEXT(SMALL(IF($E$2:$H$10<>"",ROW($E$2:$H$10)*100+COLUMN($E$2:$H$10)),ROWS($J$2:$J2)),"R00C00"),0),"")} K2 {=IF($J2="","",INDEX(A$2:A$10,SMALL(IF($E$2:$H$10<>"",ROW($E$2:$H$10)-ROW($A$2)+1),ROWS($K$2:$K2))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Put the J2 formula in, and confirm with Control+Shift+Enter. Drag down as needed. Put the K2 formula in, confirm with Control+Shift+Enter, then drag down and to the right as needed.

 

[Joe4]

Nice job Eric!

 

[Qliky66]

Thank you this worked wonderfully