Finding cell 0 (2000 SR-1)

Topic

New Reply

Hi All…

I haven’t done much automation with Excel yet, so please forgive me if this is a dumb question…. … I can do this in Access, but a lot of good that does me…

Here’s what I have…. Row 6, Cells AZ to BQ and BU to CY, contains Region ID’s…. Row 7 and up contains transaction records… The same cells will contain dollar values… All but 1 cell will be zero… I want to find that non-zero value and then find the Region ID above it in Row 6 (same column) … and place that ID in a cell at the end of each row…

30 31 32 36 37 38 39 41
– – (4,918) – – – – – I would want to see 32 here ———->
– – – – – – – – blank cell here ———->
– – 6,169 – – – – – 32 here ———>
– – – – (156,255) – – – 37 here ———->
– – – – – 13,104 – – etc.
(Oops… That doesn’t show up in columns for you… Shucks… )

Geez, I hope this makes sense to someone out there…
Any help would be appreciated!

TIA

Reply | Quote

Replies

You can use the following formula to find the RegionID for row 7, columns AZ to BQ:

=INDEX($AZ$6:$BQ$6,1,MATCH(TRUE,AZ7:BQ70,0))

entered as an array formula, i.e. confirm with Ctrl+Shift+Enter. You can fill this formula down as far as needed. It will return #N/A if there is no non-zero value in a row. You can hide this by setting a conditional format on the column with the formulas. Set the condition to Formula Is, =IsError(BS7) where BS7 is the first cell in the column with the formulas, and in the formatting, set the foreground (text) color equal to the background color. Instead of this, you might also use a formula that replaces #N/A by an empty value:
=IF(ISNA(INDEX($AZ$6:$BQ$6,1,MATCH(TRUE,AZ7:BQ70,0)),””,INDEX($AZ$6:$BQ$6,1,MATCH(TRUE,AZ7:BQ70,0)))

Reply | Quote

Thanks Hans! You’re the best!

I tweaked it a bit for other tests I had to do but it works perfectly… AND I learned a couple more Excel functions in the process… That’s always a good thing… Thanks again! Have a great day!

Reply | Quote

If the range of interest would just consist of AZ to BQ, the following non-array formula…

=INDEX($AZ$6:$BQ$6,MATCH(MAX(AZ7:BQ7),AZ7:BQ7,0))

would suffice.

Since you have 2 (noncontiguous) areas, that is, AZ:BQ and BU:CY, we need to tweak the foregoing formula…

=INDEX($AZ$6:$CY$6,IF(MAX(AZ7:BQ7),MATCH(MAX(AZ7:BQ7),AZ7:BQ7,0),MATCH(MAX(BU7:CY7),BU7:CY7,0)+COLUMNS(AZ7:BT7)))

which is ordinarily entered.

Reply | Quote

Thanks Aladin!

I tried your solution and it worked great except one thing… I failed to mention that the non-zero dollar amounts can be negative… There was one row with a negative value and that was the only one that returned an incorrect RegionID… I think the MAX stuff is where the problem is, but I’m not sure how I’d fix it… I’ll try and figure it out, for future reference…

Have a great day!

Reply | Quote

Trudi,

Replace MAX with SUM, you’ll be OK.

Aladin

Reply | Quote