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

Remaniak

New Member
Joined
May 2, 2007
Messages
36
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
BCDEFGH
1Supplier1Supplier2Supplier3Supplier4Supplier5Supplier6
2Lot100009505700
3Lot100007905700
4Lot141000045013500
5Lot100004801644
6Lot1000074059200
7Lot1012300007405642,5
8Lot100007402220
9Lot100007408598,8
10Lot200007008598,8
11Lot200007008598,8
12Lot200009707760
13Lot2000084540560
14Lot200008103796,47
Sheet1
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,
Book1
BCDEFGHIJK
1012  344
2Supplier 1Supplier 2Supplier 3Supplier 4Supplier 5Supplier 6Lot 1Supplier 1
3Lot 100009505700Supplier 2
4Lot 100007905700Supplier 5
5Lot 141000045013500Supplier 6
6Lot 100004801644 
7Lot 1000074059200 
8Lot 1012300007405642,5 
9Lot 100007402220 
10Lot 100007408598,8
11Lot 200007008598,8
12Lot 200007008598,8
13Lot 200009707760
14Lot 2000084540560
15Lot 200008103796,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

New Member
Joined
May 2, 2007
Messages
36
Thanks a lot!

However, the overview I need to make in the Analysis sheet is the following:
Book2
CDEFGHIJ
2Lot1234567
3Lot1Supplier1Supplier2Supplier5Supplier6
4Lot2Supplier5Supplier6
5Lot3
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

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
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
 

Forum statistics

Threads
1,181,100
Messages
5,928,058
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top