Tech Support Guy banner
Cancel

Excel Database Help

1808 Views 20 Replies 5 Participants Last post by  Zack Barresse, 
R
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
Save
Like
Status
Not open for further replies.
1 - 6 of 21 Posts
T
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
Save
Like
T
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 :)
Save
Like
T
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.
See less See more
Save
Like
T
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.
Save
Like
T
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)
Save
Like
T
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
Save
Like
1 - 6 of 21 Posts
Status
Not open for further replies.
Tech Support Guy
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!
Full Forum Listing
Explore Our Forums
Hardware Virus & Other Malware Removal Off Topic Lounge Thread Games & Discussion Networking
Top