+ Reply to Thread
Results 1 to 8 of 8

How To Merge Rows but create seperate Rows for Data

  1. #1
    Registered User
    Join Date
    08-05-2018
    Location
    Austin, TX
    MS-Off Ver
    MS Office 365
    Posts
    4

    How To Merge Rows but create seperate Rows for Data

    Hello all!

    I have been stumbling around with this one for a while and haven't been able to find a proper solution. Here's what I'm trying to do:

    I'd like to combine rows of the same data, but create different columns for specific data corresponding with each row. For Example:

    My current sheet shows:

    Name Amount Year
    Joe Smith 200 2015
    Joe Smith 250 2016
    Joe Smith 300 2017
    Joe Smith 350 2018

    Is it possible to merge the rows so that it reads:

    Name 2015 2016 2017 2018
    Joe Smith 200 250 300 350

    Any help would be greatly appreciated! Thanks so much!

    Chris

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: How To Merge Rows but create seperate Rows for Data

    Hello Chris and Welcome to Excel Forum.
    What about a pivot table such as the one modeled on sheet to of the attached file?
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    08-05-2018
    Location
    Austin, TX
    MS-Off Ver
    MS Office 365
    Posts
    4

    Re: How To Merge Rows but create seperate Rows for Data

    That gets me really close, thanks JeteMc! I've used pivot tables before, but not extensively.

    What if there's a lot more data that needs to stay with my row? So in the example, if Joe Smith has an address, phone number, email, etc? I know I can just add those fields to the "Columns" part of the Pivot Table, but then I can't manipulate the data. But I need the column header to read "Address" or "Phone Number." If I put those fields in the pivot table, it treats them as a Value. For Example:

    Desired:

    Name / 2015 / 2016 / 2017 / 2018 / Address / City / State
    Joe Smith / 200 / 250 / 300 / 350 / 1 Main St / Austin / TX



    Thanks Again!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: How To Merge Rows but create seperate Rows for Data

    Please upload a sanitized (sans sensitive or proprietary information) sample of what your input data looks like as well as a manual mock up of what you would like the output to display, so that we can attempt to automate the production that output.
    To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    Let us know if you have any questions.

  5. #5
    Registered User
    Join Date
    08-05-2018
    Location
    Austin, TX
    MS-Off Ver
    MS Office 365
    Posts
    4

    Re: How To Merge Rows but create seperate Rows for Data

    I've attached a "Mock Up" with 2 tabs. One for the current format of data I'm trying to manipulate, one for the desired format.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: How To Merge Rows but create seperate Rows for Data

    Perhaps this will help.
    As modeled on the PT sheet:
    1) Name, Address and Category are Row Labels
    2) Year is the Column Label
    3) Amount (Sum) is the Value
    Under the design sub tab of the PivotTable Tools tab:
    1) Report Layout is set to Tabular
    2) Subtotals and Grand total are turned off
    Note that Category (row label) will precede the values and column labels.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-05-2018
    Location
    Austin, TX
    MS-Off Ver
    MS Office 365
    Posts
    4

    Re: How To Merge Rows but create seperate Rows for Data

    That did it! The Design Sub Tab of the PivotTable Tools is new to me. Looks like I've got some new things to explore tonight!

    Thanks again for your help.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: How To Merge Rows but create seperate Rows for Data

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.

+ 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. Replies: 7
    Last Post: 08-05-2014, 05:34 AM
  2. Splitting rows with multiple columns into seperate rows for time points.
    By mbracha in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-16-2013, 10:06 AM
  3. Merge Duplicate Rows unique values into single rows for an infinite amount of columns/rows
    By aimeecrystalaid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-21-2013, 08:43 PM
  4. Replies: 1
    Last Post: 06-04-2013, 12:02 AM
  5. [SOLVED] Need help badly with transposing 3 rows of patterned data into seperate rows
    By unnyb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-03-2013, 02:42 PM
  6. [SOLVED] Create macro to compare two rows and if two columns agree merge all
    By Poseidons-Palace in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-28-2012, 03:46 PM
  7. Replies: 4
    Last Post: 05-09-2009, 06:13 AM

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