help combining rows with the same identifying 1st column and different second column

[BBLANCOBRNX]

I have a worksheet that is similar to the one I have attached here except it has many thousands of lines. I have some duplicate values, like 3 or 4 of the same number in a row in column A, with different "quantities" in column B as you can see in the sample. Is there a way to add together like rows (while only adding up the "quantities" from column B) thus eliminating the 3 or 4 duplicate entries and consolidating them to one row? Thank you so much for the help. I am learning so much thanks to this forum!!!

BBB

[jaslake]

Hi BBB
You can try this code...works fine on your sample sheet...may take a bit on thousands of rows

Please Login or Register  to view this content.

[BBLANCOBRNX]

I get "Run time error 13: Type Mismatch". What did I do wrong? I enter this as a macro, right?


EDIT: I can confirm it does work just fine on the sample worksheet...just not on the regular one which the only difference I am sure of is that it is much much longer

[BBLANCOBRNX]

Oh, I think I may know why. There are some spots in the "qty" box on my main worksheet that have more than just a number value in them. some have "1 bx" for "one box" or 1 kt for "one kit". I bet that is messing with it!

[jaslake]

Hi BBB
I'm attaching your sample file with the code embeded and a button to run it.

[jaslake]

Yep...that'll do it

I bet that is messing with it!

Assuming this relates to your other Thread (which I'm looking at now) what does the Inventory file look like for these items?

[BBLANCOBRNX]

Yep...that'll do it

Assuming this relates to your other Thread (which I'm looking at now) what does the Inventory file look like for these items?

I am dealing with 2 different inventory files. The first one is the one that is attached to the other thread. That file came from another party which has a great deal of my inventory on consignment. Problem is, I have recently found they are less than trustworth. So, I am going back through every purchase I have made over the years and making a new total inventory of what I should have which I will then compare with the sheet of what they say that I have. I am only partway through building my complete inventory. The file in the other thread is ripped right from one of their sheets they sent me with about 50,000 line items. So far this one I am building from scratch has about 28,000. The "sales" spreadsheet attached to the other thread is one I made myself over the years from their monthly sales breakdown of what they say they sold each month over the past 10 years. I is around 7,500 lines and growing as each month goes by.

[jaslake]

Hi BBB
So, show me a file that reflects this

There are some spots in the "qty" box on my main worksheet that have more than just a number value in them. some have "1 bx" for "one box" or 1 kt for "one kit".

I'll see if I can work with it.

[BBLANCOBRNX]

Hi BBB
So, show me a file that reflects thisI'll see if I can work with it.

Thank you John. Here you go.

BBB

[jaslake]

Hi BBB
This new file, I'll presume, is the Inventory file. What will the Sales file look like?

[BBLANCOBRNX]

Hi BBB
This new file, I'll presume, is the Inventory file. What will the Sales file look like?

Hello John,

First, let me thank you for all of your help. The sales file really does not need to be redone, as I have personally entered all of those items myself. It was the inventory I never had time to make a full list of over the years as I was busy bidding and buying stuff. I had all of the monthly sales breakdown sheets and entered them into excel. Obviously the inventory list is much, much, much larger than the sales list. I was hoping to run my "new" inventory list (which is the one you see a sample of here) against the sales list (which is the format I already have posted in the other thread). The problem is, I am still quite a ways off of having the "new" inventory list completed. I am taking a look at the files you attached to your reply in the other thread and see how it works with the old inventory list that I have that was provided by the people who have those parts of mine in their warehouse.

[jaslake]

Hi BBB
The link between the old Inventory file and the Sales file was Part Number, Condition Code and Split. Your new Inventory file has no Part Number nor does it have Condition Code. It does, however, have NSN Number and Split. These fields also exist in the Sales file. Am I to presume that NSN Number and Split becomes the link between the two files?

Now, as we've discussed, your new Inventory file has a Qty field that contain things like PG, AY, KT and HD. Does the Sales file Qty field include these "Packaging" codes? Do you care if you lose these Qty codes or do you want them reinstated?

Confirm what the Link is between the two files. Confirm the contents of the Sales file Qty field. I'll see what I can do.

PS: 2 records in the Sample Sales file have blank NSN codes

[jaslake]

Hi BBB

I've made certain assumptions regarding your Inventory file and your Sales file.

For example:

• I've assumed the key between the files is NSN Code plus Split Code.
• I've further assumed all items in both files will have these two codes.
• I've assumed you have no need to retain the Qty packing codes (PG, AY, KT, HD, etc). If you want/need these they can be reinstated.

The two attached files are dummied up from a combination of files you've previously attached. In these files, if the NSN Code was missing, I've either added a false code or I've deleted the items. As before, place both these files in the same folder, click the button.

Play with it...let me know of issues.