Extract duplicate value from table

[kparthy]

Hi,
I am new to excel formulas. I need a help.
I need to extract duplicated value from column 1 & Column 2 and write it in ascending order in Column Duplicate. One criteria to consider is if a cell in column 3 is empty then no need to consider that row.

Attached the sample file in this https://we.tl/t-hV5DSwMoAJ

Thanks

 

[sandy666]

Try PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"COLUMN 1", Int64.Type}, {"COLUMN 2", Int64.Type}, {"COLUMN 3", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([COLUMN 3] <> null)),
    #"Removed Columns" = Table.ToColumns(Table.RemoveColumns(#"Filtered Rows",{"COLUMN 3"})),
    #"Converted to Table" = Table.FromList(#"Removed Columns", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Kept Duplicates" = let columnNames = {"Column1"}, addCount = Table.Group(#"Expanded Column1", columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(#"Expanded Column1", columnNames, removeCount, columnNames, JoinKind.Inner),
    #"Sorted Rows" = Table.Sort(#"Kept Duplicates",{{"Column1", Order.Ascending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows")
in
    #"Removed Duplicates"[/SIZE]

example file

 

[kparthy]

Thanks for your reply. I don't have this power query.
Is there any formulas in normal excel to meet this requirement.

 

[sandy666]

PowerQuery free add-in from MS site for Excel 2010/2013
PowerQuery (Get&Transform) built-in - Excel 2016 and higher
---
I don't know formula