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?
<tbody>
</tbody>
If anyone had any help, it would be much appreciated.
Cheers
Rob
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?
A | B | C | D | E | F | G |
1 | Sale Unit 1 | Sale Unit 2 | Product 1 | Product 2 | Product 3 | |
2 | Product 1 | Product 1 | 1 | |||
3 | Product 2 | Product 2 | 1 | |||
4 | Product 2 | Product2 | 1 | 1 | ||
<tbody>
</tbody>
If anyone had any help, it would be much appreciated.
Cheers
Rob