Thanks so much! The formula does exactly what I wanted it to do. You were
correct that I have more than a possible 3 repititions. There is a
possibility of up to 32 repititions. My apologies for taking up so much
time on this issue, but could you please explain how I can adjust the formula
to account for that many reps?

Thanks.

"Pete_UK" wrote:

Okay, while I was waiting I put this in N2:

=IF(B2="","",B2&"_"&COUNTIF(B$2:B2,B2))

and copied it down to N9. It gives this:

105802_1
111186_1
116384_1
118581_1
134777_1
135246_1
135246_2
135246_3

i.e. a sequential count tagged on the the end of the ID number. You
can hide column N if you want to.

Then I put this in M2:

=IF(ISNA(MATCH(L2,B:B,0)),"N/A",VLOOKUP(L2,B:C,2,0))&IF(COUNTIF(B:B,L2)
1,","&INDEX(C:C,MATCH(L2&"_2",N:N,0)),"")&IF(COUN TIF(B:B,L2)
2,","&INDEX(C:C,MATCH(L2&"_3",N:N,0)),"")

and copied this down to M9. Notice that I've used full column
references as I suspect you have more data than just the 9 rows of
your example, and this means you don't have to adjust things. This is
what I got with your data:

2nd ID Find Matching Location:
105802 03973
111186 03205
116384 03258
118581 03952
119582 N/A
134777 02580
135246 03198,03258,02834
138106 N/A

which is what you said you wanted.

The formula just gives a maximum of up to 3 repetitions, as per your
example, but if you have more than this then post back, letting me
know how many you are likely to encounter (if it's only a few more
then the formula can be amended quite easily).

Hope this helps.

Pete


On Mar 17, 7:25 pm, Mayo wrote:
Column K is not used so please feel free to use K or N.

Thank you.



"Pete_UK" wrote:
Are all the columns between C and L used, or can I use one of them for
a helper column to make the formula easier?

Failing that, can I use column N (assuming column M is where you want
the results), and then hide it if necessary?

Pete

On Mar 17, 3:43 pm, Mayo wrote:
I have looked through the threads and I have been unsuccessful in adapting
the formulas for my needs. Here is my request:

I am attempting to return mutiple matching values based on matching values.

Here is a sample set below. The duplicates are intentional because one ID #
can be in two locations and two locations will have multiple ID #s:

ID # Location 2nd ID Find Matching Location:
105802 03973 105802
111186 03205 111186
116384 03258 116384
118581 03952 118581
134777 02580 119582
135246 03198 134777
135246 03258 135246
135246 02834 138106

Let's assume that the ID # are in column B1:B9 (all columns include a
header), Locations are in column C1:C9, and the IDs' to be matched are in
column L1:L9. The ID #s in column L are not duplicated because the goal is
to match up all Locations with each ID # either by comma separated values or
otherwise.

What I'd like it to look like at the end is:

Matching Location
03973
03205
03258
03952
N/A
02580
03198,03258,02834

Please let me know if you require further information.

Thank you,- Hide quoted text -

- Show quoted text -