Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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! |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Other than by quantity, my question to you is, how do you know it is a
duplicate? I have worked in many places where there may be a couple of lines for the same item with the same quantity for any number of a variety of reasons. So my question is there any other criteria that defines what is, and what isn't a duplicate? -- ** John C ** "Liz J" wrote: 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! |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Excel 2007:
Remove Duplicates. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Unfortunately, John, there is no data on the spreadsheet that could
distinguish what is a duplicate from what is not a duplicate. I know that there are duplicates only for the face that the sales dollars do not tie properly. I will give this some more thoughts and get back to you about a questions at to how to figure out this data-mess. Thanks for your response! "John C" wrote: Other than by quantity, my question to you is, how do you know it is a duplicate? I have worked in many places where there may be a couple of lines for the same item with the same quantity for any number of a variety of reasons. So my question is there any other criteria that defines what is, and what isn't a duplicate? -- ** John C ** "Liz J" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|