Excel Database Help

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

rudy2001

Thread Starter
Joined
Jan 11, 2007
Messages
6
I am working on a database where A1-A1500 contains part numbers. Columns B-BX contain "Vendor Price" and "Date Price was Quoted" for multiple vendors. I inserted 2 columns at Column B and added "Best Price" into Column B and did MIN function to find the lowest price. Ok, that was the easy part.

Now I would like to/have to work out some logic so the database knows which Vendor did have the lowest price because I need to have the "Date Price was Quoted" that is associated with the "Best Price" fill into Column C. I think this is a combination of a VLookup and an if then statement, but I am not sure how to approach it and I am stuck! Help!

I appreciate any insight/advice..
Thanks for your time,

Rudy
 
Joined
Feb 20, 2006
Messages
2,255
Rudy
It would be a good idea if you uploaded your spreadsheet without any sensitive data so that we can all have a look at it and give you the best advice.
Les
 

rudy2001

Thread Starter
Joined
Jan 11, 2007
Messages
6
I wasn't aware that I could do that....
I will do that later this evening.

Thank you Les!
 
Joined
Feb 20, 2006
Messages
2,255
I hope you are not my brothet in law from Holland.
I have a brother in law who is Dutch and he is called Rudy nah not possible :)
 

rudy2001

Thread Starter
Joined
Jan 11, 2007
Messages
6
Ha Ha. No, I am not your Dutch brother. Rudy is the name of my dog.

I have attached (a very simplified) file of the monstrosity that I am working on. Basically I am trying to figure out how to get the corresponding info in Columns C, D.
 

Attachments

Joined
Feb 20, 2006
Messages
2,255
Rudy
I have had a look.

I am not using Excel these days in the way I used to, so am having trouble getting the brain to function as such.

However, I am sure that their are a number of people on this forum, that would know the answer.

The way I see it, it needs a formula, that interogates the result in B2 and returns the cell reference where the result came from. Then the formula needs to move one cell to the right and return the result of that cell.

So in your example
Cell B2 =MIN(E2:M2) which in your example returns the value in E2.

Cell C2 needs to pick up the cell reference from where the result came from in B2 which in this case would be E2 and then return the information in F2 which would be the Cost source for that price.

Cell D2 needs to pick up the cell reference from where the result came from in B2 which in this case would be E2 and then return the information in G2 which would be the Quote Date for that price.

So if you wait patiently, I am sure that the guys with the finger on the pulse will help you out.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,501
what you really need to do
is search the range for that row
so in you example seach e2.m2 for a match with B2
then when found the column reference its in - do an offset of +1 to bring back the next column source or offset of +2 to bring back date

had a quick play and did not work

vlookupp wont work because it uses the left most column to mach against so your min valus would always need to be in E column

Hlookup also did not work

I tried a find etc but not able to make this work - maybe it needs a macro
 
Joined
Feb 20, 2006
Messages
2,255
I did exactly the same etaf. I agree with your comments. However I am convinced that it is possible with functions rather than macros. I also looked at Pivot tables. The layout of the spreadsheet doesn't lend itself to Vlookups and Hlookups. In my heyday, I used to create my own functions, but wouldn't know how to do that any more.

There would also need to be error trapping such as when there are 2 prices the same etc.
 
Joined
Feb 20, 2006
Messages
2,255
It also has to take account of the fact that Rudy will probably want to add extra columns and the formulas will need to expand automatically to allow for the new columns (thats not difficult to handle)
 
Joined
Feb 20, 2006
Messages
2,255
Ah well got to go out for the day, but will look in when I get back in anticipation of some little brainbox coming up with the answer :) Now don't be shy LOL
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,501
=MATCH(MIN(E3:M3),E3:M3,0)

this will tell you which column it is in based on your array - so if it returns 7 then it is in the 7th column from E3
so that would be K
E=1
F=2
G=3
H=4
I=5
J=6
K=7

now we just need to use the 7 in the array to return K3
or using choose to get the starting column of the array then add 7

tried using RC reference still not able to get it..
 

OBP

Joined
Mar 8, 2005
Messages
19,896
This sounds like you are trying to do Access Query type functions in Excel, have you considered using Access instead?
 

rudy2001

Thread Starter
Joined
Jan 11, 2007
Messages
6
they are set on using excel. why? i don't know. i agree that it would be more manageable in access.

the workbook does contain many vlookup references, but not on the specific sheet that i showed.

it seems so logical yet any formula i think of does not work!

i have macros in the other sheets in the workbook so i would not be opposed to a macro in the parts file that i displayed

i really appreciate everyone's suggestions!
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Taking Villan's point in post #9 that you may have more than 3 "sources", I can do this with VB if you want as Excel functions aren't my forte.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Try this, click the command button on the sheet.
The code allows for 5 columns of Sources.
It doesn't need the message box, it is just their to tell you where it finds the match.
 

Attachments

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top