Have a list without any blank cell.

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
252
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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
ABCDEFGHIJKLMNOPQ
1ARTPOCOLSIZEQTYARTPOCOLXSSLXLMXXL TTL
2WTRT122B94XS1001WTRT122B94100200    300
3WTRT122B94S2002         0
4WTRTB22B94S3001WTRTB22B94 300    300
5WTRT123E100L4001WTRT123E100600 400   1000
6WTRT123B94XL5001WTRT123B94   500  500
7WTRT123E100XS6002         0
8WTRTB23E100L7001WTRTB23E100  700   700
9WTRTB24F200M8001WTRTB24F200    800 800
10WTRT125B94L9001WTRT125B94  900   900
11WTRTC25F200S10001WTRTC25F200 1000  1100 2100
12WTRTC25F200M11002         0
13WTRT124C94L12001WTRT124C94  1200   1200
14WTRTC25D94XL13001WTRTC25D94   1300  1300
15WTRTD26D94XXL14001WTRTD26D94     14001400
16WTRTD26E94XL15001WTRTD26E94   1500  1500
17WTRTD-D94XXL16001WTRTD-D94   1700 16003300
18WTRTD-D94XL17002         15300
Sheet2 (3)
Cell Formulas
RangeFormula
J1:P1J1=IFERROR(INDEX($D$2:$D$16,MATCH(0,INDEX(COUNTIF($I$1:I1,$D$2:$D$16),),0)),"")
F2:F18F2=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)
G2:I18G2=IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)<=1,A2,"")
J2:O17J2=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:O18J18=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:Q17Q2=SUM(J2:P2)
Q18Q18=SUM(Q2:Q17)
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,026
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQ
1ARTPOCOLSIZEQTYARTPOCOLXSSLXLMXXL TTL
2WTRT122B94XS100WTRT122B94100200     300
3WTRT122B94S200WTRTB22B94 300     300
4WTRTB22B94S300WTRT123E100600 400    1000
5WTRT123E100L400WTRT123B94   500   500
6WTRT123B94XL500WTRTB23E100  700    700
7WTRT123E100XS600WTRTB24F200    800  800
8WTRTB23E100L700WTRT125B94  900    900
9WTRTB24F200M800WTRTC25F200 1000  1100  2100
10WTRT125B94L900WTRT124C94  1200    1200
11WTRTC25F200S1000WTRTC25D94   1300   1300
12WTRTC25F200M1100WTRTD26D94     1400 1400
13WTRT124C94L1200WTRTD26E94   1500   1500
14WTRTC25D94XL1300WTRTD-D94   1700 1600 3300
15WTRTD26D94XXL1400          0
16WTRTD26E94XL1500          0
17WTRTD-D94XXL1600          0
18WTRTD-D94XL1700          15300
Sheet3
Cell Formulas
RangeFormula
J1:P1J1=IFERROR(INDEX($D$2:$D$16,MATCH(0,INDEX(COUNTIF($I$1:I1,$D$2:$D$16),),0)),"")
G2:I17G2=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:P18J2=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:Q17Q2=SUM(J2:P2)
G18:I18G18=IF(COUNTIFS($A$2:$A18,$A18,$B$2:$B18,$B18,$C$2:$C18,$C18)<=1,A18,"")
Q18Q18=SUM(Q2:Q17)
Press CTRL+SHIFT+ENTER to enter array formulas.
 

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
252
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,397
Maybe with a Helper column (in gray)

Pasta1
ABCDEFGHIJKLMNOPQ
1ARTPOCOLSIZEQTYHelperARTPOCOLXSSLXLMXXL TTL
2WTRT122B94XS1001WTRT122B94100200    300
3WTRT122B94S2001WTRTB22B94 300    300
4WTRTB22B94S3002WTRT123E100600 400   1000
5WTRT123E100L4003WTRT123B94   500  500
6WTRT123B94XL5004WTRTB23E100  700   700
7WTRT123E100XS6004WTRTB24F200    800 800
8WTRTB23E100L7005WTRT125B94  900   900
9WTRTB24F200M8006WTRTC25F200 1000  1100 2100
10WTRT125B94L9007WTRT124C94  1200   1200
11WTRTC25F200S10008WTRTC25D94   1300  1300
12WTRTC25F200M11008WTRTD26D94     14001400
13WTRT124C94L12009WTRTD26E94   1500  1500
14WTRTC25D94XL130010WTRTD-D94   1700 16003300
15WTRTD26D94XXL140011  
16WTRTD26E94XL150012  
17WTRTD-D94XXL160013  
18WTRTD-D94XL170013  
1915300
Plan2
Cell Formulas
RangeFormula
J1:P1J1=IFERROR(INDEX($D$2:$D$18,MATCH(0,INDEX(COUNTIF($I$1:I1,$D$2:$D$18),),0)),"")
F2:F18F2=MAX(F$1:F1)+IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)=1,1,0)
G15:G18,G2:I14G2=IFERROR(INDEX(A$2:A$18,MATCH(COUNTA(G$1:G1),$F$2:$F$18,0)),"")
J2:O14J2=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:Q18Q2=IF(G2="","",SUM(J2:P2))
Q19Q19=SUM(Q2:Q18)


Hope this helps

M.
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,937
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Have you considered a Pivot Table?
 

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
252
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
@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

Well-known Member
Joined
Feb 26, 2007
Messages
5,937
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Book6
ABCDEFGHIJKLMNO
1ARTPOCOLSIZEQTYSum of QTYSIZE
2WTRT122B94XS100ARTPOCOLLMSXLXSXXL
3WTRT122B94S200WTRT122B94200100
4WTRTB22B94S30023B94500
5WTRT123E100L400E100400600
6WTRT123B94XL50024C941200
7WTRT123E100XS60025B94900
8WTRTB23E100L700WTRTB22B94300
9WTRTB24F200M80023E100700
10WTRT125B94L90024F200800
11WTRTC25F200S1000WTRTC25D941300
12WTRTC25F200M1100F20011001000
13WTRT124C94L1200WTRTD26D941400
14WTRTC25D94XL1300E941500
15WTRTD26D94XXL1400-D9417001600
16WTRTD26E94XL1500
17WTRTD-D94XXL1600
18WTRTD-D94XL1700
Sheet1
 

Attachments

  • Capture.JPG
    Capture.JPG
    37.5 KB · Views: 2

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,026
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,422
Messages
5,547,834
Members
410,813
Latest member
Vhinzvirgo
Top