Trying to create Summary Sheet from Order Form with no empty rows

[crosskonaftw]

I have a master order form with say 10 SKUs for different products (for simplicity). I want to create a separate sheet that SUMMARIZES only the items that are being ordered (quantity 1 or greater).

My current "dirty" solution is to use a formula that compares if the quantity column is >= 1, and if so, copy the contents of the row cell by cell. The problem with this is that is leaves a blank line whenever a item is NOT ordered.

I would like to find a solution, preferably by formula instead of VB macro, that will check the quantity column and copy it to the summary sheet if quantity is 1 or more, and skip to the next SKU if quantity column for that item is 0 or blank.

I have seen some posts online about using a combination of array formulas, match, etc.. I am somewhat familiar with advanced formula use, but not so much with arrays and advanced nesting. Any ideas? Thanks all in advance for your help

See below for example:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]ITEM[/TD]
[TD]PRICE[/TD]
[TD]QTY[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]SKU 1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SKU 2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]SKU 3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]SKU 4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SKU 5[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]SKU 6[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SKU 7[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]SKU 8[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]SKU 9[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SKU 10[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

and resulting summary sheet to show:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]ITEM[/TD]
[TD]PRICE[/TD]
[TD]QTY[/TD]
[TD]TOTAL[/TD]
[/TR]
[TR]
[TD]SKU 2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]SKU 3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]SKU 5[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]SKU 7[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]SKU 8[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]SKU 10[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Pup Denab]

Hi crosskonaftw

This is not the best solution, however it requires no vba or macros to run, all you need do is copy & paste to excel, you will see the data you want, but not the formulas
Regards
Pup

it creates a ref, which you can use to identify the rows you want, then just do want you have already done, but using the ref to remove the blank rows

<colgroup><col style="mso-width-source:userset;mso-width-alt:3803;width:156pt" width="208"> <col style="mso-width-source:userset;mso-width-alt:914;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:804;width:33pt" width="44"> <col style="mso-width-source:userset;mso-width-alt:585;width:24pt" width="32"> <col style="mso-width-source:userset;mso-width-alt:877;width:36pt" width="48"> <col style="mso-width-source:userset;mso-width-alt:731;width:30pt" width="40"> <col style="mso-width-source:userset;mso-width-alt:548;width:23pt" width="30"> <col style="mso-width-source:userset;mso-width-alt:4388; width:180pt" span="2" width="240"> <col style="mso-width-source:userset;mso-width-alt:4425;width:182pt" width="242"> <col style="mso-width-source:userset;mso-width-alt:4315;width:177pt" width="236"> </colgroup><tbody>
[TD="class: xl65, width: 208"]Ref Create[/TD]
[TD="class: xl66, width: 50"]ITEM[/TD]
[TD="class: xl66, width: 44"]PRICE[/TD]
[TD="class: xl66, width: 32"]QTY[/TD]
[TD="class: xl66, width: 48"]TOTAL[/TD]
[TD="class: xl65, width: 40"][/TD]
[TD="class: xl65, width: 30"]Ref[/TD]
[TD="class: xl66, width: 240"]ITEM[/TD]
[TD="class: xl66, width: 240"]PRICE[/TD]
[TD="class: xl66, width: 242"]QTY[/TD]
[TD="class: xl66, width: 236"]TOTAL[/TD]

[TD="class: xl65"]=IF(E2="","",COUNT($E$2:E2))[/TD]
[TD="class: xl66, width: 50"]SKU 1[/TD]
[TD="class: xl66, width: 44"]1[/TD]
[TD="class: xl66, width: 32"][/TD]
[TD="class: xl66, width: 48"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]=IFERROR(VLOOKUP(G2,A:B,2,0),"")[/TD]
[TD="class: xl65"]=IF(H2=""," ",(SUMIF(A:A,G2,C:C)))[/TD]
[TD="class: xl65"]=IF(H2=""," ",(SUMIF(A:A,G2,D:D)))[/TD]
[TD="class: xl65"]=IF(H2=""," ",(SUMIF(A:A,G2,E:E)))[/TD]

[TD="class: xl65"]=IF(E3="","",COUNT($E$2:E3))[/TD]
[TD="class: xl66, width: 50"]SKU 2[/TD]
[TD="class: xl66, width: 44"]2[/TD]
[TD="class: xl66, width: 32"]1[/TD]
[TD="class: xl66, width: 48"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]=IFERROR(VLOOKUP(G3,A:B,2,0),"")[/TD]
[TD="class: xl65"]=IF(H3=""," ",(SUMIF(A:A,G3,C:C)))[/TD]
[TD="class: xl65"]=IF(H3=""," ",(SUMIF(A:A,G3,D:D)))[/TD]
[TD="class: xl65"]=IF(H3=""," ",(SUMIF(A:A,G3,E:E)))[/TD]

[TD="class: xl65"]=IF(E4="","",COUNT($E$2:E4))[/TD]
[TD="class: xl66, width: 50"]SKU 3[/TD]
[TD="class: xl66, width: 44"]3[/TD]
[TD="class: xl66, width: 32"]1[/TD]
[TD="class: xl66, width: 48"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]=IFERROR(VLOOKUP(G4,A:B,2,0),"")[/TD]
[TD="class: xl65"]=IF(H4=""," ",(SUMIF(A:A,G4,C:C)))[/TD]
[TD="class: xl65"]=IF(H4=""," ",(SUMIF(A:A,G4,D:D)))[/TD]
[TD="class: xl65"]=IF(H4=""," ",(SUMIF(A:A,G4,E:E)))[/TD]

[TD="class: xl65"]=IF(E5="","",COUNT($E$2:E5))[/TD]
[TD="class: xl66, width: 50"]SKU 4[/TD]
[TD="class: xl66, width: 44"]5[/TD]
[TD="class: xl66, width: 32"][/TD]
[TD="class: xl66, width: 48"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]=IFERROR(VLOOKUP(G5,A:B,2,0),"")[/TD]
[TD="class: xl65"]=IF(H5=""," ",(SUMIF(A:A,G5,C:C)))[/TD]
[TD="class: xl65"]=IF(H5=""," ",(SUMIF(A:A,G5,D:D)))[/TD]
[TD="class: xl65"]=IF(H5=""," ",(SUMIF(A:A,G5,E:E)))[/TD]

[TD="class: xl65"]=IF(E6="","",COUNT($E$2:E6))[/TD]
[TD="class: xl66, width: 50"]SKU 5[/TD]
[TD="class: xl66, width: 44"]3[/TD]
[TD="class: xl66, width: 32"]3[/TD]
[TD="class: xl66, width: 48"]9[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]=IFERROR(VLOOKUP(G6,A:B,2,0),"")[/TD]
[TD="class: xl65"]=IF(H6=""," ",(SUMIF(A:A,G6,C:C)))[/TD]
[TD="class: xl65"]=IF(H6=""," ",(SUMIF(A:A,G6,D:D)))[/TD]
[TD="class: xl65"]=IF(H6=""," ",(SUMIF(A:A,G6,E:E)))[/TD]

[TD="class: xl65"]=IF(E7="","",COUNT($E$2:E7))[/TD]
[TD="class: xl66, width: 50"]SKU 6[/TD]
[TD="class: xl66, width: 44"]2[/TD]
[TD="class: xl66, width: 32"][/TD]
[TD="class: xl66, width: 48"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]=IFERROR(VLOOKUP(G7,A:B,2,0),"")[/TD]
[TD="class: xl65"]=IF(H7=""," ",(SUMIF(A:A,G7,C:C)))[/TD]
[TD="class: xl65"]=IF(H7=""," ",(SUMIF(A:A,G7,D:D)))[/TD]
[TD="class: xl65"]=IF(H7=""," ",(SUMIF(A:A,G7,E:E)))[/TD]

[TD="class: xl65"]=IF(E8="","",COUNT($E$2:E8))[/TD]
[TD="class: xl66, width: 50"]SKU 7[/TD]
[TD="class: xl66, width: 44"]2[/TD]
[TD="class: xl66, width: 32"]2[/TD]
[TD="class: xl66, width: 48"]4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]=IFERROR(VLOOKUP(G8,A:B,2,0),"")[/TD]
[TD="class: xl65"]=IF(H8=""," ",(SUMIF(A:A,G8,C:C)))[/TD]
[TD="class: xl65"]=IF(H8=""," ",(SUMIF(A:A,G8,D:D)))[/TD]
[TD="class: xl65"]=IF(H8=""," ",(SUMIF(A:A,G8,E:E)))[/TD]

[TD="class: xl65"]=IF(E9="","",COUNT($E$2:E9))[/TD]
[TD="class: xl66, width: 50"]SKU 8[/TD]
[TD="class: xl66, width: 44"]5[/TD]
[TD="class: xl66, width: 32"]1[/TD]
[TD="class: xl66, width: 48"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]=IFERROR(VLOOKUP(G9,A:B,2,0),"")[/TD]
[TD="class: xl65"]=IF(H9=""," ",(SUMIF(A:A,G9,C:C)))[/TD]
[TD="class: xl65"]=IF(H9=""," ",(SUMIF(A:A,G9,D:D)))[/TD]
[TD="class: xl65"]=IF(H9=""," ",(SUMIF(A:A,G9,E:E)))[/TD]

[TD="class: xl65"]=IF(E10="","",COUNT($E$2:E10))[/TD]
[TD="class: xl66, width: 50"]SKU 9[/TD]
[TD="class: xl66, width: 44"]1[/TD]
[TD="class: xl66, width: 32"][/TD]
[TD="class: xl66, width: 48"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]=IFERROR(VLOOKUP(G10,A:B,2,0),"")[/TD]
[TD="class: xl65"]=IF(H10=""," ",(SUMIF(A:A,G10,C:C)))[/TD]
[TD="class: xl65"]=IF(H10=""," ",(SUMIF(A:A,G10,D:D)))[/TD]
[TD="class: xl65"]=IF(H10=""," ",(SUMIF(A:A,G10,E:E)))[/TD]

[TD="class: xl65"]=IF(E11="","",COUNT($E$2:E11))[/TD]
[TD="class: xl66, width: 50"]SKU 10[/TD]
[TD="class: xl66, width: 44"]4[/TD]
[TD="class: xl66, width: 32"]1[/TD]
[TD="class: xl66, width: 48"]4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]=IFERROR(VLOOKUP(G11,A:B,2,0),"")[/TD]
[TD="class: xl65"]=IF(H11=""," ",(SUMIF(A:A,G11,C:C)))[/TD]
[TD="class: xl65"]=IF(H11=""," ",(SUMIF(A:A,G11,D:D)))[/TD]
[TD="class: xl65"]=IF(H11=""," ",(SUMIF(A:A,G11,E:E)))[/TD]

</tbody>

 

[Peter_SSs]

I would consider using some helper columns as it keeps the formulas fairly simple.
The helper columns can be hidden once populated with the formulas.

In the original sheet (Sheet1 for me):
F1 is empty (or houses a 0)
F2 copied down as far as your data might ever go.
G1 is a stand-alone formula.

Excel Workbook
	A	B	C	D	E	F	G
1	ITEM	PRICE	QTY	TOTAL			6
2	SKU 1	1				0
3	SKU 2	2	1	2		1
4	SKU 3	3	1	3		2
5	SKU 4	5				2
6	SKU 5	3	3	9		3
7	SKU 6	2				3
8	SKU 7	2	2	4		4
9	SKU 8	5	1	5		5
10	SKU 9	1				5
11	SKU 10	4	1	4		6
12
Sheet1

In the other sheet (Sheet2 for me):
Column G is manually populated as shown as far down as you might ever need
Formula in A2 is copied across and down.

Excel Workbook
	A	B	C	D	E	F	G
1	ITEM	PRICE	QTY	TOTAL
2	SKU 2	2	1	2			1
3	SKU 3	3	1	3			2
4	SKU 5	3	3	9			3
5	SKU 7	2	2	4			4
6	SKU 8	5	1	5			5
7	SKU 10	4	1	4			6
8							7
9							8
10							9
11							10
Sheet2

 

[crosskonaftw]

Peter and Pup,

Thank you so very much for your input. I spent an hour diagramming out Peter's solution and writing out in plain text what each part of each functions does. FINALLY.. it makes sense to me what you are doing.

I applied it to my real life application spreadsheet and got it to work, albeit a simplified version i.e. I had to delete rows that were used to designate categories until I figure out how to fit them in without breaking the formula

The next couple days I will work through Pup's solution and report back..

---

A clarifying question, the index function takes 3 arguments, (ARRAY, ROW_NUMBER, COLUMN_NUMBER) in our case, ARRAY is a single column and row number is returned by MATCH function, so there is no problem.

I did some testing with a 15x5 array (A1 through E15) and found the following:

• If I don't specify a row argument, but DO specify a column argument =INDEX(A1:E15,,3) it evaluates OK
• If I don't specify a column argument, but DO specify a row argument =INDEX(A1:E15,3) or =INDEX(A1:E15,3,) it returns an error

Is it because in the INDEX syntax, column number is in square brackets?

My conclusions in regard to INDEX function:

• If the array is either a single row or a single column by itself, you can safely skip ROW_NUMBER AND COLUMN_NUMBER arguments, respectively
• If the array is NOT a single row or column, you can safely not enter ROW_NUMBER, but must specify COLUMN_NUMBER

Is that correct? or am I on crack?

Sheet3

*	A	B	C	D	E
a1	b1	c1	d1	e1
a2	b2	c2	d2	e2
a3	b3	c3	d3	e3
a4	b4	c4	d4	e4
a5	b5	c5	d5	e5
a6	b6	c6	d6	e6
a7	b7	c7	d7	e7
a8	b8	c8	d8	e8
a9	b9	c9	d9	e9
a10	b10	c10	d10	e10
a11	b11	c11	d11	e11
a12	b12	c12	d12	e12
a13	b13	c13	d13	e13
a14	b14	c14	d14	e14
a15	b15	c15	d15	e15

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

I had to delete rows that were used to designate categories until I figure out how to fit them in without breaking the formula

Post back with more details and sample data/results if you need more help with that.

If I don't specify a row argument, but DO specify a column argument =INDEX(A1:E15,,3) it evaluates OK

What value does it return from your sample data and why do you think that is the "correct" answer?

I would only omit an argument if dealing with a single column or row array.

Have a careful look at these formulas and results and perhaps you'll see what is happening.

Excel Workbook
	A	B	C	D	E	F	G
1	a1	b1	c1	d1	e1
2	a2	b2	c2	d2	e2
3	a3	b3	c3	d3	e3		b3
4	a4	b4	c4	d4	e4		b4
5	a5	b5	c5	d5	e5		b5
6	a6	b6	c6	d6	e6
7	a7	b7	c7	d7	e7
8	a8	b8	c8	d8	e8
9	a9	b9	c9	d9	e9
10	a10	b10	c10	d10	e10
11	a11	b11	c11	d11	e11
12	a12	b12	c12	d12	e12
13	a13	b13	c13	d13	e13
14	a14	b14	c14	d14	e14
15	a15	b15	c15	d15	e15
16
17		b3	c3
INDEX