+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Registered User
    Join Date
    01-10-2012
    Location
    Farmington, CT
    MS-Off Ver
    Excel 2010
    Posts
    24

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

    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
    Attached Files Attached Files

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    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.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    01-10-2012
    Location
    Farmington, CT
    MS-Off Ver
    Excel 2010
    Posts
    24

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

    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
    Last edited by BBLANCOBRNX; 01-13-2012 at 05:10 PM.

  4. #4
    Registered User
    Join Date
    01-10-2012
    Location
    Farmington, CT
    MS-Off Ver
    Excel 2010
    Posts
    24

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

    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!

  5. #5
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

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

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    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?
    Last edited by jaslake; 01-13-2012 at 05:17 PM.

  7. #7
    Registered User
    Join Date
    01-10-2012
    Location
    Farmington, CT
    MS-Off Ver
    Excel 2010
    Posts
    24

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

    Quote Originally Posted by jaslake View Post
    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.

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    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.

  9. #9
    Registered User
    Join Date
    01-10-2012
    Location
    Farmington, CT
    MS-Off Ver
    Excel 2010
    Posts
    24

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

    Quote Originally Posted by jaslake View Post
    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
    Attached Files Attached Files

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

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

  11. #11
    Registered User
    Join Date
    01-10-2012
    Location
    Farmington, CT
    MS-Off Ver
    Excel 2010
    Posts
    24

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

    Quote Originally Posted by jaslake View Post
    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.

  12. #12
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    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
    Last edited by jaslake; 01-20-2012 at 02:14 PM. Reason: Add PS

  13. #13
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1