Removing blank entries and moving to the left

[Maciej1986]

Hello everyone,

I have a table of customers who purchased different quantities of different products. Some of them didn't purchase anything. I need to use a non-destructive & updatable way (delete and shift empty cells is a no-no so I think formula or a macro) to assign products and their quantities to customers in a single row, without empty cells and aligned to left. This data will be then fetched by mailmerge in Word document so i need each customer to stay as one record.
Ideally the new table will be to the right of the old one & they will stay in sync.
Have a look below:

Anyone has any suggestions?

 

[wideboydixon]

May need adjusting according to where your source / target tables reside but something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Product 1	Product 2	Product 3	Product 4	Product 5	Product 6	Product 7
2	Customer 1			5			2
3	Customer 2	3
4	Customer 3
5	Customer 4					1
6	Customer 5		6	1
7
8	Customer 1	Product 3	5	Product 6	2
9	Customer 2	Product 1	3
10	Customer 3
11	Customer 4	Product 5	1
12	Customer 5	Product 2	6	Product 3	1
Sheet1

Cell Formulas
Range	Formula
B8	{=IFERROR(INDEX($B$1:$H$1,SMALL(IF($B2:$H2>0,COLUMN($B2:$H2)-1),COLUMN()/2)),"")}
C8	{=IFERROR(INDEX($B2:$H2,SMALL(IF($B2:$H2>0,COLUMN($B2:$H2)-1),(COLUMN()-1)/2)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy the formulas in B8/C8 across and down.

WBD

 

[bosco_yip]

Try……….

1] Using wideboydixon's table layout in post #2

2] In B8, formula copy across to E8 and all copy down :

=IFERROR(INDEX(($B$1:$H$1,$B2:$H2),0,AGGREGATE(15,6,(COLUMN($B2:$H2)-1)/($B2:$H2>0),INT((COLUMN(A1)-1)/2)+1),MOD(COLUMN(A1)+1,2)+1),"")

Regards

 

[Maciej1986]

That worked like a charm! Thanks guys fro prompt reply

 

[Maciej1986]

Try……….

1] Using wideboydixon's table layout in post #2

2] In B8, formula copy across to E8 and all copy down :

=IFERROR(INDEX(($B$1:$H$1,$B2:$H2),0,AGGREGATE(15,6,(COLUMN($B2:$H2)-1)/($B2:$H2>0),INT((COLUMN(A1)-1)/2)+1),MOD(COLUMN(A1)+1,2)+1),"")

Regards

One more question - when I insert a new column (lets say before A) - everything is messed up. I tried moving your reference fields around but I cant find logic behind that. Could you explain what should I alter if I wish to insert N new columns on the left of your table?