# Thread: Find the lowest number and place the number and Column name into in Field

1. Registered User
Join Date
Mar 2004
Posts
29

## 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. Registered User
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,"")

3. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
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.

4. Registered User
Join Date
Oct 2003
Posts
1,091
Guess I missed out on what this was for. hmmmm it helps to get the full story.

5. Registered User
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. Registered User
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.

7. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
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...

8. Registered User
Join Date
Oct 2003
Posts
1,091
Reminds of a discussion from 2 years ago: Excel vs Access?

9. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102