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.
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.
Last edited by rajani85; 11-29-2020 at 11:56 PM.
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
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
I don't understand how you're identifying the names … For the first row, what should I check to flag Peter; Tom; Ricky ?
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.
... we hope you will upload a sample 'xls' file, not image ...
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.
UDF
Use in cell like
=JoinIf(B$1:G2,B2:G2,A2,";")
Then copy down
Please Login or Register to view this content.
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.
It should.
Upload your workbook and the result that you want.
Please find attached the sample file attached for your reference.
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.
Formula in H2
=JoinIf(A2,$B$1:$G$6,"; ")
Please Login or Register to view this content.
Wow great!!! Tons of thanks all for the help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks