Hi All,
I am in sheet "Analysis" and my data is stored in sheet "1 on 1" (example of the data is shown at the bottom).
What I need to know is for "lot 1", which column headers in row 1 have values more than 0.
I am using the following formula:
=IF(COLUMNS($E141:E141)>SUMPRODUCT(--(INDEX('1 on 1'!$B$2:$EQ$50000;MATCH("lot 1";'1 on 1'!$B$2:$B$50000;0);0)>0));"";INDEX('1 on 1'!$B$1:$EQ$1;SMALL(IF(INDEX('1 on 1'!$B$2:$EQ$50000;MATCH("lot 1";'1 on 1'!$B$2:$B$50000;0);0)>0;COLUMN($B:$EQ)-COLUMN($B:$B)+1);COLUMNS($E141:E141)))))
However, it gives me supplier 5, and supplier 6. While supplier 1 and 2 also have values more than 0, but are several rows lower than supplier 5 and 6, and thus are not included anymore.
It there a workaround for this?
Thanks.
Remaniak
I am in sheet "Analysis" and my data is stored in sheet "1 on 1" (example of the data is shown at the bottom).
What I need to know is for "lot 1", which column headers in row 1 have values more than 0.
I am using the following formula:
=IF(COLUMNS($E141:E141)>SUMPRODUCT(--(INDEX('1 on 1'!$B$2:$EQ$50000;MATCH("lot 1";'1 on 1'!$B$2:$B$50000;0);0)>0));"";INDEX('1 on 1'!$B$1:$EQ$1;SMALL(IF(INDEX('1 on 1'!$B$2:$EQ$50000;MATCH("lot 1";'1 on 1'!$B$2:$B$50000;0);0)>0;COLUMN($B:$EQ)-COLUMN($B:$B)+1);COLUMNS($E141:E141)))))
However, it gives me supplier 5, and supplier 6. While supplier 1 and 2 also have values more than 0, but are several rows lower than supplier 5 and 6, and thus are not included anymore.
It there a workaround for this?
Thanks.
Remaniak
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | |||
1 | Supplier1 | Supplier2 | Supplier3 | Supplier4 | Supplier5 | Supplier6 | |||
2 | Lot1 | 0 | 0 | 0 | 0 | 950 | 5700 | ||
3 | Lot1 | 0 | 0 | 0 | 0 | 790 | 5700 | ||
4 | Lot1 | 410 | 0 | 0 | 0 | 450 | 13500 | ||
5 | Lot1 | 0 | 0 | 0 | 0 | 480 | 1644 | ||
6 | Lot1 | 0 | 0 | 0 | 0 | 740 | 59200 | ||
7 | Lot1 | 0 | 12300 | 0 | 0 | 740 | 5642,5 | ||
8 | Lot1 | 0 | 0 | 0 | 0 | 740 | 2220 | ||
9 | Lot1 | 0 | 0 | 0 | 0 | 740 | 8598,8 | ||
10 | Lot2 | 0 | 0 | 0 | 0 | 700 | 8598,8 | ||
11 | Lot2 | 0 | 0 | 0 | 0 | 700 | 8598,8 | ||
12 | Lot2 | 0 | 0 | 0 | 0 | 970 | 7760 | ||
13 | Lot2 | 0 | 0 | 0 | 0 | 845 | 40560 | ||
14 | Lot2 | 0 | 0 | 0 | 0 | 810 | 3796,47 | ||
Sheet1 |