Find duplications using VBA

lhernandez

Active Member
Joined
May 22, 2006
Messages
282
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi lhernandez

A Pivot Table already does what you want.

Hope this helps
PGC
 

pfarmer

Well-known Member
Joined
Jul 6, 2005
Messages
550
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

Active Member
Joined
May 22, 2006
Messages
282
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
May 22, 2006
Messages
282
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

Well-known Member
Joined
Jul 6, 2005
Messages
550
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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.
 

Forum statistics

Threads
1,136,655
Messages
5,677,019
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top