Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help
I have a file that has approximately 50,000 lines of data. The data contains
information for quantity of goods that have been sent to different customers. These columns have the following data: Column A - Customer Name Column B - Invoice # Column E - Quantity Ordered Here's my predicament/question: I want to find the quantity shipped per customer, per invoice. This should be done by simply looking at each row of data on my current spreadsheet. However, the data has some flaws. Some of the invoice #, customer name, and the corresponding quantity amounts are duplicated. For example, Invoice # 123456 for Customer XYZ Company with quantity 6 might show up three times on the spreadsheet. Now, it's OK if the Invoice # and the Customer name show up more than once on the spreadsheet, BUT, if I want to count this quantity, I can ONLY do that if the quantity number is different from the other quantities that are mentioned for the same invoice # and customer. For example, let's say, Invoice # 654321 for Customer ABC Company shows up 4 times on the spreadsheet. The first time the quantity is 6, the second is 5, the third has 6, and the fourth 12. This means, this invoice has a total quantity of 23. We can't count the third order (with the quantity of 6) because that is a duplicate. I know this is a difficult question, but if you could please help me any way you can, I would certainly appreciate it! Thank you! |