Sorting the duplicate values:

[Analytic]

Hi,

There are some values in coloumn A and coloumn B . The solution is just to simply get the duplicate values from coloumn A and B , and then display those duplicate values in Coloumn C AND Coloumn D.

EXAMPLE:

A B C D
1 3 1 3
2 4 5 6
3 1
2 4
5 6
6 5
2 9
1 4
1 4

In the above example there is 1 in C1 and 3 in D1 because 1,3 and 3,1 are duplicates in coloumn A and B combined, also likewise there is 5 in C2 and 6 in D2 because there is 5 in A5 and alos 5 in B6 and 6 in B5 and 6 in A 6. Please note the desired solution does not includes 2,4 and 1,4 in Coloumn C and D because they are duplicates in the same row.

Thanks

 

[Anthony47]

Wouldn't be sufficient to mark those rows that are symmetrical?
For example, in D2:

=IFERROR(MATCH(B2&A2,$A$2:$A$20&$B$2:$B$20,0),"")

To be confirmed using the combination Contr-Shift-Enter, not Enter alone; then copy down along your list

Or let's use a User Function...
1) in a standard module of your vba project insert the following code:

Function Symmetrical(ByRef myRan As Range) As Variant
'https://www.mrexcel.com/board/threads/sorting-the-duplicate-values.1220705/
Dim oArr() As String, cCk As String, I As Long, J As Long
Dim cCnt As Long, rStr As String
'
ReDim oArr(1 To 1)
With myRan
    For I = 1 To .Rows.Count - 1
        cCk = .Cells(I, 1) & "##" & .Cells(I, 2) & "##"
        cCnt = 0: rStr = ""
        If Len(cCk) > 4 Then
            For J = I + 1 To .Rows.Count
                If .Cells(J, 2) & "##" & .Cells(J, 1) & "##" = cCk Then
                    cCnt = cCnt + 1
                    rStr = rStr & Cells(J, 1).Row & "/ "
                End If
            Next J
            If cCnt > 0 Then
                ReDim Preserve oArr(1 To UBound(oArr) + 1)
                oArr(UBound(oArr)) = .Cells(I, 1) & "; " & .Cells(I, 2)
                oArr(UBound(oArr)) = oArr(UBound(oArr)) & " - " & cCnt & " - " & rStr       'See Note ***
            End If
        End If
    Next I
    oArr(1) = UBound(oArr) - 1
End With
If Parent.Caller.Rows.Count > UBound(oArr) Then
    ReDim Preserve oArr(1 To Parent.Caller.Rows.Count)
ElseIf Parent.Caller.Rows.Count < UBound(oArr) Then
    oArr(1) = oArr(1) & "##"
End If
Symmetrical = Application.WorksheetFunction.Transpose(oArr)
End Function

Now on your sheet you may use the function Symmetrical. For example:
-select F2:F8
-set in the formula bar the following formula

=Symmetrical(A2:B20)

Confirm using the combination Contr-Shift-Enter, not Enter alone
The formula will return:
-how many "duplicates" have been found
-duplicate1 - HowManyOfThis - RowOfDuplicate1 /RowOfDuplicate2 /etc (see note ***)

F2:F8 is the area filled by the formula; if there are more duplicates than the set cells then item 1 returned (how many "duplicates" have been found) is marked by ## to mean that there is not enough cell for the results; this is shown in column H of the XL2BB minisheet. To "extend" the area of the results:
-select the current area + additional cells; press F2 (Edit formula), confirm using Contr-Shift Enter

Note ** If you don't like the additional information returned with the duplicates, then remove or "comment" the line marked See Note ***

HTH...

MULTI_C21026.xlsm
	A	B	C	D	E	F	G	H	I
1	A	B		Formula		Symmetrical		Symmetrical
2	1	3		3		3		3##
3	2	4				1; 3 - 1 - 3/		1; 3 - 1 - 3/
4	3	1		1		3; 1 - 1 - 10/		3; 1 - 1 - 10/
5	2	4				5; 6 - 1 - 6/
6	5	6		6
7	6	5		5
8	2	9
9	1	4
10	1	4
11	1	3		3
12
13
14
Foglio6

Cell Formulas
Range	Formula
H2:H4	H2	=Symmetrical(A2:B14)
F2:F8	F2	=Symmetrical(A2:B20)
D2:D11	D2	=IFERROR(MATCH(B2&A2,$A$2:$A$20&$B$2:$B$20,0),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

NB: Even formulas in column D requires Contr-Shift-Enter with Excel versions that don't support Dynamic Array