1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel help combining fields

Discussion in 'Business Applications' started by ksquared, Sep 28, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. ksquared

    ksquared Thread Starter

    Joined:
    Apr 19, 2004
    Messages:
    68
    Hello, not sure how to explain this but hopefully this will work.

    I need to combine the contents of all the cell B’s when cell A has the same contents (in this example 2222 etc.).
    ........A......B
    line 1 2222 object
    line 2 2222 box
    line 3 2222 something
    line 4 2223 circle
    line 5 2223 square
    line 6 2224 stuff
    line 7 2225 glass

    When I’m done, my spreadsheet will look like this.
    ........A......B
    line 1 2222 Object, box, Something
    line 2 2222
    line 3 2222
    line 4 2223 Circle, square
    line 5 2223
    line 6 2224 stuff
    line 7 2225 glass

    or this. It doesn't matter if the the original contents of the B cells stay as they originally were.
    ........A......B
    line 1 2222 Object, box, Something
    line 2 2222 box
    line 3 2222 Somthting
    line 4 2223 Circle, square
    line 5 2223 square
    line 6 2224 stuff
    line 7 2225 glass

    I've been manually cut and pasting but there must be a better way. Microsoft Office Excel 2007

    Thanks in advance!
     
  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi

    Here's a 2 step procedure to get where you want to go. This assumes that the values in column A are sorted;

    C1 will be =B1
    C2 to the end of the list will be

    =IF(A2="","",IF(A2=A1,C1&", "&B2,B2))

    D1 to the end of the list will be

    =IF(A1=A2,"",C1)

    See attached ...

    I'm pretty sure there's a cleverer way to do this with excel functions. I know it can easily be done with a macro. But see if this suits.
     

    Attached Files:

  3. ksquared

    ksquared Thread Starter

    Joined:
    Apr 19, 2004
    Messages:
    68
    Thank-you XCubed! Your formulas combined everything but I need one more change. I need to have the combined products stored in the 1st instance of the 1st appearance. Can a 3rd step be added to move the combined prodcuts to the 1st line where we started?

    (I may have finally figured out how to upload a file. I made charges so you can see what the final result needs to look like)
     

    Attached Files:

  4. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    a manual way to do this would be to

    copy/pastespcial/values columns C & D
    Custom Sort Cols A-D on Col A and Col D Z to A

    If this is all too much my next suggestion would be a macro
     
  5. ksquared

    ksquared Thread Starter

    Joined:
    Apr 19, 2004
    Messages:
    68
    Thank-you again XCubed. I hadn't used a custom sort before but was able to figure it out using your instructions. I sorted Col A from smallest to large and then Col D from z to a which did exactly what I needed to do. I captured all of my keystrokes into a macro (typing out your formulas and following the provided instructions) and it's perfect! I really appreciated that you took the time to read my question and put the formulas together for me. A simple thing for you but a weeks worth of frustration for me assuming I could have even figured it out on my own (not).Plus I learned something new along the way which was an added bonus. Much appreciated as always. :)
     
  6. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Glad I could help and impressed that you developed you own macro. Just one hint ... it sounds like you did 2 separate sorts when you can, in fact, do multiple sorts at the same time. When you get the Sort window click on "Add a Level" and you can enter your secondary sort in there.
     
  7. ksquared

    ksquared Thread Starter

    Joined:
    Apr 19, 2004
    Messages:
    68
    Thanks XCubed. Please don't be too impressed with me writing macros. Excell has a developer tool which captures what you are doing and writes the code (VB?) for you. All I have to do know how to do it manually 1st with no mistakes along the way and then turn on the capture macro tool. I usually capture the individual steps siince I'm not the best typest and than combne the code into one macro using the edit macro function. If I write out the steps and put them in the comment section it helps me understand the actual code so I can make changes later without to capture my keystrokes all over again. Sometimes, not always though.

    Thanks agian for your help. I did do the multiple sorts as suggested which worked great. I have a few more things I'll need help with but I will post a new thread (after I've tried everything I know).
     
  8. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    I'm even more impressed now. You'd be amazed at how many people are unaware of or are reluctant to use the "Record Macro" utility, never mind combining macros and annotating them.

    Looking forward to your next question(s). If at all possible, attach the Excel file in your post - it will result in a better, more specific, response.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1109486

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice