+ Reply to Thread
Results 1 to 8 of 8

Combine Data in Multiple Columns to One Column Based on Data in Rows

  1. #1
    Registered User
    Join Date
    09-08-2014
    Location
    earth
    MS-Off Ver
    2010
    Posts
    4

    Combine Data in Multiple Columns to One Column Based on Data in Rows

    Hi Excel experts!

    I have similar data spread across multiple rows that I would like to combine. Below is a simplified example:

    r15Yz2x.jpg

    I want to combine all of the names in column D, comma delimited, if they have the same information from other rows in columns A, B, and C. For example, Column A, B, and C of rows 2, 3, and 4 say Pizza, http://www.pizza.com, 1/12/2014. The difference comes in Column D of rows 2, 3, and 4. I would like to consolidate all of the names in Coilumn D that pertain to the information from the first three columns. Here is an example:


    1JP9KS6.jpg

    How can I accomplish this? Please see attached a sample workbook for the example I have created. My actual data is very large (1000+ rows)

    Example.xlsx
    Last edited by exceldivx; 09-08-2014 at 03:47 PM.

  2. #2
    Registered User
    Join Date
    09-08-2014
    Location
    earth
    MS-Off Ver
    2010
    Posts
    4

    Re: Combine Data in Multiple Columns to One Column Based on Data in Rows

    bump, anybody, please?

  3. #3
    Registered User
    Join Date
    08-28-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    92

    Re: Combine Data in Multiple Columns to One Column Based on Data in Rows

    i think you need to sort the rows and apply the formula that will work. please find the attached sheet.then you can sort
    them with pizza,burgers....etc and get the highest count and total customers.


    seems like this formula will help you....

    add * as reputation if my answer helps you.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-08-2014
    Location
    earth
    MS-Off Ver
    2010
    Posts
    4

    Re: Combine Data in Multiple Columns to One Column Based on Data in Rows

    Quote Originally Posted by boddulus View Post
    i think you need to sort the rows and apply the formula that will work. please find the attached sheet.then you can sort
    them with pizza,burgers....etc and get the highest count and total customers.


    seems like this formula will help you....

    add * as reputation if my answer helps you.
    thank you, but I am trying to have the data organized as follows. If I respectfully may, the spreadsheet you provided has the info kind of confusing like it was with my starting data.

    Attachment 343958

  5. #5
    Registered User
    Join Date
    01-20-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Combine Data in Multiple Columns to One Column Based on Data in Rows

    Hi exceldivx

    Please find attached a workbook with the relevant formula. You will need to enter a user defined function in VBA.

    Example.xlsm

    edit:

    To find 'Number of Contacts' is a simple COUNTIF:
    Please Login or Register  to view this content.
    edit2:

    In case you don't know how to enter user defined functions, press Alt+F11, click on your workbook in the project explorer on the left, open a new standard module and paste this :

    Please Login or Register  to view this content.
    Last edited by cs454; 09-09-2014 at 06:30 AM.
    +++ If I helped please add reputation +++

  6. #6
    Registered User
    Join Date
    09-08-2014
    Location
    earth
    MS-Off Ver
    2010
    Posts
    4

    Re: Combine Data in Multiple Columns to One Column Based on Data in Rows

    Quote Originally Posted by cs454 View Post
    Hi exceldivx

    Please find attached a workbook with the relevant formula. You will need to enter a user defined function in VBA.

    Attachment 344128

    edit:

    To find 'Number of Contacts' is a simple COUNTIF:
    Please Login or Register  to view this content.
    edit2:

    In case you don't know how to enter user defined functions, press Alt+F11, click on your workbook in the project explorer on the left, open a new standard module and paste this :

    Please Login or Register  to view this content.
    ohh that's very close to what i need, thank you! how can i get rid of the duplicate data in column a, b, and c while retaining one copy of it, with the contacts name in that cell, as shown in the attached?

    how can i also make sure the contact names that were combined are comma delimited?

    Attachment 343958

  7. #7
    Registered User
    Join Date
    01-20-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Combine Data in Multiple Columns to One Column Based on Data in Rows

    I have edited the function to separate names with commas.

    Please Login or Register  to view this content.
    To remove duplicate data, first copy and paste your table as values. Then select your table Data -> Remove Duplicates -> select all columns except D.

  8. #8
    Registered User
    Join Date
    01-20-2014
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Combine Data in Multiple Columns to One Column Based on Data in Rows

    exceldivx - did you want the contact list to correspond not just to Place to Order, but also Delivery date? If so here is an amended function.

    Please Login or Register  to view this content.
    Now you will need to specify the Place to order column AND the delivery date column. So in column e:
    Please Login or Register  to view this content.
    edit: your formula in column F will now be
    Please Login or Register  to view this content.
    Last edited by cs454; 09-09-2014 at 07:38 AM.

+ 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. Combine multiple columns of data into one column
    By PMBottas in forum Excel General
    Replies: 8
    Last Post: 08-12-2014, 11:10 PM
  2. Replies: 6
    Last Post: 02-19-2014, 10:25 AM
  3. Combine multiple columns of data into one column???
    By seedman76 in forum Excel General
    Replies: 4
    Last Post: 03-07-2013, 02:20 AM
  4. Combine data from multiple rows into one row when other column data changes
    By nena473 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-22-2012, 03:56 PM
  5. Replies: 6
    Last Post: 05-03-2006, 04:30 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