Forum Discussion
show the 2nd data with same value
Hi, I need to found a way that will look the columns where the number of time appear and show the name of that EFC. most of the time, we can have the same number for different EFC code and I need to be able to show the 2nd or the third one is happen to the board next to it. here what I explain in the picture. we see that 1C01 and ES10 have the same number but that show in the board only 1C01 2 time. I use the formule "=INDEX(A18:A108,MATCH(LARGE($B$18:$B$108,1),$B$18:$B$108,0))" and "=INDEX(A18:A108,MATCH(LARGE($B$18:$B$108,2),$B$18:$B$108,0))" for the second box but we see the result.
thank you for your help
Please refer to the attached with a workaround in case you don't have access to the TEXTJOIN function.
6 Replies
Since you didn't provide a sample workbook, I prepared one with a slightly different layout and used the following Array Formula which requires confirmation with Ctrl+Shift+Enter to get the multiple occurrences of EFC Codes if any.
=TEXTJOIN(", ",TRUE,IF($B$2:$B$23=LARGE(IF(FREQUENCY($B$2:$B$23,$B$2:$B$23),$B$2:$B$23),D2),$A$2:$A$23,""))- thank you for that quick response, I forget something important, I have the Excel 2013, unfortunately I don't have the nice new futur like "TextJoin", how I can do the same with my version?
Please refer to the attached with a workaround in case you don't have access to the TEXTJOIN function.
Formula always returns the value for first found largest number, i.e. 1C01. To return all of them it's bit another technique with regular formulas, but that better to demonstrate on the sample file. Or, if you are on modern Excel, use FILTER() function.
