MATCH TRUE INDEX - Where does small come in to it??!!?

[Robbiehof]

Hi Guys, I am completely stuck on this and could really do with some help. I am trying to display a column title when a unit sale (indicated by a 1) is placed below in the rows. The current set up I have, which is working perfectly is =IFERROR(IF(COUNTA($AC5:$BT5)=0,"",INDEX($AC$4:$BT$4,MATCH(TRUE,INDEX($AC5:$BT5>0,0),0))),"")

Row 4 being the Product Names, and Row 5 being in Product sale. This displays a blank where there is no sale, and pulls the column title where a unit has been sold.

The problem I am having is where there have been two different products sold i one sale, so the above formula is only picking up the first occurrence it sees. I want to add a new column which searches the same field but picks up the second occurrence, third, fourth and ongoing. I thought this would be a case of =IFERROR(IF(COUNTA($AC5:$BT5)=0,"",INDEX($AC$4:$BT$4,SMALL(MATCH(TRUE,INDEX($AC5:$BT5>0,0),0),2))),"") with the addition I have made in bold but this ends up showing blank.

So in the below example which is smaller than the above, Column b works fine, but I need Column C to Show a blank if there isnt a second occurrance (Like in Row 2 and 3) and to Show "Product 3" in C4. Hopefully this explains?
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Sale Unit 1[/TD]
[TD]Sale Unit 2[/TD]
[TD][/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD]Product 3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Product 1[/TD]
[TD]Product 1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Product 2[/TD]
[TD]Product 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Product 2 [/TD]
[TD]Product2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



If anyone had any help, it would be much appreciated.

Cheers

Rob

 

[AhoyNC]

Maybe something like this:
Change ranges for your data.
Drag formula across and down.
This is an array formula and must be entered with

Excel Workbook
	A	B	C	D	E	F	G
1	1	Sale Unit 1	Sale Unit 2		Product 1	Product 2	Product 3
2	2	Product 1			1
3	3	Product 2				1
4	4	Product 2	Product 3			1	1
CTRL-SHIFT-ENTER.

 

[Robbiehof]

Maybe something like this:
Change ranges for your data.
Drag formula across and down.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

*	A	B	C	D	E	F	G
Product 1	*	*	*	*
Product 2	*	*	*	*
Product 2	Product 3	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 84px;"><col style="width: 114px;"><col style="width: 64px;"><col style="width: 70px;"><col style="width: 67px;"><col style="width: 78px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="bgcolor: #C0C0C0, align: right"]1[/TD]
[TD="bgcolor: #C0C0C0"]Sale Unit 1[/TD]
[TD="bgcolor: #C0C0C0"]Sale Unit 2[/TD]
[TD="bgcolor: #C0C0C0"]*[/TD]
[TD="bgcolor: #C0C0C0"]Product 1[/TD]
[TD="bgcolor: #C0C0C0"]Product 2[/TD]
[TD="bgcolor: #C0C0C0"]Product 3[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="bgcolor: #C0C0C0, align: right"]2[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="bgcolor: #C0C0C0, align: right"]3[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="bgcolor: #C0C0C0, align: right"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 {=IFERROR(INDEX($E$1:$G$1,SMALL(IF($E2:$G2=1,COLUMN($E$1:$G$1)-COLUMN($E$1)+1),COLUMNS($E$1:E1))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Well there you go, this is truly amazing!! Looks like I have some new functions to learn. I have one last questions, if for example F4 was "2", this formula does not pick it up, as it is searching for 1's only, I tried changing SMALL(IF($E2:$G2=1,COLUMN to SMALL(IF($E2:$G2=">.5",COLUMN or "<>0" but its having non of it. Could you kkindly advise?

Many thanks for your help so far!

Robbiehof

 

[AhoyNC]

You were close. Remove the quotes from the ">.5".
Based on your example you could change formula to:

=IFERROR(INDEX($E$1:$G$1,SMALL(IF($E2:$G2[COLOR=#ff0000]>=0.5[/COLOR],COLUMN($E$1:$G$1)-COLUMN($E$1)+1),COLUMNS($E$1:E$1))),"")
or
=IFERROR(INDEX($E$1:$G$1,SMALL(IF($E2:$G2[COLOR=#ff0000]<>""[/COLOR],COLUMN($E$1:$G$1)-COLUMN($E$1)+1),COLUMNS($E$1:E$1))),"")

This part of the formula COLUMN($E$1:$G$1)-COLUMN($E$1)+1) just returns the column numbers in the table (range). In this case {1,2,3}.
This part COLUMNS($E$1:E$1) returns K in the SMALL function. So E1:E1 returns 1 and as you drag across columns E1:F1 returns 2, etc.

Excel Workbook
	A	B	C	D	E	F	G
1	1	Sale Unit 1	Sale Unit 2		Product 1	Product 2	Product 3
2	2	Product 1			2
3	3	Product 2				1
4	4	Product 2	Product 3			3	1
Sheet

 

[Robbiehof]

You were close. Remove the quotes from the ">.5".
Based on your example you could change formula to:

=IFERROR(INDEX($E$1:$G$1,SMALL(IF($E2:$G2[COLOR=#ff0000]>=0.5[/COLOR],COLUMN($E$1:$G$1)-COLUMN($E$1)+1),COLUMNS($E$1:E$1))),"")
or
=IFERROR(INDEX($E$1:$G$1,SMALL(IF($E2:$G2[COLOR=#ff0000]<>""[/COLOR],COLUMN($E$1:$G$1)-COLUMN($E$1)+1),COLUMNS($E$1:E$1))),"")

This part of the formula COLUMN($E$1:$G$1)-COLUMN($E$1)+1) just returns the column numbers in the table (range). In this case {1,2,3}.
This part COLUMNS($E$1:E$1) returns K in the SMALL function. So E1:E1 returns 1 and as you drag across columns E1:F1 returns 2, etc.

*	A	B	C	D	E	F	G
Product 1	*	*	*	*
Product 2	*	*	*	*
Product 2	Product 3	*	*

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 44px;"><col style="width: 78px;"><col style="width: 76px;"><col style="width: 36px;"><col style="width: 79px;"><col style="width: 78px;"><col style="width: 81px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="bgcolor: #C0C0C0, align: right"]1[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Sale Unit 1[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Sale Unit 2[/TD]
[TD="bgcolor: #C0C0C0"]*[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Product 1[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Product 2[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Product 3[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="bgcolor: #C0C0C0, align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="bgcolor: #C0C0C0, align: right"]3[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="bgcolor: #C0C0C0, align: right"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 {=IFERROR(INDEX($E$1:$G$1,SMALL(IF($E2:$G2>0.5,COLUMN($E$1:$G$1)-COLUMN($E$1)+1),COLUMNS($E$1:E$1))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Ahh PERFECT! I had G2=>.5 whereas i needed G2>=.5.

thank you both for your help and speedy replies. This really is a great website.

Thanks again.

Robbiehof

 

[AhoyNC]

You're welcome. Thanks for the feedback.