Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding cell <> 0 (2000 SR-1)

    Hi All...

    I haven't done much automation with Excel yet, so please forgive me if this is a dumb question.... <img src=/S/shy.gif border=0 alt=shy width=15 height=15> ... I can do this in Access, but a lot of good that does me... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    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... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Any help would be appreciated!

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding cell <> 0 (2000 SR-1)

    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:BQ7<>0,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:BQ7<>0 ,0)),"",INDEX($AZ$6:$BQ$6,1,MATCH(TRUE,AZ7:BQ7<>0, 0)))

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding cell <> 0 (2000 SR-1)

    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:B Q7),AZ7:BQ7,0),MATCH(MAX(BU7:CY7),BU7:CY7,0)+COLUM NS(AZ7:BT7)))

    which is ordinarily entered.
    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding cell <> 0 (2000 SR-1)

    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... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> That's always a good thing... Thanks again! Have a great day! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding cell <> 0 (2000 SR-1)

    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... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Have a great day!

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding cell <> 0 (2000 SR-1)

    Trudi,

    Replace MAX with SUM, you'll be OK.

    Aladin
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •