Finding Matching Cells Across Columns and then Sorting

ExitToDOS

New Member
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

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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.

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.

Chris

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

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!

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
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

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

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

hi jindon.

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

any thoughts?
ben.

ben

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

Wasn't it what the OP desired?

Replies
2
Views
172
Replies
4
Views
243
Replies
7
Views
210
Replies
3
Views
413
Replies
1
Views
231

1,219,819
Messages
6,150,404
Members
450,960
Latest member
GB2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back