+ Reply to Thread
Results 1 to 15 of 15

Find Cell content in multiple columns and retrieve the result wit semicolon separated

  1. #1
    Registered User
    Join Date
    05-11-2013
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    21

    Find Cell content in multiple columns and retrieve the result wit semicolon separated

    Hi Team,

    Could you please help me finding cell content in multiple columns and retrieve the result in ";" separated.

    Many thanks in advance.

    Kind Regards,
    Rajani

    E.g.
    Attached Files Attached Files
    Last edited by rajani85; 11-29-2020 at 11:56 PM.

  2. #2
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    Hi Rajani,

    Try below code assuming you have a header in row #1 ...
    Please Login or Register  to view this content.
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  3. #3
    Registered User
    Join Date
    05-11-2013
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    Hi Nankw83,

    Many thanks for the help. The given code is applicable to the cell value available in the same row.

    My Apologies, I have provided my requirement wrong.

    Please find the attached Image & example will help you understand more about the requirement.

    Excel Requirment.JPG

    John Smith Robin Peter Tom Ricky Max Avaiability
    1 9 8 4 1 3 3 Peter; Tom; Ricky
    2 2 4 3 5 5 5 Smith; Tom
    3 6 5 1 4 3 6 Peter; Ricky; Max
    4 4 6 3 5 1 3 Smith; Robin; Peter; Tom
    5 6 7 5 2 5 6 Robin; Peter; Tom; Ricky, Max

  4. #4
    Forum Expert nankw83's Avatar
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    1,709

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    I don't understand how you're identifying the names … For the first row, what should I check to flag Peter; Tom; Ricky ?

  5. #5
    Registered User
    Join Date
    05-11-2013
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    I need to find each cell value available in cell A1 in all the respective columns and provide the column name after the last column.

    Hope you understand the need.

  6. #6
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    ... we hope you will upload a sample 'xls' file, not image ...

  7. #7
    Registered User
    Join Date
    05-11-2013
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    Hi Porucha,

    Sorry to say, I'm unable to upload the excel file.

    The attachment option available in Advance is not taking me anywhere. Else I would have done that in the first threat itself.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    UDF
    Use in cell like
    =JoinIf(B$1:G2,B2:G2,A2,";")
    Then copy down
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-11-2013
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    Hi Jindon,

    Many thanks for your quick reply.

    The code & formula is applicable for the same value available in the same row but not the columns. My requirement is to look into the columns and retrieve the column header in ';' separated.

    Kind Regards,
    Rajani
    Last edited by rajani85; 11-27-2020 at 12:23 AM.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    It should.

    Upload your workbook and the result that you want.

  11. #11
    Registered User
    Join Date
    05-11-2013
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    Please find attached the sample file attached for your reference.
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    You said,
    Quote Originally Posted by rajani85 View Post
    E.g.
    PHP Code: 
    A    B    C    D    E    F    G    Availability
    2    1    2    5    4    4    2    C
    G
    1    3    3    1    2    1    5    D
    F
    3    3    1    1    3    4    1    B
    E
    2    2    2    1    3    5    2    B
    CG
    1    3    3    1    5    2    1    D

    It is describing to get the matched column titles from B:G that matches to Col.A value

    Now your result doesn't make sense to me.
    If you explain the logic step by step, it will help.

  13. #13
    Registered User
    Join Date
    05-11-2013
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    Hi Jindon,

    My apology, my initial requirement example was wrong. I have edited the initial post with the correct example.

    Hope this time will help you understand better.

    Requirement:

    Basically I wanted to search for a cell value available in the first column in the adjacent columns and retrieve the column headings with ";" separated in the last column.

    If there are duplicate values in a single column it should retrieve the column name once.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    Formula in H2
    =JoinIf(A2,$B$1:$G$6,"; ")

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    05-11-2013
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Find Cell content in multiple columns and retrieve the result wit semicolon separated

    Wow great!!! Tons of thanks all for the 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. Replies: 3
    Last Post: 07-28-2020, 04:54 PM
  2. Replies: 2
    Last Post: 06-14-2017, 04:15 PM
  3. Replies: 9
    Last Post: 12-15-2016, 01:12 PM
  4. Find missing data in columns ignoring blanks, separated by semicolon
    By Cboggie in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-28-2014, 10:04 PM
  5. split multiple semicolon separated values into new rows for multiple columns
    By bruno08102013 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-05-2014, 05:27 PM
  6. Import multiple semicolon separated csv files
    By Ivodv in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-07-2012, 08:17 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