+ Reply to Thread
Results 1 to 17 of 17

Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

  1. #1
    Registered User
    Join Date
    02-18-2017
    Location
    Malaysia
    MS-Off Ver
    office 2010
    Posts
    9

    Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    Hi
    attached the sample spreadsheet and expected results. looking for a solution using excel functions. VBA solution not preferred because this spreadsheet will be shared to others and also kept in google sheet.

    googled, search in this forum but not able to find with the exact result.

    Appreciate your help!

    Thanks

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    1) To accomplish concatenation like this requires "helper columns". See D:E on the data sheets.

    2) This also requires your DATA sheet be sorted exactly as you have shown in the sample, sorted together by rooms and dates.

    3) Then the VLOOKUP shown on the results will work.


    I would be remiss if I did not point out that the formula-based requirements on a need like this places a LOT more stress on the user than simply installing a macro that can accomplish this same result with no need for ANY of the caveats above, a macro, a single click on a button or an automated macro that keeps the "result" sheet updated all the time is far less stress on the user.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    02-18-2017
    Location
    Malaysia
    MS-Off Ver
    office 2010
    Posts
    9

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    Hi
    Thanks a lot for your response , very much appreciated for your help.
    It solves my first question.
    I have one more request, how to add two more criteria in to the VLOOKUP? or can we use IF function?
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,965

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    I assume you mean data in Columns A & B of "Data": if so concatenate them with Data in G

    so guessing ...

    in G2 of "Data"

    =E2&" "& A2&" "&B2&IF(F2=F3,CHAR(10)&G3,"")

  5. #5
    Registered User
    Join Date
    02-18-2017
    Location
    Malaysia
    MS-Off Ver
    office 2010
    Posts
    9

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    Hi
    sorry for not explaining in detail in the last thread
    My requirement is
    4 Criteria's ( Flag, Importance, Room Name, Start Date) in 4 column and one result column in "data" tab.

    in the report sheet.
    (1) Flag - Its a drop down list menu and have 3 options - All ( all the data), Yes or No
    (2) Importance - Its a drop down list menu and have 3 options - All (all the data), VIP or Normal
    (3) Room Name in rows
    (4) Start date in columns.

    attached the sample data.

    thanks once again for your help.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,965

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    Why is Flag only in B1?

    The attached (I believe) offers a much better was of presenting the data.

    "Activities" tab contains the data to input into the Calendar (automatically) and you can have 7 entries per day although this could be extended.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-18-2017
    Location
    Malaysia
    MS-Off Ver
    office 2010
    Posts
    9

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    Hi
    after going through this excelforum suggestion prepared a sample spreadsheet.
    this spreadsheet contains two tabs , one is "data" and other one "Report - By formula" (which is partially achieved report through formula)

    "Data" sheet
    (1) Multiple Criteria is picked from the Column B & Column D (two columns)
    (2) Results (Multiple) from Column A
    (3) two helper columns created as per suggestion ( H & I )

    "Report - By formula" Sheet
    (1) Primary requirement and looking for solution : Used VLOOKUP Function and achieved the result partially .
    Problem is - on N6 it suppose to show multiple results (VH-1 & VH-2) but it shows only VH-1. ie. it picks the first instance only.
    It can show the multiple result when the information are next to each other.
    Can it be fixed with VLOOKUP or some other formula required?
    (2) Optional requirement : Is the report can be based on the B1 - Dropdown selection?
    (3) Optional requirement : Is the report can be based on the B2 - Dropdown selection?

    thanks once again for your help.
    Attached Images Attached Images
    Attached Files Attached Files

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    Quote Originally Posted by JBeaucaire View Post
    2) This also requires your DATA sheet be sorted exactly as you have shown in the sample, sorted together by rooms and dates.
    Quote Originally Posted by JBeaucaire View Post
    I would be remiss if I did not point out that the formula-based requirements on a need like this places a LOT more stress on the user than simply installing a macro that can accomplish this same result with no need for ANY of the caveats above, a macro, a single click on a button or an automated macro that keeps the "result" sheet updated all the time is far less stress on the user.
    The issues you are starting to outline, such as non-adjacent values from the data do not get concatenated together, is a result of the formula based approach. You must sort the data by room/date to get the formula-based results.

    VBA would not have any of these needs.

  9. #9
    Registered User
    Join Date
    02-18-2017
    Location
    Malaysia
    MS-Off Ver
    office 2010
    Posts
    9

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    Hi JBeaucaire
    thanks for your reply.
    i understood your earlier email and the contents after applied the formula in to the real time data.
    can you please suggest me the VBA Codes ? In this case i need to maintain a offline file ( excel with VBA Codes) and online data (shared with other users to update the data at google sheets).
    do you have suggestion to use the VBA Codes in google spreadsheet?

    thanks in advance

  10. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    I have no comments on Google Sheets.

  11. #11
    Registered User
    Join Date
    02-18-2017
    Location
    Malaysia
    MS-Off Ver
    office 2010
    Posts
    9

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    Noted thanks.

    please help If you have any VBA Codes to match this requirement
    (1) Multiple criteria ( 2 until 4)
    (2) Multiple results ( 0 to 6) shown in single cell.

    thanks.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,965

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    VLOOKUP or MATCH will only find the first value where there are duplicate/multiple "keys".

    If you had use the "calendar" I offered then these problems would not occur.

    Re requirements (2) and (3): it would be possible to generate a "dynamic" table based on "Data".

    I nothing about Google Sheets.

  13. #13
    Registered User
    Join Date
    02-18-2017
    Location
    Malaysia
    MS-Off Ver
    office 2010
    Posts
    9

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    Hi JohnTopley
    Thanks for your suggestion.

    I have gone through your calendar spreadsheet. Its a useful one but i don't think i can convert this solution to my current requirement.

    as i mentioned before my requirement is - multiple criteria & multiple results in one single cell.

    if i not mistaken i understood your example "calendar" spreadsheet is one criteria , multiple results restricted to 7 cells.

    thanks for your support.

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,965

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    Each cell for a give date can have any data you choose: you could extended the number of rows per day from 7 if required.

    Looking at your example I cannot find any date (day) which has more than 2 entries.

  15. #15
    Registered User
    Join Date
    02-18-2017
    Location
    Malaysia
    MS-Off Ver
    office 2010
    Posts
    9

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    The example is with lesser data.
    actual spreadsheet is with more information & complex structure.
    (1) Dates Starting from 01Feb2017 to 15Apr2017
    (2) Vehicle No. Only two is shown actually it is around 28
    (3) Total work id is 147 and no of Vehicle is 28 , so total work id is 4000+ and open item between feb to april is around 2200.

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,965

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    Maximum work IDs on any one day?

  17. #17
    Registered User
    Join Date
    02-18-2017
    Location
    Malaysia
    MS-Off Ver
    office 2010
    Posts
    9

    Re: Multiple Criteria and Multiple results in Single Cell separated with Comma or Space

    Number of column & number of rows will not change include the Work ID's. Actually work ID's are unique and we reserved few for future.

    update will be there on the various data
    (1) Work Status ( Change from open to done or deleted)
    (2) planned date ( change in dates)
    (3) actual completion date ( change in dates)
    let me know if you need further information.
    solution by VBA or any excel formula will do
    thanks for your help

+ 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. Separating multiple data separated by a comma into single cells.
    By sgmgrider in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-29-2015, 05:58 PM
  2. [SOLVED] Vlookup table array matching with multiple value in single cell separated by comma.
    By kannoy in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-04-2015, 04:03 AM
  3. Multiple Search Criteria produces (list) Multiple results in a single cell
    By snake007uk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-02-2014, 12:13 PM
  4. Replies: 4
    Last Post: 03-13-2013, 07:52 AM
  5. Help with counting multiple items in a cell separated by a comma
    By excelnoobies in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-11-2012, 02:43 AM
  6. Sum values based on multiple criteria (comma delimited) in a single cell
    By leem888 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 01-07-2011, 02:49 PM
  7. Replies: 4
    Last Post: 09-04-2010, 03:37 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