new challenge for all the gurus?

  • Thread starter Thread starter ksnapp
  • Start date Start date
K

ksnapp

Ok let me show you something similar to what I have:

Client 1 Pork 1
Chicken 2
Beef 5
Pork 3
Client 2 Beef 4
Beef 6
Chicken 1
Client 3 Chicken 2

For each client there are severl entries of a product and then th
quantity purchased of that product. You will notice that some client
have multiple entries for the same product. That is the issue I nee
your assistance with. If there are duplicate entries for a product
need the quantities sumed, then shown in the uppermost of the existin
entries and the left over one deleted (whole row). Any Ideas ar
helpfull, but there are alot of these and a sub is preffered.

Thank You for Your Assistance
 
ksnapp said:
Client 1 Pork 1
Chicken 2
Beef 5
Pork 3
Client 2 Beef 4
Beef 6
Chicken 1
Client 3 Chicken 2

For each client there are severl entries of a product and then the
quantity purchased of that product. You will notice that some clients
have multiple entries for the same product. That is the issue I need
your assistance with. If there are duplicate entries for a product I
need the quantities sumed, then shown in the uppermost of the existing
entries and the left over one deleted (whole row). Any Ideas are
helpfull, but there are alot of these and a sub is preffered.
Click to expand...

If it's a one-time job, use Excel's basic features to manipulate the
file to look like this (the first field is a counter to prevserve the
original ordering):

1 Client1 Pork 1
2 Client1 Chicken 2
3 Client1 Beef 5
4 Client1 Pork 3
5 Client2 Beef 4
6 Client2 Beef 6
7 Client2 Chicken 1
8 Client3 Chicken 2

After you sort on Client and Product, the needed sub is easy, because
product duplications are adjacent to each other. Once the macro
finishes, sort the file on the first field to return to the original
ordering.
 
With the linewrap, I can't make out what your arrangement of data is,
but if possible, I would strongly urge you to consider a Pivot Table
instead of code - this type of application is exactly what PTs are
designed for.
 
too much data for pivot tables and sorting but both are great ideas.

I think if i can find a way for me assign the locations (cel
refreneces) of the cells that countain client names to variables the
plug those cell refrences to a sumif or something of that sort it wil
work for me. The row deleteing part I have in other code so thats jus
fine
 
Back
Top