Get duplicates from two columns

telebajsk 0
Viewed 37

I'm having trouble filtering an excel table. M, it is a set of two rows from two tables, where it is necessary to find duplicates.

2 rows with duplicates
2 rows with duplicates

Some idents are repeated, they are present both in the current and previous months. In the example below, with the help with this function =IFERROR(MATCH(A2;B:B;0); "NO"), I obtained information about which data from last month is repeated in the current month and exactly in which row it is located. The code for determining whether it is repeated is as follows =COUNTIFS($A$2:$B$13;A2)>1

duplicates and if repeated
duplicates and if repeated

I would like to retrieve only duplicates from the list, I tried the code =IFERROR(INDEX(A:A;SMALL(IF(NOT(D$2:D$104=TRUE);ROW(B2)-ROW(INDEX(B2;1;1))+1);ROW(G:G)));" ERROR")to get the ones that are repeat and skip those ones that arent, but the result is not as desired. In line G, you can see an example of how Excel gives me data regarding the entered function. In cell H, it is shown how I would like a new row to be created with only duplicates.

Current vs. desired display
Current vs. desired display

In this example, the columns are a bit small, but in reality there could be at least a thousand rows, so I would need help filtering those.

formula duplicates countif filter excel
1 Answers

You implied these columns were present in two different tables. So I used Tables with structured references. You can convert to normal addressing if you require that instead.

If you have Windows Excel 2021 or later, you can use:

=FILTERXML("<t><s>" &TEXTJOIN("</s><s>",,UNIQUE(LastMonth[Last month marks],FALSE,TRUE),UNIQUE(CurrentMonth[Current Month],FALSE,TRUE))& "</s></t>","//s[following::*=.]")
  • Create a list of distinct items for each row
  • Create an XML by concatenating the items into an array using Textjoin
  • Extract only those items that are followed by an identical item

enter image description here

With your earlier version of Excel, again, I would still use Tables and structured references but I would also use a Helper Column

D2: =IFERROR(MATCH(lastMonth[@[last month]],currentMonth[current month],0),"NO")  *and fill down*
E2: =IFERROR(INDEX(currentMonth[current month], AGGREGATE(15,6,[Duplicates in Which Row],ROWS($1:1))),"")

enter image description here

Ron Rosenfeld0
Related