Find duplications using VBA

[lhernandez]

I have just used a macro to run through a worksheet and derive a list of the descriptions and quantities I need.
Is there an easy way to write a macro to run through this new list to find any duplications and print the description once and add up the quantites from the all the duplications?

Ex:

Low Voltage Cable - 10
High Voltage Cable - 5
Low Voltage Cable - 5
High Voltage Cable - 10
Low Voltage Cable - 20


Euqla to:
Low Voltage Cable - 35
High Voltage Cable - 15

 

[pgc01]

Hi lhernandez

A Pivot Table already does what you want.

Hope this helps
PGC

 

[pfarmer]

I have just used a macro to run through a worksheet and derive a list of the descriptions and quantities I need.
Is there an easy way to write a macro to run through this new list to find any duplications and print the description once and add up the quantites from the all the duplications?

Ex:

Low Voltage Cable - 10
High Voltage Cable - 5
Low Voltage Cable - 5
High Voltage Cable - 10
Low Voltage Cable - 20


Euqla to:
Low Voltage Cable - 35
High Voltage Cable - 15

I can think of a couple of ways of doing this, such as copying all low voltage to one column, high to another and then summing the results.

Another way is to simply sort the batch and then sum the resulting ranges.

As another poster pointed out - pivot tables.

What you planning to do besides figuring out the amounts with the data may dictate the approach to use.

What you didn't mention is if the amount sits in a different column then the type.

Perry

 

[lhernandez]

Finding Duplicates using VB

thank you for the replies, I know I can do it manually with a pivot table and by the other ideas that were madem, but I am trying to make this a faster process than manual.
After this has been done (finding the duplicates and summing the quantities) I would like to copy this information over to another workdsheet and run a differnt macro that has been built to search a database and compile more data associated with the description.
I would like to do this without a pivot table because the data can be anywhere from 15 lines to 1000 lines.
If I could have a macro do this for me and the copy the data into another worksheet and call the prviously written macro, I think it would save time.
Any ideas?
Thank you

 

[Norie]

Yes use a pivot table.

There is no reason that you can't.

If there is going to be a changing amount of data use a dynamic named range.

If you really want code then record a macro when you create the pivot table manually.

If you really don't want to use a pivot table we would need more information.

As Perry asked, are the amounts in a seperate column?

 

[lhernandez]

yes the amounts are in seperate columns. how do I use a dynamic named range with a pivot table ( after i have recorded it)?

 

[pfarmer]

yes the amounts are in seperate columns. how do I use a dynamic named range with a pivot table ( after i have recorded it)?

I just wanted to state the concept I mentioned as far as sorting. In the sort you can compare the current value in the row of the column containing high/low with the previous value and if the same you add the amount, if not you save the last amount copy along with the high/low that value to another column or sheet and then start the count over.

There are a lot of different ways to do this, one of the most important pieces of information given was the values being located in separate columns (not necessary but makes it easier).

Perry

 

[jindon]

I have just used a macro to run through a worksheet and derive a list of the descriptions and quantities I need.
Is there an easy way to write a macro to run through this new list to find any duplications and print the description once and add up the quantites from the all the duplications?

Ex:

Low Voltage Cable - 10
High Voltage Cable - 5
Low Voltage Cable - 5
High Voltage Cable - 10
Low Voltage Cable - 20


Euqla to:
Low Voltage Cable - 35
High Voltage Cable - 15

If you show us the code that derive the result you have, we could produce the result that you want directly from the souce sheet.