Question description:
This user has given permission to use the problem statement for this
blog.
I have a list of names which I want to compare to a set of columns which has a header and then a list of names in each column. I need the return to be a concatenated list of the headings of the columns each name appears in.
Solved by M. H. in 14 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/08/2018 - 12:49
Hello, I understand that you need to return a list of the headers where a name was found, right?
User
16/08/2018 - 12:49
Yes...
User
16/08/2018 - 12:49
essentially I have some people's names in one column on a worksheet
Excelchat Expert
16/08/2018 - 12:49
Alright, we can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
User
16/08/2018 - 12:49
I want to compare each name against an array of columns on another worksheet and return the headers
Excelchat Expert
16/08/2018 - 12:49
The solution to this will depend on how many columns you have for checking because if it's too many, the formula may not fit at all.
User
16/08/2018 - 12:50
OK...
User
16/08/2018 - 12:50
There are roughly 450 columns
Excelchat Expert
16/08/2018 - 12:50
Oh, if that's the case I don't think it'll fit in a formula.
Excelchat Expert
16/08/2018 - 12:50
Because the way to do this is to have a match per each column and then return the header name if it is a match.
User
16/08/2018 - 12:51
yes, that's what I'm looking for
Excelchat Expert
16/08/2018 - 12:51
Since it's 450 columns that needs to be checked, I don't think the formula will fit.
User
16/08/2018 - 12:51
what's the highest number of columns that it would work for?
User
16/08/2018 - 12:52
could split it into "batches" and then concatenate the split results into one final result
Excelchat Expert
16/08/2018 - 12:52
You can try that. Here's what I'd suggest. Since we have a very limited amount of time to do this, it would not be possible for me to do it for you, so I'll create a sample formula you can expand yourself, would that be acceptable?
User
16/08/2018 - 12:53
That's great
Excelchat Expert
16/08/2018 - 12:53
Please create a sample data using the sheet to the right.
User
16/08/2018 - 12:53
OK
User
16/08/2018 - 12:54
Done
Excelchat Expert
16/08/2018 - 12:55
Thank you. Give me a few minutes to figure out the best possible solution for this.
Excelchat Expert
16/08/2018 - 12:58
Column B will be your formula just for 3 columns. You can imagine how long this will be for 450 columns.
User
16/08/2018 - 12:59
wow
User
16/08/2018 - 12:59
yeah....
Excelchat Expert
16/08/2018 - 12:59
If you have the patience to do it this way, this should be enough but the best approach to this would have to be VBA/Macro.
Excelchat Expert
16/08/2018 - 12:59
Unfortunately, we currently do not support VBA Macro solutions.
Excelchat Expert
16/08/2018 - 01:00
Here's for the first column:
Excelchat Expert
16/08/2018 - 01:00
=if(isnumber(match(A2,Sheet2!A:A,0)),Sheet2!A$1,"")
Excelchat Expert
16/08/2018 - 01:00
What you need to change is Sheet2!A:A and Sheet2!A$1
Excelchat Expert
16/08/2018 - 01:00
So for the next column it will be:
Excelchat Expert
16/08/2018 - 01:00
if(isnumber(match(A2,Sheet2!B:B,0)),Sheet2!B$1,"")
Excelchat Expert
16/08/2018 - 01:01
Then concatenate them by using the &
User
16/08/2018 - 01:01
That's ok... thank you!
Excelchat Expert
16/08/2018 - 01:01
Glad to have helped!
Excelchat Expert
16/08/2018 - 01:01
Would there be anything else that I can help you with regards to the original question?
User
16/08/2018 - 01:02
No that's great thank you very much
Excelchat Expert
16/08/2018 - 01:02
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
16/08/2018 - 01:02
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
16/08/2018 - 01:02
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
16/08/2018 - 01:02
will do!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
