Formula for returning text list and quantity from table

[esamk]

I have a named table of data e.g:

	A	B	C	D
1	P/N	DESCR	QTY	PRICE
2	001-abc	Nut self locking		$0.15
3	dc-107-012	Bolt flange head	2	$0.45
4	seg1089	Nut castle		$1.00
5	101908457	Pin cotter	16	$1.10

A/B formatted as text, C as general (to calculate totals separately). There are a lot more columns in the original but I didn't want to clutter up the example.

It has an order overview tab, which I would like to display only the items where a quantity has been added to the original table (excluding all the blanks). For the above example:

	A	B
1	DESCR	QTY
2	Bolt flange head	2
3	Pin cotter	16

 

[hatstand]

Hi, I would create a Pivot Table, set it as you like and remove the display options. PivotTable Options / Display, then untick all the Display boxes. This will give you a table that you need and look like a regular table.

 

[Toadstool]

Hi Esamk,

Does this do what you want?

Here's the Data tab:

Book3
	A	B	C	D
1	P/N	DESCR	QTY	PRICE
2	001-abc	Nut self locking		$0.15
3	dc-107-012	Bolt flange head	2	$0.45
4	seg1089	Nut castle		$1.00
5	101908457	Pin cotter	16	$1.10
Data

Here's the Order Overview tab

Book3
	A	B
1	DESC	QTY
2	Bolt flange head	2
3	Pin cotter	16
Order Overview

Cell Formulas
Range	Formula
A2:A3	A2	=IFERROR(INDEX(Data!$B$2:$B$999,AGGREGATE(15,6,ROW(Data!$B$2:$B$999)-ROW(Data!$B$1)/(Data!$C$2:$C$999<>""),ROW()-ROW($A$1))),"")
B2:B3	B2	=IFERROR(INDEX(Data!$C$2:$C$999,AGGREGATE(15,6,ROW(Data!$B$2:$B$999)-ROW(Data!$B$1)/(Data!$C$2:$C$999<>""),ROW()-ROW($A$1))),"")

 

[esamk]

Hi Esamk,

Does this do what you want?

- Perfect, exactly what I need. Had to change B2 from:

=IFERROR(INDEX(Data!$C$2:$C$999,AGGREGATE(15,6,ROW(Data!$B$2:$B$999)-ROW(Data!$B$1)/(Data!$C$2:$C$999<>""),ROW()-ROW($A$1))),"")

To:

=IFERROR(INDEX(Data!$C$2:$C$999,AGGREGATE(15,6,ROW(Data!$C$2:$C$999)-ROW(Data!$B$1)/(Data!$C$2:$C$999<>""),ROW()-ROW($A$2))),"")

For it to work properly. And had to set "$A$2" so it didn't skip the first row. Thank you so much!

Hi, I would create a Pivot Table, set it as you like and remove the display options. PivotTable Options / Display, then untick all the Display boxes. This will give you a table that you need and look like a regular table.

- Ideally, yes. However it needs to be print friendly which the table is not (too much data). That's why I need a formula.

Only one last issue to solve. Due to the unusual format of the overview sheet (has to print landscape annoyingly), I need the formula to work across two sets of columns. E.g:

	A	B	C	D
1	DESCR​	QTY​	DESCR​	QTY​
2	Bolt flange head	2​	R-Clip	100​
3	Pin cotter	16​	O-ring	50​
4	Widget S/S	22​	Bearing roller	12​

Just to add to the challenge... so when it reaches the last row (17/18) it needs to start again at the top a few columns over.

 

[Toadstool]

Hi Esamk,

You don't need to change Data!$B$2:$B$999 to Data!$C$2:$C$999 because it only needs to use the row numbers, not the actual content of those cells.

You don't need to change ROW($A$1) to ROW($A$2) unless your headings are actually in row 2. It's using "ROW()-ROW($A$1)" to figure out which occurrence to select (1st, 2nd, 3rd, etc) so if your headings are on row 1 and the formula starts row 2 then ROW()-ROW($A$1) = 2-1 = 1 so you get the first.

Here's some extra data to trigger the second column and a modified formula for your columns C and D.

Esamk2.xlsx
	A	B	C	D
1	P/N	DESCR	QTY	PRICE
2	001-abc	Nut self locking		$0.15
3	dc-107-012	Bolt flange head	2	$0.45
4	seg1089	Nut castle		$1.00
5	101908457	Pin cotter	16	$1.10
6	X-H6	Widget S/S	22	$0.15
7	X-H7	R-Clip	100	$0.45
8	X-H8	O-ring	50	$1.00
9	X-H9	Bearing roller	12	$1.10
10	X-H10	L/H Widget nut	4	$0.15
11	X-H11	L/H Widget bolt	3	$0.45
12	X-H12	R/H Widget nut	5	$1.00
13	X-H13	R/H Widget bolt	6	$1.10
14	X-H14	O/set x/over driver	7	$0.15
15	X-H15	Lug nut	8	$0.45
16	X-H16	P-bracket	9	$1.00
17	X-H17	Jumper 17	2	$1.10
18	X-H18	Jumper 18	1	$0.15
19	X-H19	Jumper 19	11	$0.45
20	X-H20	Jumper 20	12	$1.00
21	X-H21	Jumper 21	44	$1.10
22	X-H22	Jumper 22	66	$1.00
23	X-H23	Jumper 23	88	$1.10
Data

Esamk2.xlsx
	A	B	C	D
1	DESC	QTY	DESC	QTY
2	Bolt flange head	2	Jumper 21	44
3	Pin cotter	16	Jumper 22	66
4	Widget S/S	22	Jumper 23	88
5	R-Clip	100
6	O-ring	50
7	Bearing roller	12
8	L/H Widget nut	4
9	L/H Widget bolt	3
10	R/H Widget nut	5
11	R/H Widget bolt	6
12	O/set x/over driver	7
13	Lug nut	8
14	P-bracket	9
15	Jumper 17	2
16	Jumper 18	1
17	Jumper 19	11
18	Jumper 20	12
Order Overview

Cell Formulas
Range	Formula
A2:A18	A2	=IFERROR(INDEX(Data!$B$2:$B$999,AGGREGATE(15,6,ROW(Data!$B$2:$B$999)-ROW(Data!$B$1)/(Data!$C$2:$C$999<>""),ROW()-ROW($A$1))),"")
B2:B18	B2	=IFERROR(INDEX(Data!$C$2:$C$999,AGGREGATE(15,6,ROW(Data!$B$2:$B$999)-ROW(Data!$B$1)/(Data!$C$2:$C$999<>""),ROW()-ROW($A$1))),"")
C2:C18	C2	=IFERROR(INDEX(Data!$B$2:$B$999,AGGREGATE(15,6,ROW(Data!$B$2:$B$999)-ROW(Data!$B$1)/(Data!$C$2:$C$999<>""),ROW()-ROW($A$1)+COUNTIF($B$2:$B$18,"<>"))),"")
D2:D18	D2	=IFERROR(INDEX(Data!$C$2:$C$999,AGGREGATE(15,6,ROW(Data!$B$2:$B$999)-ROW(Data!$B$1)/(Data!$C$2:$C$999<>""),ROW()-ROW($A$1)+COUNTIF($B$2:$B$18,"<>"))),"")

 

[esamk]

Here's some extra data to trigger the second column and a modified formula for your columns C and D.

Hi Toadstool

That's excellent, works great! Thank you so much for you help , If only I could get back the wasted hours googling the problem and trying to work it out myself haha. Now I just need to figure out how to mark the thread as solved...

Much appreciated

 

[Toadstool]

Glad it worked for you and thanks for the feedback.