Have a list without any blank cell.

[mehidy1437]

Hi There,

I'm re-arranging data from column A:E to G:O.
While re-arranging, I'm getting blank cell in soeme rows of column G, H & I, it's because of my current formula at cell g2, h2 & i2.

I need help to have the alternative formula for this 3 ( g2, h2 & i2) cell, to have the same result but without any blank cell.
A formula without CTRL+SHIFT+ENTER is preferable, if possible.

EXAMPLE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ART	PO	COL	SIZE	QTY		ART	PO	COL	XS	S	L	XL	M	XXL		TTL
2	WTRT1	22	B94	XS	100	1	WTRT1	22	B94	100	200						300
3	WTRT1	22	B94	S	200	2											0
4	WTRTB	22	B94	S	300	1	WTRTB	22	B94		300						300
5	WTRT1	23	E100	L	400	1	WTRT1	23	E100	600		400					1000
6	WTRT1	23	B94	XL	500	1	WTRT1	23	B94				500				500
7	WTRT1	23	E100	XS	600	2											0
8	WTRTB	23	E100	L	700	1	WTRTB	23	E100			700					700
9	WTRTB	24	F200	M	800	1	WTRTB	24	F200					800			800
10	WTRT1	25	B94	L	900	1	WTRT1	25	B94			900					900
11	WTRTC	25	F200	S	1000	1	WTRTC	25	F200		1000			1100			2100
12	WTRTC	25	F200	M	1100	2											0
13	WTRT1	24	C94	L	1200	1	WTRT1	24	C94			1200					1200
14	WTRTC	25	D94	XL	1300	1	WTRTC	25	D94				1300				1300
15	WTRTD	26	D94	XXL	1400	1	WTRTD	26	D94						1400		1400
16	WTRTD	26	E94	XL	1500	1	WTRTD	26	E94				1500				1500
17	WTRTD	-	D94	XXL	1600	1	WTRTD	-	D94				1700		1600		3300
18	WTRTD	-	D94	XL	1700	2											15300
Sheet2 (3)

Cell Formulas
Range	Formula
J1:P1	J1	=IFERROR(INDEX($D$2:$D$16,MATCH(0,INDEX(COUNTIF($I$1:I1,$D$2:$D$16),),0)),"")
F2:F18	F2	=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)
G2:I18	G2	=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)<=1,A2,"")
J2:O17	J2	=IFERROR(INDEX($E$2:$E$160,AGGREGATE(15,6,(ROW($A$2:$A$160)-ROW($A$2)+1)/($A$2:$A$160=$G2)/($B$2:$B$160=$H2)/($C$2:$C$160=$I2)/($D$2:$D$160=J$1),1)),"")
J18:O18	J18	=IFERROR(INDEX($E$2:$E$160,AGGREGATE(15,6,(ROW($A$2:$A$160)-ROW($A$2)+1)/($A$2:$A$160=$G18)/($B$2:$B$160=$H18)/($C$2:$C$160=$I18)/($D$2:$D$160=J$1),COLUMNS($A17:A17))),"")
Q2:Q17	Q2	=SUM(J2:P2)
Q18	Q18	=SUM(Q2:Q17)

 

[jasonb75]

A formula without CTRL+SHIFT+ENTER is preferable, if possible.

Any reason why?
For what you want to do a formula without CSE would be less efficient than one with CSE.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ART	PO	COL	SIZE	QTY		ART	PO	COL	XS	S	L	XL	M	XXL		TTL
2	WTRT1	22	B94	XS	100		WTRT1	22	B94	100	200						300
3	WTRT1	22	B94	S	200		WTRTB	22	B94		300						300
4	WTRTB	22	B94	S	300		WTRT1	23	E100	600		400					1000
5	WTRT1	23	E100	L	400		WTRT1	23	B94				500				500
6	WTRT1	23	B94	XL	500		WTRTB	23	E100			700					700
7	WTRT1	23	E100	XS	600		WTRTB	24	F200					800			800
8	WTRTB	23	E100	L	700		WTRT1	25	B94			900					900
9	WTRTB	24	F200	M	800		WTRTC	25	F200		1000			1100			2100
10	WTRT1	25	B94	L	900		WTRT1	24	C94			1200					1200
11	WTRTC	25	F200	S	1000		WTRTC	25	D94				1300				1300
12	WTRTC	25	F200	M	1100		WTRTD	26	D94						1400		1400
13	WTRT1	24	C94	L	1200		WTRTD	26	E94				1500				1500
14	WTRTC	25	D94	XL	1300		WTRTD	-	D94				1700		1600		3300
15	WTRTD	26	D94	XXL	1400												0
16	WTRTD	26	E94	XL	1500												0
17	WTRTD	-	D94	XXL	1600												0
18	WTRTD	-	D94	XL	1700												15300
Sheet3

Cell Formulas
Range	Formula
J1:P1	J1	=IFERROR(INDEX($D$2:$D$16,MATCH(0,INDEX(COUNTIF($I$1:I1,$D$2:$D$16),),0)),"")
G2:I17	G2	=IFERROR(INDEX(A$2:A$18,MATCH(TRUE,COUNTIFS($G$1:G1,$A$2:$A$18,$H$1:H1,$B$2:$B$18,$I$1:I1,$C$2:$C$18)={0},0)),"")
J2:P18	J2	=IFERROR(1/(1/SUMIFS($E$2:$E$18,$A$2:$A$18,$G2,$B$2:$B$18,$H2,$C$2:$C$18,$I2,$D$2:$D$18,J$1)),"")
Q2:Q17	Q2	=SUM(J2:P2)
G18:I18	G18	=IF(COUNTIFS($A$2:$A18,$A18,$B$2:$B18,$B18,$C$2:$C18,$C18)<=1,A18,"")
Q18	Q18	=SUM(Q2:Q17)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[mehidy1437]

Hi Jasonb75, thanks alot for your help.Actually more data will be added in coumn A:E daily basis,also this data will be transfer to other sheet, that's why i want to avoid CSE, to avoid any issue in case, hope you understand. Is it possible to have a optional formula without CSE?

 

[Marcelo Branco]

Maybe with a Helper column (in gray)

Pasta1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ART	PO	COL	SIZE	QTY	Helper	ART	PO	COL	XS	S	L	XL	M	XXL		TTL
2	WTRT1	22	B94	XS	100	1	WTRT1	22	B94	100	200						300
3	WTRT1	22	B94	S	200	1	WTRTB	22	B94		300						300
4	WTRTB	22	B94	S	300	2	WTRT1	23	E100	600		400					1000
5	WTRT1	23	E100	L	400	3	WTRT1	23	B94				500				500
6	WTRT1	23	B94	XL	500	4	WTRTB	23	E100			700					700
7	WTRT1	23	E100	XS	600	4	WTRTB	24	F200					800			800
8	WTRTB	23	E100	L	700	5	WTRT1	25	B94			900					900
9	WTRTB	24	F200	M	800	6	WTRTC	25	F200		1000			1100			2100
10	WTRT1	25	B94	L	900	7	WTRT1	24	C94			1200					1200
11	WTRTC	25	F200	S	1000	8	WTRTC	25	D94				1300				1300
12	WTRTC	25	F200	M	1100	8	WTRTD	26	D94						1400		1400
13	WTRT1	24	C94	L	1200	9	WTRTD	26	E94				1500				1500
14	WTRTC	25	D94	XL	1300	10	WTRTD	-	D94				1700		1600		3300
15	WTRTD	26	D94	XXL	1400	11
16	WTRTD	26	E94	XL	1500	12
17	WTRTD	-	D94	XXL	1600	13
18	WTRTD	-	D94	XL	1700	13
19																	15300
Plan2

Cell Formulas
Range	Formula
J1:P1	J1	=IFERROR(INDEX($D$2:$D$18,MATCH(0,INDEX(COUNTIF($I$1:I1,$D$2:$D$18),),0)),"")
F2:F18	F2	=MAX(F$1:F1)+IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)=1,1,0)
G15:G18,G2:I14	G2	=IFERROR(INDEX(A$2:A$18,MATCH(COUNTA(G$1:G1),$F$2:$F$18,0)),"")
J2:O14	J2	=IFERROR(INDEX($E$2:$E$160,AGGREGATE(15,6,(ROW($A$2:$A$160)-ROW($A$2)+1)/($A$2:$A$160=$G2)/($B$2:$B$160=$H2)/($C$2:$C$160=$I2)/($D$2:$D$160=J$1),1)),"")
Q2:Q18	Q2	=IF(G2="","",SUM(J2:P2))
Q19	Q19	=SUM(Q2:Q18)

Hope this helps

M.

 

[alansidman]

Have you considered a Pivot Table?

 

[mehidy1437]

@jasonb75 , your formula for J2 is more suitable than my original in this case, thanks a lot again. just, if could avoid CSE for G2 would be great.

@Marcelo Branco , thank you for the guidance, is it be possible to avoid the helper column for this?

@alansidman , I haven't use the Pivot Table yet, can you make one pivot table with my data to have the desired result, (formula solution is my first priority).

 

[alansidman]

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ART	PO	COL	SIZE	QTY		Sum of QTY			SIZE
2	WTRT1	22	B94	XS	100		ART	PO	COL	L	M	S	XL	XS	XXL
3	WTRT1	22	B94	S	200		WTRT1	22	B94			200		100
4	WTRTB	22	B94	S	300			23	B94				500
5	WTRT1	23	E100	L	400				E100	400				600
6	WTRT1	23	B94	XL	500			24	C94	1200
7	WTRT1	23	E100	XS	600			25	B94	900
8	WTRTB	23	E100	L	700		WTRTB	22	B94			300
9	WTRTB	24	F200	M	800			23	E100	700
10	WTRT1	25	B94	L	900			24	F200		800
11	WTRTC	25	F200	S	1000		WTRTC	25	D94				1300
12	WTRTC	25	F200	M	1100				F200		1100	1000
13	WTRT1	24	C94	L	1200		WTRTD	26	D94						1400
14	WTRTC	25	D94	XL	1300				E94				1500
15	WTRTD	26	D94	XXL	1400			-	D94				1700		1600
16	WTRTD	26	E94	XL	1500
17	WTRTD	-	D94	XXL	1600
18	WTRTD	-	D94	XL	1700
Sheet1

 

[mehidy1437]

Thanks a lot for this.

 

[jasonb75]

Actually more data will be added in coumn A:E daily basis,also this data will be transfer to other sheet, that's why i want to avoid CSE, to avoid any issue in case

A CSE formula will still work, you just need to make the ranges dynamic.

 

[mehidy1437]

Noted & thank you.