PDA

View Full Version : Arrange the values as per pre-defined orders



sindhuja
08-24-2009, 08:42 AM
Hi All,

I have set of values which are pre-defined.
Want another set of values to be arranged as per the predefined values.

For example..

Predefined values in column L:
Sample
Query
Dominant
Apple
Cannot
Laminate
Diminish


Below are the values in column D, I want to arrange per the above arrangement

Laminate
Sample
Query

The expected result should be

Sample
Query
Laminate

Can this be done in excel..

-Sindhuja

mdmackillop
08-24-2009, 08:52 AM
Put this in the adjoining cell, copy down, then sort both columns on the result
=MATCH(D1,L:L,0)

Bob Phillips
08-24-2009, 11:18 AM
Select column L and goto Tools>Options>Custom Lists.

Click Import, that will build a list with those values.

Select Column D, and goto Data>Sort.

Click Options, and pick your list from the dropdown.

sindhuja
08-24-2009, 11:48 AM
Hi Md,

Thanks for the help !!!

I used the match function in the column M and found only the numbers displayed.
To be clear I want the below values

Laminate
Sample
Query

To be sorted as below in column D as predefined set is arranged.

Sample
Query
Laminate

Thanks in advance,
Sindhuja

sindhuja
08-24-2009, 11:58 AM
Hi,

I tried the tools-à Options method..
It works fine for me manually..

Can this be done automatically using coding..
Also I have a query I have imported the list in my system, if others needs to do use my coding they need to import the list in their system.
This is part of the activity I used in my automation…

Suggest me in doing further proceedings..

-sindhuja

mdmackillop
08-24-2009, 12:02 PM
The formula goes next to your unsorted items. However I would go with XLD's solution which uses functions designed for this purpose.

sindhuja
08-24-2009, 12:48 PM
Even I am very much comfortable with xld solution.
I have to do series of activities (consider 5 activities). I wrote macro for 3 activities out of 5 and the fourth activity is the one am looking for.
If this needs to be done manually then the activity five also needs to be done manually where as I have macro for the 5th activity.

That’s the reason am looking for a macro to do this activity..

-Sindhuja