Find Lowest Price and Vendor

[joshdward]

I have a list of products with column headings as such:

Product #, Description, Price, Vendor

There are several different vendors and so there are also several different prices for the same product numbers. The product numbers are list multiple times on the sheet.

I'd like to be able to enter a product number on a separate sheet and have it return the 3 lowest priced vendors for that part in addition to the prices.

Thanks in advance for the help.

[alansidman]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[joshdward]

Thanks for the heads up. Here is an example workbook.

[xlbiznes]

Hi,

Attached is my solution and i have modified it to suit you requirements.

Firstly , click on the Select file to select your actual data source and then enter the product for which you want to get the least 3 prices in the cell marked (G2) and then click on process to get the output.

Hope this helps.

[ramananhrm]

Please try this file.

[joshdward]

Fantastic, works great.

[joshdward]

As a followup, I'm pulling the pricing information from 20 different sheets right now. All the sheets are formatted a bit differently. The column names in the header rows are all slightly different and in different orders. Right now I'm just copying and pasting them all onto 1 sheet but it's very time consuming. What's the best approach to automating this? Perhaps using a macro?

[xlbiznes]

Hi,

in that case your requirement was not properly explained. Copying data from 20 sheets with varied positions of fields is another challenge. Will there be a consistency in the flow of data or you will not know until the data comes to you ?

we can build a logic if you are able to shed some light on this.

[FDibbins]

josh I suggest that you try and standardize your sheets - or better yet, combine them all into 1 "data" sheet. That will make it easier to offer simple suggestions

[joshdward]

I was planning on just copying and pasting everything into 1 standard sheet. However after spending 3 hours on trying to manually do it yesterday (there are several records and each list from a vendor is different) I thought I might ask for a little more help. Even just standardizing each sheet to make it easier to combine took a couple of hours. The price sheets are updated multiple times a week which makes it difficult to spend hours standardizing the sheets.

There is a consistency from each vendor. Each vendor formats their price sheets the same way each time. However not all vendors format it the same way. The logic would have to be per vendor.