Finding Matching Cells Across Columns and then Sorting

[ExitToDOS]

Hello.

I have a worksheet on which data is listed in three columns: A, B and C. The data in each column is unique, in that a value will only appear once in that column. However, that value may also appear in one or both of the other columns.

If possible, I would like to sort the worksheet so that the rows which match across all three columns appear at the top, followed by rows which match across two of the three columns, and lastly, with the unique rows appearing at the bottom.

Here is an example of the unsorted list:

Col A | Col B | Col C
Green | Blue | Yellow
Blue | Yellow | Green
Yellow | Green | Blue
Red | Purple | White
White | Red | Brown
Orange
Pink

And here is an example of the sorted list:

Col A | Col B | Col C
Green | Green | Green
Blue | Blue | Blue
Yellow | Yellow | Yellow
Red | Red
White | White
Orange | Purple | Brown
Pink

Any ideas? Thanks in advance.

 

[markyc]

You could add a control column in D to sort on.
In D use the formula
=SUM(IF(OR(A1=B1,A1=C1,B1=C1),1,0),IF(AND(A1=B1,B1=C1),1),0)
and then sort by descending order.

 

[ExitToDOS]

Thanks for the reply, Markyc.

What I need the formula(s) to do, however, is to determine whether the values in Column A are found in either Columns B or C -- not just across a single row from A to C.

Then, when matches are found, the list would be sorted so that values that are in Columns A, B and C appear at the top, then values that are in two of the three columns, then unique values last.

I've been trying in vain to get this to work using a variety of nested functions.

Thanks again, though.

 

[jindon]

Chris

What if there are more than 3 match like 5 Greens?
Do you still want to limit the columns in 3?

 

[ExitToDOS]

Hello, Jindon. Thanks for the reply.

Each column is basically a unique list. However, a value that appears in column A may also appear in columns B or C. Furthermore, a value that appears in column B may also appear in column C.

So, what I'm trying to do is, first, determine whether the values in any column can be found in another column. Then, I want to be able to sort the list.

I'm not entirely sure this can be done -- or, at least not easily.

Thanks again!

 

[jindon]

This is limited to each item only has max of 3 dups
try

Sub test()
Dim a, dic As Object, i As Long, b(), ii As Byte, n As Long
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
a = Range("a1").CurrentRegion.Resize(,3).Value
ReDim b(1 To UBound(a,1), 1 To 3)
For i = 1 To UBound(a,1)
    For ii = 1 To 3
        If Not IsEmpty(a(i,ii)) Then
            If Not dic.exists(a(i,ii)) Then
                n = n + 1
                x = 1
                dic.add a(i,ii), n
            Else
                If i = dic(a(i,ii)) Then
                    x = IIf(b(dic(a(i,ii),2)="",2,3)
                Else
                    x = IIf(b(dic(a(i,ii)),3)="",3,2)
                End If
                b(dic(a(i,ii)),x) = a(i,ii)
            End If
        End If
     End If
Next
With Range("e1")
   .CurrentRegion.ClearContents
   .Resize(n,3).Value = b
End With
End Sub

Edit: code 11:56

 

[ExitToDOS]

Thanks, Jindon. I'll give your code a try.

 

[markyc]

What is the purpose of sorting it as such.
If you want to just summarize then use a pivot table by putting all the data in one column with the second column indicating the source. Then your pivot table can just count the number in each column and be sorted in descending order as such.
A B C Grand Total
Green 1 1 1 3
Yellow 1 1 2
Blue 1 1

 

[sweater_vests_rock]

hi jindon.

i checked out your code, but didn't get expected results. see html maker below:

Book1
	A	B	C	D	E	F	G
1	a	d	b		a	a	a
2	b	a	c		b	b
3	c		a		c	c
4	d				d	d
5
6	EntereddataA1->C4				MacroreturneddataE1->G4
Sheet1

any thoughts?
ben.

 

[jindon]

ben

Thanks for testing, but the code meant to do that result.

Wasn't it what the OP desired?