Good evening all!
I was wondering if anyone could possible offer a solution/guidance to a problem I'm having.
I have 2 tables of data on 2 worksheets. I have 2 criteria that I wish to search, and for all matches, concatenate the values.
Unfortunately I cannot use VBA, UDFs or addins for this (IT security protocols).
The tables below should help illustrate what I'm trying to achieve.
Table 1 - where i want the concatenated values to populate:
<tbody>
</tbody>
Table 2 - values to be concatenated:
<tbody>
</tbody>
So what i'm trying to achieve is below:
<tbody>
</tbody>
The criteria to match therefore are the name and week, with all values matching these to be concatenated as above.
I've tried a number of things but can't seem to get any to work, I'm thinking the best bet will be an index, match, concatenate combo but keep getting #value or #ref errors.
I've figured out how to use index match to pull a single value, but its the inclusion of concatenation that is throwing me.
Here is the actual formula to pull a single value:
=INDEX(Comments!$B$3:$AJ$66,MATCH($C$1,Comments!$A$2:$AJ$2,0),MATCH(A4,Comments!$A$3:$A$66,0))
Could anyone advise how to either incorporate the concatenation (i've pulled all information into one table as it really doesn't like indirect for some reason) - or what would be even better if anyone could advise how to amend it so that it will search based on an indirect range, and then concatenate the information.
Sorry its such a long post, i'm trying to make it super clear what i'm trying to achieve, its been boxing my brain for weeks and I can see no solution!
Thank you all in advance.
I was wondering if anyone could possible offer a solution/guidance to a problem I'm having.
I have 2 tables of data on 2 worksheets. I have 2 criteria that I wish to search, and for all matches, concatenate the values.
Unfortunately I cannot use VBA, UDFs or addins for this (IT security protocols).
The tables below should help illustrate what I'm trying to achieve.
Table 1 - where i want the concatenated values to populate:
| Name | week | concat info |
| Person 1 | week 1 | |
| Person 2 | week 1 |
<tbody>
</tbody>
Table 2 - values to be concatenated:
| Name | week 1 | week 1 | week 1 | week 1 | week 1 | week 2 | |||
| Person 1 | Apples | oranges | pears | apples | pears | pineapple | |||
| Person 2 |
<tbody>
</tbody>
So what i'm trying to achieve is below:
| Name | week | concat values |
| Person 1 | week 1 | Apples, oranges, pears, apples, pears |
| Person 2 | week 1 |
<tbody>
</tbody>
The criteria to match therefore are the name and week, with all values matching these to be concatenated as above.
I've tried a number of things but can't seem to get any to work, I'm thinking the best bet will be an index, match, concatenate combo but keep getting #value or #ref errors.
I've figured out how to use index match to pull a single value, but its the inclusion of concatenation that is throwing me.
Here is the actual formula to pull a single value:
=INDEX(Comments!$B$3:$AJ$66,MATCH($C$1,Comments!$A$2:$AJ$2,0),MATCH(A4,Comments!$A$3:$A$66,0))
Could anyone advise how to either incorporate the concatenation (i've pulled all information into one table as it really doesn't like indirect for some reason) - or what would be even better if anyone could advise how to amend it so that it will search based on an indirect range, and then concatenate the information.
Sorry its such a long post, i'm trying to make it super clear what i'm trying to achieve, its been boxing my brain for weeks and I can see no solution!
Thank you all in advance.
