I have Office 365, using PC
I have attached my sample data file, I am trying to filter out the duplicates in an array and list the unique instance of the duplicates horizontally.
Please see provide xlsx, appreciate any help on the matter
Hi Alver,
Welcome to our forum! Please tell us whether you're using Excel Office 365 so we know what functions you have available to use?
Thanks,
Mynda
Thank you for your response, I am using Excel Office 365 on a PC
One of my formula is =SORT(UNIQUE(IF(COUNTIF($C$5:V$7,C5:V7)>2,INDEX(Midday_Last3,,),""),))
| 5 | 42 | ||||||||||||||||||
| 5 | 42 | ||||||||||||||||||
| 5 | 42 |
I was able to filter the numbers but not sure how to display it properly Horizontally
| 5 | 42 |
Hi Alver,
UNIQUE considers a whole row or whole column when it looks for unique results. Therefore each row or column in your 2D array has a different sequence of numbers, as opposed to assessing the numbers in the entire 2D array as a single column or row of values.
Therefore, you can use this formula where SMALL reduces the array to a single row of values:
=IFERROR(UNIQUE(SMALL(IF(COUNTIF(Midday_Last3,Midday_Last3)>2,INDEX(Midday_Last3,,),""),COLUMN(C3:V4)),TRUE),"")
Hope that helps.
Mynda
Hi Mynda,
Thanks a lot for the answer it did it automatically generate the numbers, but the first instance of the duplicate is not being added, not sure why for the second part where the criteria if the occurrence of the number is 2x the 1st instance is skip in the list generated.
=IFERROR(UNIQUE(SMALL(IF(COUNTIF(Midday_Last3,Midday_Last3)=2,INDEX(Midday_Last3,,),""),COLUMN(C3:V4)),TRUE),"")
Alver
