|
Return multiple values using a lookup function
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, |
Return multiple values using a lookup function
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 - |
Return multiple values using a lookup function
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 - |
Return multiple values using a lookup function
Thanks Pete. The formula is incredible. I understand that the number of
reps could be monotanous so if you have time to look into it later on, then that would be great. I've never used a UDF so that would be a new experience for me. Is it possible if the reps are only 10 or 11 long? I am thinking the formula would still be quite lengthy. Thanks again! "Pete_UK" wrote: 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 - |
Return multiple values using a lookup function
If you want to play about with the formula I have given to you, this
will enable you to have up to 4 repetitions for each ID #: =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(COUNTIF(B:B,L2)2,","&INDEX(C:C,MATCH(L2&"_3", N:N,0)),"") &IF(COUNTIF(B:B,L2)3,","&INDEX(C:C,MATCH(L2&"_4", N:N,0)),"") I've just split this manually here so that you can better understand how it works. The first part will return N/A if there is no match, or it will return the first matching value (VLOOKUP and MATCH will always return the first matching value and then stop looking). The second part is made up of multiple IFs concatenated together - basically, these look for the next sequential item in column N and return "" (empty string) if there isn't one, or they return a comma followed by the corresponding value from column C. Notice the similarity in each of the IFs - the first has a comparison of 1, then 2, then 3 (and so on), and has a corresponding sequence number of _2, _3, _4 (and so on). So, if you want to take this further all you need to do is highlight this part in the formula bar: &IF(COUNTIF(B:B,L2)3,","&INDEX(C:C,MATCH(L2&"_4", N:N,0)),"") then do CTRL-C (copy), then move the cursor to the end of the formula and CTRL-V (paste) to tag a copy onto the end of the formula, and then change the 3 and _4 to 4 and _5 respectively. You can do this a few times, maybe to get up to 10 repetitions, and then copy the formula down. Obviously the formula will grow in length each time you do this. With a UDF it is written in VBA and can perform repetitive calculations like this through a loop, so that it will just return the result you want. Consequently, the formula will just be something like: =result(...) where result is the name of the UDF. However, I'm a bit pushed for time at the moment, so I'll come back to it later on. Hope this helps. Pete On Mar 17, 8:19*pm, Mayo wrote: Thanks Pete. *The formula is incredible. *I understand that the number of reps could be monotanous so if you have time to look into it later on, then that would be great. I've never used a UDF so that would be a new experience for me. *Is it possible if the reps are only 10 or 11 long? *I am thinking the formula would still be quite lengthy. Thanks again! "Pete_UK" wrote: 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 -- Hide quoted text - - Show quoted text - |
Return multiple values using a lookup function
Pete,
You are tremendously helpful and your explanation of this formula is crystal clear. For now, I will continue to muck around with the formula you have given me but if you have time later on, and you wouldn't mind helping me understand creating a UDF, then I'd be glad to accept your help. Based on your explanation, a UDF would make this worksheet far more manageable than it currently is. Hope you are a patient man Pete because this may take a bit of time for me to understand what steps I need to take. Thank you "Pete_UK" wrote: If you want to play about with the formula I have given to you, this will enable you to have up to 4 repetitions for each ID #: =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(COUNTIF(B:B,L2)2,","&INDEX(C:C,MATCH(L2&"_3", N:N,0)),"") &IF(COUNTIF(B:B,L2)3,","&INDEX(C:C,MATCH(L2&"_4", N:N,0)),"") I've just split this manually here so that you can better understand how it works. The first part will return N/A if there is no match, or it will return the first matching value (VLOOKUP and MATCH will always return the first matching value and then stop looking). The second part is made up of multiple IFs concatenated together - basically, these look for the next sequential item in column N and return "" (empty string) if there isn't one, or they return a comma followed by the corresponding value from column C. Notice the similarity in each of the IFs - the first has a comparison of 1, then 2, then 3 (and so on), and has a corresponding sequence number of _2, _3, _4 (and so on). So, if you want to take this further all you need to do is highlight this part in the formula bar: &IF(COUNTIF(B:B,L2)3,","&INDEX(C:C,MATCH(L2&"_4", N:N,0)),"") then do CTRL-C (copy), then move the cursor to the end of the formula and CTRL-V (paste) to tag a copy onto the end of the formula, and then change the 3 and _4 to 4 and _5 respectively. You can do this a few times, maybe to get up to 10 repetitions, and then copy the formula down. Obviously the formula will grow in length each time you do this. With a UDF it is written in VBA and can perform repetitive calculations like this through a loop, so that it will just return the result you want. Consequently, the formula will just be something like: =result(...) where result is the name of the UDF. However, I'm a bit pushed for time at the moment, so I'll come back to it later on. Hope this helps. Pete On Mar 17, 8:19 pm, Mayo wrote: Thanks Pete. The formula is incredible. I understand that the number of reps could be monotanous so if you have time to look into it later on, then that would be great. I've never used a UDF so that would be a new experience for me. Is it possible if the reps are only 10 or 11 long? I am thinking the formula would still be quite lengthy. Thanks again! "Pete_UK" wrote: 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 -- Hide quoted text - - Show quoted text - |
Return multiple values using a lookup function
I was tied up longer than I expected, so I haven't got chance to look
at it now. I'll pick it up in the morning if I have chance. Pete On Mar 17, 9:25*pm, Mayo wrote: Pete, You are tremendously helpful and your explanation of this formula is crystal clear. For now, I will continue to muck around with the formula you have given me but if you have time later on, and you wouldn't mind helping me understand creating a UDF, then I'd be glad to accept your help. * Based on your explanation, a UDF would make this worksheet far more manageable than it currently is. *Hope you are a patient man Pete because this may take a bit of time for me to understand what steps I need to take. Thank you "Pete_UK" wrote: If you want to play about with the formula I have given to you, this will enable you to have up to 4 repetitions for each ID #: =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(COUNTIF(B:B,L2)2,","&INDEX(C:C,MATCH(L2&"_3", N:N,0)),"") &IF(COUNTIF(B:B,L2)3,","&INDEX(C:C,MATCH(L2&"_4", N:N,0)),"") I've just split this manually here so that you can better understand how it works. The first part will return N/A if there is no match, or it will return the first matching value (VLOOKUP and MATCH will always return the first matching value and then stop looking). The second part is made up of multiple IFs concatenated together - basically, these look for the next sequential item in column N and return "" (empty string) if there isn't one, or they return a comma followed by the corresponding value from column C. Notice the similarity in each of the IFs - the first has a comparison of 1, then 2, then 3 (and so on), and has a corresponding sequence number of _2, _3, _4 (and so on). So, if you want to take this further all you need to do is highlight this part in the formula bar: &IF(COUNTIF(B:B,L2)3,","&INDEX(C:C,MATCH(L2&"_4", N:N,0)),"") then do CTRL-C (copy), then move the cursor to the end of the formula and CTRL-V (paste) to tag a copy onto the end of the formula, and then change the 3 and _4 to 4 and _5 respectively. You can do this a few times, maybe to get up to 10 repetitions, and then copy the formula down. Obviously the formula will grow in length each time you do this. With a UDF it is written in VBA and can perform repetitive calculations like this through a loop, so that it will just return the result you want. Consequently, the formula will just be something like: =result(...) where result is the name of the UDF. However, I'm a bit pushed for time at the moment, so I'll come back to it later on. Hope this helps. Pete On Mar 17, 8:19 pm, Mayo wrote: Thanks Pete. *The formula is incredible. *I understand that the number of reps could be monotanous so if you have time to look into it later on, then that would be great. I've never used a UDF so that would be a new experience for me. *Is it possible if the reps are only 10 or 11 long? *I am thinking the formula would still be quite lengthy. Thanks again! "Pete_UK" wrote: 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 -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Return multiple values using a lookup function
Excel 2007 Pivot Table
Most terrifying formula (of 2): =COUNTIF(Table2[Cust],Table1[[#This Row],[Cust]]) No limit on reps, no copy/paste/fill. Dynamic Tables. http://www.mediafire.com/file/wgxmyz...03_18_09a.xlsx |
Return multiple values using a lookup function
I hope you are still monitoring this thread. I've put together a UDF
which mimics the formula that I gave you, but which will handle any number of repeats (tested up to 32). You still need to have the formula in N2, i.e.: =IF(B2="","",B2&"_"&COUNTIF(B$2:B2,B2)) copied down as far as you need (the same number of entries as you have in column B). Then, with the UDF installed (see below) you can put this formula in M2: =res(L2) and just copy this down as far as you have entries in column L. To install the UDF, do Alt-F11 to bring up the VB Editor, then click Insert | Module, and then copy the following from this post and paste it into the window: Function res(my_ID) As String Dim reps As Integer, num_reps As Integer num_reps = Application.WorksheetFunction _ .CountIf(Range("B:B"), my_ID) If num_reps = 0 Then res = "N/A" Else res = Application.WorksheetFunction _ .VLookup(my_ID, Range("B:C"), 2, 0) If num_reps 1 Then For reps = 2 To num_reps res = res & "," & _ Application.WorksheetFunction _ .Index(Range("C:C"), _ Application.WorksheetFunction _ .Match(my_ID & "_" & reps, Range("N:N"), 0)) Next reps End If End If End Function Then just click on the Close icon to get back to the Excel window and now you can enter the formula in M2. I've manually broken a number of the lines in the UDF so that you don't get spurious line breaks in the newsgroup viewer that you are using. And that's all there is to it. When you save the file this VBA code is saved with it. This means that when you open the file subsequently you will be warned that it contains a Macro (assuming your security level is set to Medium). Hope this helps. Pete On Mar 17, 9:25*pm, Mayo wrote: Pete, You are tremendously helpful and your explanation of this formula is crystal clear. For now, I will continue to muck around with the formula you have given me but if you have time later on, and you wouldn't mind helping me understand creating a UDF, then I'd be glad to accept your help. * Based on your explanation, a UDF would make this worksheet far more manageable than it currently is. *Hope you are a patient man Pete because this may take a bit of time for me to understand what steps I need to take. Thank you |
Return multiple values using a lookup function
Excellent! I was relying on the notifications to let me know when you posted
a response and for some reason I did not receive an email. Thank you for all the time you've put into this for me and my apologies for taking so long to thank you. Calay Mayo "Pete_UK" wrote: I hope you are still monitoring this thread. I've put together a UDF which mimics the formula that I gave you, but which will handle any number of repeats (tested up to 32). You still need to have the formula in N2, i.e.: =IF(B2="","",B2&"_"&COUNTIF(B$2:B2,B2)) copied down as far as you need (the same number of entries as you have in column B). Then, with the UDF installed (see below) you can put this formula in M2: =res(L2) and just copy this down as far as you have entries in column L. To install the UDF, do Alt-F11 to bring up the VB Editor, then click Insert | Module, and then copy the following from this post and paste it into the window: Function res(my_ID) As String Dim reps As Integer, num_reps As Integer num_reps = Application.WorksheetFunction _ .CountIf(Range("B:B"), my_ID) If num_reps = 0 Then res = "N/A" Else res = Application.WorksheetFunction _ .VLookup(my_ID, Range("B:C"), 2, 0) If num_reps 1 Then For reps = 2 To num_reps res = res & "," & _ Application.WorksheetFunction _ .Index(Range("C:C"), _ Application.WorksheetFunction _ .Match(my_ID & "_" & reps, Range("N:N"), 0)) Next reps End If End If End Function Then just click on the Close icon to get back to the Excel window and now you can enter the formula in M2. I've manually broken a number of the lines in the UDF so that you don't get spurious line breaks in the newsgroup viewer that you are using. And that's all there is to it. When you save the file this VBA code is saved with it. This means that when you open the file subsequently you will be warned that it contains a Macro (assuming your security level is set to Medium). Hope this helps. Pete On Mar 17, 9:25 pm, Mayo wrote: Pete, You are tremendously helpful and your explanation of this formula is crystal clear. For now, I will continue to muck around with the formula you have given me but if you have time later on, and you wouldn't mind helping me understand creating a UDF, then I'd be glad to accept your help. Based on your explanation, a UDF would make this worksheet far more manageable than it currently is. Hope you are a patient man Pete because this may take a bit of time for me to understand what steps I need to take. Thank you |
Return multiple values using a lookup function
Hello,
Another suggestion is my UDF vlookupall: http://www.sulprobil.com/html/lookup-variants.html Regards, Bernd |
| All times are GMT +1. The time now is 04:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com