+ Reply to Thread
Results 1 to 7 of 7

Using multiple IF+Concatenate to return data to a single cell

  1. #1
    Registered User
    Join Date
    08-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    5

    Using multiple IF+Concatenate to return data to a single cell

    Hello

    I found this solution (IF(Z5="",Z5,Z1&Z5)) in another post about linking IF with concatenate.

    I was wondering if you could do this for a number of cells and combine the results in a single cell?

    I'm looking at some survey data and I'm trying to:

    - find those cells with data in them (e.g. responses to a question).
    - then, if this is the case, concatenate the question with the answer
    - then combine all cells where there are answers into one cell

    Is that possible? I've looked into nested IFs but couldn't get it to work.

    Many thanks in advance.



    Jeremy

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,001

    Re: Using multiple IF+Concatenate to return data to a single cell

    yes, maybe a sample spreadsheet attached to the post will help us see how the data is laid out
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    08-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Using multiple IF+Concatenate to return data to a single cell

    Hi

    Thanks for your help. Here you go:

    IF query.xlsx

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,001

    Re: Using multiple IF+Concatenate to return data to a single cell

    you example - just show the response and not the question

    try
    =IF(C2<>"","Q. "&C$1&" A."&C2,IF(E2<>"","Q. "&E$1&" A. "&E2,""))

    includes Question and Answer
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Using multiple IF+Concatenate to return data to a single cell

    Hi Etaf

    Thanks for your help, and sorry it's taken me a few days to come back to you.

    I've had a look at the solution and while it looks like it's including the right cells it's not showing the data.

    Ideally, the results field (G2) would say:

    "COMMENTS - How would you rate the cleanliness and maintenance of the gardens and grounds?, Sample text1. COMMENTS - Overall, how would you rate the cleanliness of the Hotel?Sample text3"

    Am I doing something wrong?

    Thanks again for you help.

    Jeremy

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,001

    Re: Using multiple IF+Concatenate to return data to a single cell

    Try
    =IF(AND(C2="",E2=""),"",IF(AND(C2<>"",E2<>""),C$1&", "&C2&". "&E$1&", "&E2, IF(C2<>"", C$1&", "&C2, E$1&", "&E2)))
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-01-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Using multiple IF+Concatenate to return data to a single cell

    That's perfect, thank you so much.

    Jeremy

+ 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. [SOLVED] How to return multiple values to a single cell based on multiple criteria
    By lwallace in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2014, 06:32 AM
  2. Need macro to search multiple data points from multiple entries and return single value
    By Redbullmoo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2013, 02:16 AM
  3. macro to concatenate data from column into a single cell
    By Poonjas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-23-2012, 04:29 AM
  4. Replies: 1
    Last Post: 08-24-2012, 05:52 AM
  5. How to return multiple IF values in a single cell?
    By macmanjpc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-15-2008, 08:54 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