+ Reply to Thread
Results 1 to 15 of 15

Combine Line Items From Columns/List

  1. #1
    Registered User
    Join Date
    07-30-2014
    Location
    New York, NY
    MS-Off Ver
    2010
    Posts
    7

    Combine Line Items From Columns/List

    I'm trying to consolidate three (sometimes more) years worth of unique/duplicated P&L line items...If I pasted them all in one columnn on top of one another and filter by unique values, it places them at bottom, and I have to manually place unique ones back in their spot. Below is basically what I'm dealing with:

    Year 1
    X expense
    Y Expense

    Year 2
    A Expense
    B Expense

    Year 3
    Q Expense
    A Expense
    Y Expense

    I want to turn it into a consolidated list over the three years so it would look like this:

    A Expense
    B Expense
    Q Expense
    X Expense
    Y Expense

    I've tried a couple formulas to no avail, is VBA the only way?!

    Thanks a bunch guys. Looking forward to your thoughts!

  2. #2
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Combine Line Items From Columns/List

    Post a sample workbook, showing how your data is actually laid out. A pivot table might work.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    07-30-2014
    Location
    New York, NY
    MS-Off Ver
    2010
    Posts
    7

    Re: Combine Line Items From Columns/List

    Attached. Took out other stuff; just raw data.

    Just wish I could merge the columns together and place the unique values in their spot on aggregated.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Combine Line Items From Columns/List

    Do you just want one list of unique values? Data > Remove duplicates can achieve this.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-30-2014
    Location
    New York, NY
    MS-Off Ver
    2010
    Posts
    7

    Re: Combine Line Items From Columns/List

    It's great...but again, it places these at the bottom because they change position and it's obviously not where they belong:

    Discounts
    Commercial
    Finance Charge
    Interest Expense - Other
    Officers' Compensation
    Legal Fees
    Security System
    Property
    Cleaning
    Construction Expense
    Reconciliation Discrepancies
    Safety Coordinator
    Trash/Toilets
    Gain or Loss on Sale of Assets

  6. #6
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Combine Line Items From Columns/List

    What do you mean by "obviously not where they belong"?

  7. #7
    Registered User
    Join Date
    07-30-2014
    Location
    New York, NY
    MS-Off Ver
    2010
    Posts
    7

    Re: Combine Line Items From Columns/List

    Net Income is last line in all three- where it belongs.

    Those 14 unique ones that filtered to bottom do not belong there.

    i.e. - discounts belongs way up in its expenses, where it's kind of 'grouped'. Look at the 401K line..that made it to its appropriate spot. I feel like it gives priority to the top-most list?

    I have no idea.

  8. #8
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Combine Line Items From Columns/List

    Okay. A2:D4 are the same. Easy peazy. A5, B5, and C5 are all different, but on the same row. How are we to know the order they should be listed in?

  9. #9
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Combine Line Items From Columns/List

    See attached.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-30-2014
    Location
    New York, NY
    MS-Off Ver
    2010
    Posts
    7

    Re: Combine Line Items From Columns/List

    We can't believe it. Happiest office in New York right now...how did you do that!?

  11. #11
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Combine Line Items From Columns/List

    Some manual manipulation with the help of an add-in.

  12. #12
    Registered User
    Join Date
    07-30-2014
    Location
    New York, NY
    MS-Off Ver
    2010
    Posts
    7

    Re: Combine Line Items From Columns/List

    Was lining the items (inserting rows so they even out) all manually/eye-balling!? Very interested in add in too

  13. #13
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Combine Line Items From Columns/List

    It took maybe 5 minutes. The add-in is Kutools, which provides a lot of VBA functionality by simply clicking buttons. It's pretty helpful. I wouldn't be surprised if it could actually have done the whole thing automatically, I'm just not super familiar with it.

    But in general, if there's a task you do on a regular basis (e.g., daily, weekly, etc.), then it might be worthwhile to spend hours or days trying to find the most efficient way to do it. But if you've got a one-time problem, sometimes the tedious/manual solution is the way to go. I got too annoyed trying to figure out the most efficient way to do it, so I just did it manually. But check out ku-tools.

  14. #14
    Registered User
    Join Date
    07-30-2014
    Location
    New York, NY
    MS-Off Ver
    2010
    Posts
    7

    Re: Combine Line Items From Columns/List

    Is it a lengthy explanation? I like how you added the rows in between, and then did you merge?

    I downloaded Kutools...compare ranges? I maybe just need to sit & play with it...lost

  15. #15
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Combine Line Items From Columns/List

    I manually inserted cells so everything lined up and any unique items were in rows by themselves. Then used kutools to fill in blank cells pulling to the left. There may be an easier way to do it with kutools. I'm not too familiar with all their functions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Print a 15000 line list of 2 related columns in 4 columns per page.
    By jriver03 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-25-2012, 05:23 PM
  2. [SOLVED] [solved]combine 4 columns into one non-repeating list
    By kamelkid2 in forum Excel General
    Replies: 11
    Last Post: 04-09-2012, 01:29 PM
  3. Combine two lists into a list of unique items
    By mkvassh in forum Excel General
    Replies: 4
    Last Post: 03-26-2010, 03:27 AM
  4. Replies: 3
    Last Post: 10-08-2008, 10:32 AM
  5. How do I combine quantities of similar line items
    By Joshua Hullender in forum Excel General
    Replies: 2
    Last Post: 01-03-2006, 07:45 PM

Tags for this Thread

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