Excel: Data extraction?
Hi there,
I have a spreadsheet of 10 suppliers and 100+ items that they have quoted for. I need to know which company has presented the cheapest quote for each line multiplied by usage (I've been using =min, but I want the value to appear as the company name rather than the figure) and then I need to extract this >Update:
Hi Greg,
It's a very simple layout. The first column (a) is item/item number, the second (b) the quantity required, and then C to K are company's quotes.
eg.
#090809098 4 £6 £4 £6 £5
etc.
Thank you so much!
More Questions From This User See All
Answers & Comments
Verified answer
Can you give an example as to how the data is arranged? With that, I can give you a formula for getting the results you're looking for.
Hi, I'm not sure if your problems could be solved by Excel, but hereby I recommend you esCalc, it is an intelligent spreadsheet with the same cell style interface, it can solve such problems easily.
Import the data from Excel with esCalc,
and then group the data according to company( just use menu-group),
and use min formula, the company together with figure, items could be listed, just filter them if you don't need some of them.
After that, you can also set the format you need within one cell, the other cells' formats with the same layer will automatically changed, for example, if your want to change the color of company name, just set the color in Company A, the company B, or C... and others, will change into the color automatically, you don't need to copy the formula like in Excel one by one.
Download esCalc at http://www.raqsoft.com/download.html, it has free version
Finally, export the table you need into Excel files again, it will be OK.
Hope it can help you.