My array formula stops once the first row matches...

[Remaniak]

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

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

 

[Krishnakumar]

Hi,

Book1
	B	C	D	E	F	G	H	I	J	K
1	0	1	2			3	4		4
2		Supplier 1	Supplier 2	Supplier 3	Supplier 4	Supplier 5	Supplier 6		Lot 1	Supplier 1
3	Lot 1	0	0	0	0	950	5700			Supplier 2
4	Lot 1	0	0	0	0	790	5700			Supplier 5
5	Lot 1	410	0	0	0	450	13500			Supplier 6
6	Lot 1	0	0	0	0	480	1644
7	Lot 1	0	0	0	0	740	59200
8	Lot 1	0	12300	0	0	740	5642,5
9	Lot 1	0	0	0	0	740	2220
10	Lot 1	0	0	0	0	740	8598,8
11	Lot 2	0	0	0	0	700	8598,8
12	Lot 2	0	0	0	0	700	8598,8
13	Lot 2	0	0	0	0	970	7760
14	Lot 2	0	0	0	0	845	40560
15	Lot 2	0	0	0	0	810	3796,47
Sheet2

Formulas

In C1 and copied across,

=IF(SUMPRODUCT(--($B$3:$B$15=$J$2),C$3:C$15)>0,LOOKUP(9.9999E+307,$B$1:B1)+1,"")

J1:

=LOOKUP(9.99999E+307,B1:H1)

K2 and copied down,

=IF(ROWS($A$2:$A2)<=$J$1,LOOKUP(ROWS($A$2:$A2),$C$1:$H$1,$C$2:$H$2),"")

HTH

 

[Remaniak]

Thanks a lot!

However, the overview I need to make in the Analysis sheet is the following:

Book2
	C	D	E	F	G	H	I	J
2	Lot	1	2	3	4	5	6	7
3	Lot1	Supplier1	Supplier2	Supplier5	Supplier6
4	Lot2	Supplier5	Supplier6
5	Lot3
Sheet1

Also, I do not know what rows contains which lots. Is there no way to modify the array formula to not just check the first row it finds for "lot 1" but also the next rows with "lot 1"?

The formula was:

=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)))))

Thanks!

 

[Krishnakumar]

Hi,

Download Morefunc add-in from here.

In K2 and copied across,

=INDEX($A$1:$H$1,SMALL(UNIQUEVALUES(IF($B$2:$B$14=$J2,IF($C$2:$H$14>0,COLUMN($C$1:$H$1)))),COLUMNS($K$2:K$2)))

where j2 houses Lot 1

Must be confirmed with CTRL+SHIFT+ENTER

HTH