Finding Matching Cells Across Columns and then Sorting

ExitToDOS

New Member
Joined
Sep 26, 2006
Messages
5
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.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

markyc

Board Regular
Joined
Aug 23, 2006
Messages
149
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

New Member
Joined
Sep 26, 2006
Messages
5
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Chris

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

ExitToDOS

New Member
Joined
Sep 26, 2006
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
This is limited to each item only has max of 3 dups
try
Code:
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

New Member
Joined
Sep 26, 2006
Messages
5

ADVERTISEMENT

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

markyc

Board Regular
Joined
Aug 23, 2006
Messages
149
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

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
hi jindon.

i checked out your code, but didn't get expected results. see html maker below:
Book1
ABCDEFG
1adbaaa
2bacbb
3cacc
4ddd
5
6EntereddataA1->C4MacroreturneddataE1->G4
Sheet1


any thoughts?
ben.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
ben

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

Wasn't it what the OP desired?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,861
Messages
5,544,721
Members
410,630
Latest member
JFORTH97
Top