Please help me with this excel challenge of extracting unique part numbers and the cheapest price.?

Question

Hello everybody! Please help me with this excel challenge if you can. I am in the printer cartridges business and I have put up a big spreadsheet containing three of my suppliers’ price lists with the purpose of comparing to get the cheapest price. Obviously under the “cartridge part number column” there are groups of the same cartridge part numbers. The spreadsheet only contains two columns ---- “cartridge part number column” and “price column”. Is there a way to “extract” the one unique cartridge part number from every group under the “cartridge part number column” and also “extract” the one cheapest price from each group of unique part numbers and put them into a new sheet or a new spreadsheet file? The result will be a perfect one as it will contain every part number once and right next to it will be the cheapest price for me. Thank you very much everybody and have a good one! Sincerely, Chris

Answer 1

Ok very easy. Do a pivot table.

highlight all your data, go to toolbar > Data > pivot table & pivot chart report. Then in the layout, put the "cartridge part number " at the row and "price" at the data. Summarize the price to min, then you will get the lowest price for every part number.

Answer 2

First, you might want to add a column for supplier name (or ID) so you will be able to tell where you can get the lowest price.



The easiest way to get what you want would be to write a macro that



1. Copies the table to a new worksheet,

2. Sorts it by part number then price ascending

3. Deletes every row where the previous row has the same part number.



You can get a good start on your macro by recording the first two steps. When you select the range to copy and sort include as many empty rows at the bottom as you are likely to need for some time.



Assuming the part number is in column A, the code for the third step would look like:



iRow = [second row number]

Do While Cells(iRow, 1).Text > ""

If Cells(iRow, 1) = Cells(iRow - 1, 1) Then

Rows(iRow).delete

Else

iRow = iRow + 1

End If

Loop



Note that you increase iRow by one only if you do not delete the row.

Answer 3

If the companies cost information superhighway internet site (say on Sheet1) lists a similar factor quantity some events, and you're watching for which one in the checklist has the backside cost, then you definately'll use the subsequent array equipment on Sheet2: I used the subsequent levels for the example: 'Sheet1'!A1:A100 = factor numbers 'Sheet1'!B1:B100 = expenses 'Sheet2'!A1 = factor quantity to look up =MIN (IF ('Sheet1'!$A$a million:$A$one hundred = A1, 'Sheet1'!$B$a million:$B$one hundred) ) that is an array equipment; rather of only pressing enter, press CTRL+SHIFT+enter. Excel will wrap it in {curly brackets} if entered thoroughly.

Answer 4

If the suppliers price page (say on Sheet1) lists the same part number several times, and you are looking for which one in the list has the lowest price, then you can use the following array formula on Sheet2:



I used the following ranges for the example:

'Sheet1'!A1:A100 = part numbers

'Sheet1'!B1:B100 = prices

'Sheet2'!A1 = part number to look up



=MIN (IF ('Sheet1'!$A$1:$A$100 = A1, 'Sheet1'!$B$1:$B$100) )



This is an array formula; instead of just pressing ENTER, press CTRL+SHIFT+ENTER. Excel will wrap it in {curly brackets} if entered correctly.