How to exctract duplicate values in multiple columns

[mehmetics]

I have a table which has data in its first 6 columns.
How can ı extract the duplicate entries in the next column.
Please see the attached sample file.
thank you for your help.

[PlusX.me]

Please Login or Register  to view this content.

[mehmetics]

thank you for your reply. This formula gives the number of duplication. I want the value itself.

[PlusX.me]

How about:

Please Login or Register  to view this content.

[mehmetics]

No. it gives the same values in column F (data 6) not the duplicates.

[PlusX.me]

No idea what you are looking for

[mehmetics]

sonuçta formül istenileni vermiyor.

[JohnTopley]

Haven't you listed the duplicates in G onwards????

[mehmetics]

yes. But if the columns A to F are full of data the formula does not work. it works for only one column (column F).

[mehmetics]

Any help please.

[PlusX.me]

Do you want this ?
dublicates here 2 times duplicates 3 times duplicates


400 36
100 88
200 12
300 28
13
87
ee.PNG

[mehmetics]

Not exactly.. İf an an entry duplicates (any number of times) in the range A2:F50 that value should be shown in column in G. İf it duplicates 2 times only we can see it in column H, ...

[Haseeb Avarakkan]

Hello mehmetics,

Try these Array Formulas** then copy down.

G2,

=IFERROR(SMALL(IF(COUNTIF(A$2:F$21,A$2:F$21)>1,IF(COUNTIF(G$1:G1,A$2:F$21)=0,A$2:F$21)),ROWS(G$2:G2)),"")

H2,

=IFERROR(SMALL(IF(COUNTIF($A$2:$F$21,$A$2:$F$21)=2,IF(COUNTIF(H$1:H1,$A$2:$F$21)=0,$A$2:$F$21)),ROWS(G$2:G2)),"")

Change red highlighted to 3, 4, 5 etc.. in other columns.

**Must be entered with CTR+SHIFT+ENTER

[mehmetics]

thank you very much. this is what I want, Haseeb A