Thread: problem:Sort

Hi,

I have a problem and was hoping you could help me with it. I have two columns, I want to keep column 1 static and find any values in Column 2 and place them in the same order as found in Column 1. I am attaching a spreadsheet to help explain my question. Column A and B are the two columns and Column D is the desired answer.

Thanks

Nimesh

You have values in column-B that do not appear in column-A so you cannot, as I see it, get the results you show in column-D.

Try this formula in cell E2 and copied down.

=INDEX($B$2:B6,MATCH(A2,$B$2:B6,0))

That works partially, it shows the values from Column A in the right place but shows #N/A when it does not find values from A in B. hope I am making sense.

Thanks for the reply

That works partially, it shows the values from Column A in the right place but shows #N/A when it does not find values from A in B.

Exactly the point I made in previous post - column-B contains values not found in column-A

If you want to show a blank instead of the #N/A error, use this formula.

=IF(ISNA(INDEX($B$2:B6,MATCH(A2,$B$2:B6,0))),"",INDEX($B$2:B6,MATCH(A2,$B$2:B6,0)))

I am not sure if this can be done, but is it possible to put the values from Column B which were not found in column A in the blank cells ?

Thanks,

Nimesh
Palmetto is correctm, just replace "" for your needed column data

reeditred from Palmettos code

=IF(ISNA(INDEX($B$2:B6,MATCH(A2,$B$2:B6,0))),B2,INDEX($B$2:B6,MATCH(A2,$B$2:B6,0)))



Vora

Hi Nimesh,

It is impossible to do it with formulae (or may be extremely difficult).

However, it can be very easily done with the help of a simple macro. I have written one. The logic is to add all the values of Column B not found in Column A into an array and then use the array values to fill the blank rows in the resulting column. The result column in my code is E. The code used is:

Code:

Dim i As Long, j As Long
Dim flag As Boolean
Dim notfound As New Collection

For i = 2 To Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
    flag = False
    For j = 2 To Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
        If Sheets(1).Cells(i, 2).Value = Sheets(1).Cells(j, 1).Value Then
            flag = True
            Exit For
        End If
    Next
    If flag = True Then
        Sheets(1).Cells(j, 5).Value = Sheets(1).Cells(i, 2).Value
    Else
        notfound.Add (Sheets(1).Cells(i, 2).Value)
    End If
Next

j = 1
For i = 2 To Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
    If Sheets(1).Cells(i, 5).Value = "" Then
        Sheets(1).Cells(i, 5).Value = notfound.Item(j)
        j = j + 1
    End If
Next

However, I am attaching the Excel file for your reference. Be sure to Enable Macros when you open the file & click on 'Run Macro'.

--Karan--