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:

Please Login or Register  to view this content.

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