+ Reply to Thread
Results 1 to 13 of 13

How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'....

  1. 10-18-2013, 03:50 AM #1
    jaysakle
    jaysakle is offline
    Registered User
    Join Date
    06-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    93

    Question How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'....

    Dear Experts,

    I am here again to seek your guidance.....

    I learn how to collect data from multiple cell to single cell using "& &".

    Now, I am trying to use 'IF' function to collect data/text from multiple cell to single cell when answer of a particular cell is 'Yes' / 'No'.

    I wish that data shall be separately collected in a assigned cell based on yes and no answer.

    I wrote a formula as per my limited knowledge, but it won't work...

    I have attached herewith "Book 5" for your kind reference.

    Thanks in advance....
    Attached Files Attached Files
    Last edited by jaysakle; 10-18-2013 at 03:52 AM.
    Reply With QuoteRegister To Reply
  2. 10-18-2013, 04:06 AM #2
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'

    Try in this way..even if i am not sure if the result will be ok for you.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Add as many conditions you want......
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  3. 10-18-2013, 04:10 AM #3
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,726

    Re: How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'

    You can do it like this in E2:

    =IF(B2="Yes",A2&" ","")&IF(B3="Yes",A3&" ","")&IF(B4="Yes",A4&" ","")&IF(B5="Yes",A5,"")

    For your other formula, just change Yes to No.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  4. 10-18-2013, 05:02 AM #4
    jaysakle
    jaysakle is offline
    Registered User
    Join Date
    06-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'

    Quote Originally Posted by Pete_UK View Post
    You can do it like this in E2:

    =IF(B2="Yes",A2&" ","")&IF(B3="Yes",A3&" ","")&IF(B4="Yes",A4&" ","")&IF(B5="Yes",A5,"")

    For your other formula, just change Yes to No.

    Hope this helps.

    Pete
    Dear Pete,

    Thank you very much.

    The formula suggested by you working nicely. I have one query that, Can i use this formula for 100 names using "If" function?

    Please guide.

    Thanks in advance...
    Reply With QuoteRegister To Reply
  5. 10-18-2013, 05:03 AM #5
    jaysakle
    jaysakle is offline
    Registered User
    Join Date
    06-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'

    Dear Fotis 1991,

    Thank you very much for the help. Your suggestion works.

    Thanks again...
    Reply With QuoteRegister To Reply
  6. 10-18-2013, 05:06 AM #6
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'

    In Excel 2007 you can use until 64 ifs...

    Perhaps a VBA code will do it!
    Reply With QuoteRegister To Reply
  7. 10-18-2013, 05:15 AM #7
    jaysakle
    jaysakle is offline
    Registered User
    Join Date
    06-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'

    Thank you very much.
    64 ifs are sufficient presently for me.
    If I need more than that, then I will try the VBA code....of course under your guidance....

    Thanks again for the help.
    Reply With QuoteRegister To Reply
  8. 10-18-2013, 05:16 AM #8
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,726

    Re: How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'

    Quote Originally Posted by jaysakle View Post
    ... Can i use this formula for 100 names using "If" function?...
    Well, you could do as the IF functions are not nested, but it would be a very long formula.

    Take a look at this thread:

    http://www.excelforum.com/excel-form...ml#post3441884

    to see another way of achieving multiple concatenations.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  9. 10-18-2013, 05:49 AM #9
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,726

    Re: How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'

    In answer to the questions you asked in the other thread, please see attached file.

    I've added these formulae in the cells stated (also shown in blue):

    C2: =IF(B2=F$1,A2,"")
    D2: =IF(C2="","",C2)
    D3: =D2&IF(OR(LEN(D2)=0,LEN(C3)=0),"",", ")&C3

    The formula in C2 is copied down to the bottom of the data, as is the formula in D3. Then I have this formula in F2:

    =INDEX(D:D,COUNTA(D:D)+1)

    Now, compare those formulae with the ones in the other thread.

    I've also added a drop-down on cell F1 (yellow) so you can choose either Yes or No and the result in F2 will automatically adjust.

    Notice how the multiple concatenation here is done by a simple formula which repeatedly adds the new names onto what has gone before as the formula is copied down the column, rather than in one long formula.

    Hope this helps.

    Pete
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 10-18-2013, 05:52 AM #10
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'

    Too much cleaver Pete!
    Reply With QuoteRegister To Reply
  11. 10-18-2013, 06:14 AM #11
    jaysakle
    jaysakle is offline
    Registered User
    Join Date
    06-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'

    Dear Pete_UK,

    Great help....

    Thanks a lot again....!
    Reply With QuoteRegister To Reply
  12. 10-18-2013, 06:17 AM #12
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,726

    Re: How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'

    You're welcome.

    To make it look better, just hide columns C and D, then when you change Yes to No or vice-versa it looks like MAGIC !!

    Pete
    Reply With QuoteRegister To Reply
  13. 10-18-2013, 07:02 AM #13
    jaysakle
    jaysakle is offline
    Registered User
    Join Date
    06-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    93

    Re: How to collect data/text from multiple cell to one cell when answer of a cell is 'Yes'

    Dear Pete_UK

    I did it... working nicely and smoothly.....
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Search cell for multiple text , return comma separated text in separate cell if found
    By dangerdoug in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-01-2013, 01:52 PM
  2. Trying to collect data from OPC will only update in 1 cell ,not down entire column
    By Cydog in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-30-2012, 09:35 PM
  3. How to collect data from many cells in a row into one cell?
    By rcurious in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-18-2012, 02:35 PM
  4. Excel 2007 : multiple cell text data entry to a single cell
    By sticktoexecute in forum Excel General
    Replies: 2
    Last Post: 03-27-2009, 01:35 PM
  5. [SOLVED] Conditionally collect data in one cell?
    By GenieNitro in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-13-2008, 09:08 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1