How to sort multiple columns in Excel by matching value

Question

I am doing data clean up. I collected data from multiple excel spreadsheets. I am trying to achieve sorting on the basis on exact match i.e.

**Original dataset**

FirstName_Store1  FirstName_Store2  FirstName_Store3
Aat               Cat               Dat
Cat               Bat               Zat
Cot
Eat

**Result intended**

FirstName_Store1  FirstName_Store2  FirstName_Store3
Aat                              
                  Bat              
Cat               Cat               
Cot
                                    Dat
Eat                              
                                    Zat

The reason I intended result because I want to find out which record to delete. I want to delete the value from Store1 which is not in store2 and store3.

Original dataset contains about 4000 records. Normal alphabet sorting doesn't display exact column value from Store1 side by side with Store2 and Store3.

Things I tried so far: Match function Match (Store1,Store2:Store3,0) and the result was an error. I also tried Vlookup, however, Vlookup doesn't do the sorting.

@SolarMike I tried match function and alphabetic sorting but none of them gets me the intended result. There might be some combination of formulas I need to use to get this result. — gaten, Jul 14 '18 at 07:10
You need to show what you have tried - this is not a "you request - we do on command" site, what happens here is we help sort your errors with what you have achieved... — Solar Mike, Jul 14 '18 at 07:20
I edited my question and explained why any built-in formula is not working. In my case, it is not a programming question where I have code to review. The questions is around what to use. — gaten, Jul 14 '18 at 07:36
Well, first point is match does not work in the way you used it, so go back and look at its arguments then consider the use of an if() with and() to combine 3 match statements. — Solar Mike, Jul 14 '18 at 07:44
I looked into the match functions and if function and, and function. Can't figure out for the last 2 hours how they will work together. could you please help how to use them to get the desired result. — gaten, Jul 14 '18 at 11:24
If you want to find out if a value in the first store is in either the second or the third store then use countif. — , Jul 14 '18 at 13:14

score 1 · Accepted Answer · answered Jul 14 '18 at 19:42

Personally, I think this is an XY Problem where you are trying to solve a problem that has little if anything to do with your actual issue.

_{The reason I intended result because I want to find out which record to delete. I want to delete the value from Store1 which is not in store2 and store3.}

This situation should be solved easily by looping through each cell in the FirstName_Store1 column from bottom to top and performing an application.countif on the remaining columns.

With that said, here's one solution to the question you actually posed.

Sample data before sortAndSift sub procedure

sortAndSift code for public module code sheet

Option Explicit

Sub sortAndSift()
    Dim i As Long, j As Long, m As Variant, n As Variant

    With Worksheets("sheet6")
        With .Cells(1, 1).CurrentRegion
            With .Cells.Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                'store the original values
                m = .Value2
            End With

            'put all values into column A
            For i = 2 To .Columns.Count
                With .Range(.Cells(2, i), .Cells(Rows.Count, i).End(xlUp))
                    .Parent.Cells(.Parent.Rows.Count, 1).End(xlUp).Resize(.Rows.Count, .Columns.Count).Offset(1, 0) = .Value
                End With
            Next i
        End With

        'reassert CurrentRegion since it probably changed
        With .Cells(1, 1).CurrentRegion
            With .Columns(1).Cells
                'remove duplicates from column A
                .RemoveDuplicates Columns:=1, Header:=xlYes

                'sort column A
                .Sort Key1:=.Columns(1), Order1:=xlAscending, _
                      Orientation:=xlTopToBottom, Header:=xlYes
            End With

            'put a copy of the expanded, de-duplicated and sorted column A
            ' in all other columns and make a copy of the values
            With .Cells.Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                .FillRight
                n = .Value2
            End With

            'remove values from each 'column' in the array that were not in the original
            For i = LBound(n, 1) To UBound(n, 1)
                For j = LBound(n, 2) To UBound(n, 2)
                    If IsError(Application.Match(n(i, j), Application.Index(m, 0, j), 0)) Then
                        n(i, j) = vbNullString
                    End If
                Next j
            Next i

        End With

        'put values back on the worksheet
        .Cells(2, 1).Resize(UBound(n, 1), UBound(n, 2)) = n
    End With

End Sub

Sample data after sortAndSift sub procedure

pnuts · Answer 2 · 2018-07-14T19:59:46.203

0

A unique list can easily be created by putting your data into a PivotTable (how though seems off topic for SO ref). The results may be Copy/Paste Special/Values, Remove Duplicates applied, sorted alphabetically and then placed in ColumnE, for example.

Then in F2 copied across and down to H8:

=IFERROR(IF(MATCH($E2,A:A,0),$E2),"")

Copy headings across.

Assumes Aat is in A2.

edited Jul 14 '18 at 19:59

answered Jul 14 '18 at 19:54

pnuts

54,806
9
74
122

How to sort multiple columns in Excel by matching value

2 Answers2