Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    29

    Lightbulb Unanswered: Find the lowest number and place the number and Column name into in Field

    I have in Excel the following columns nams and data.

    DestZipCode,Origin44114,Origin90210,Origin44105,Op timalOrigin
    00501,2,5,2
    00544,3,5,1
    01003,2,2,5


    What I need to to is look into each row and find out the lowest number and place the column name into
    another field. If the lowest number has 1 or 2 values then have both column names in the OptimalOrigin
    field. I have 42,000 different zipcode.

    Example
    DestZipCode,Origin44114,Origin90210,Origin44105,Op timalOrigin
    00501,2,5,2,Origin44114 Origin44105
    00544,3,5,1,Origin44105

    What would be the formula needed to get these resaults?

    Any help would be great!
    Thank You!

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. Not sure if I fully understand, but here is a possible solution.

    If the following are accurate, assuming only Columns B, C, and D have conditions to test, with these titles in Row 1:

    column A: DestZipCode
    Column B: Origin44114
    Column C: Origin90210
    Column D: Origin44105

    Then put this into cell E2

    =IF($B2=MIN($B2:$D2),B$1,IF($C2=MIN($B2:$D2),C$1,D $1))

    and this into cell F2

    =IF(AND($B2=MIN($B2:$D2),$C2=MIN($B2:$D2)),C$1,IF( AND($B2=MIN($B2:$D2),$D2=MIN($B2:$D2)),D$1,""))

    and this into cell G2

    =IF(AND($B2=$C2,$B2=$D2),D$1,"")
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    don't do this, it's going to shoot you in the foot for the reasons I mentioned in your post in the access forum. This is for import into a RELATIONAL database, there's a reason they call it a relational database and not "group of spreadsheets" database.

    Seriously, the extra work will save you acute brain trauma in the near future.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Guess I missed out on what this was for. hmmmm it helps to get the full story.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Mar 2004
    Posts
    29
    Thank you very much Shades this works perfect I think you should be called the MONKEY not the other guy (That thinks he is the Access Monkey) its people like him that "THINKs" he know everything I am just a newbie with these kind of formulas and was in need of help not just attitude. I hope in the future I will get help from people like you!

    AGAIN I CAN NOT THANK YOU ENOUGH YOU SAVE ME A TON OF TIME!!

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    Great. Glad to help, and thanks for coming back to report on whether the offered solution was appropriate. It helps you, me, and others who read.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by cle5cap
    Thank you very much Shades this works perfect I think you should be called the MONKEY not the other guy (That thinks he is the Access Monkey) its people like him that "THINKs" he know everything I am just a newbie with these kind of formulas and was in need of help not just attitude. I hope in the future I will get help from people like you!

    AGAIN I CAN NOT THANK YOU ENOUGH YOU SAVE ME A TON OF TIME!!
    lol!

    I'm trying to rip on you for being new, I'm trying to help you not make severe mistakes that will require you to build work around after work around instead of simple solutions.

    Take it as you will...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    Reminds of a discussion from 2 years ago: Excel vs Access?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by shades
    Reminds of a discussion from 2 years ago: Excel vs Access?
    Slightly different. Had he not posted in the Access forum thereby indicating he's trying to use a relational platform, I would say excel works just fine. I prefer it for anything that doesn't involve relationships between multiple entities, from what I understand, this is not one of those situations.

    edit: Ho ho, what have we here? It would appear to be a textbook example of why I was pushing for a normalized solution...
    Last edited by Teddy; 12-15-05 at 12:29.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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