Well, with that many repetitions I'd be tempted to use a User-defined
function (UDF), as a spreadsheet formula using normal functions will
be extremely long and difficult to maintain.

If I have time later on I may look into it.

Hope this helps.

Pete

On Mar 17, 7:57*pm, Mayo wrote:
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 -- Hide quoted text -

- Show quoted text -