custom sort according to header in each sheet

[raj soni]

hello frds ,

i have four sheets ,

all four sheets have headers in a1 , b1 , c1 , d1

all four headers has custom sort order , which is define in sheet(ORDER)

appearance of this headers in all four sheets are in differently
sheet1 : PLANETS MONTHS DAYS COLOR
sheet2 : COLOR DAYS MONTHS PLANETS
sheet3 : DAYS COLOR PLANETS MONTHS
sheet4 : MONTHS PLANETS COLOR DAYS

all four sheets needed to be sorted out in order mention in sheet(order)

just for example , i have attach small sample file .

i would like to custom sort this sheets with vba , rather manually , coz we have ard 75 sheets like this

[MickG]

Try this:-

Please Login or Register  to view this content.

Regards Mick

[raj soni]

wonderful sir , works like magic ,

Thanks alot much for taking the time to help with my project.

[raj soni]

sir ,

only small question related to same vba ,
i just added 5 th column as price ,
but i dont want to sort that ,
would like to expand selection after sorting those 4 columns .

[MickG]

Please explain, what you mean, I don't understand !!!

would like to expand selection after sorting those 4 columns .

[raj soni]

4 columns we are custom sorting .,

5th column price , we are expanding the sortment ,

just an example of i am trying to say

EXPAND SELECTION.JPG

[MickG]

I still don't understand in terms of the Sorting code I sent you.
You will have to show a relevant before and after scenario based on your original data.
To expand the selection means to have the selected cells sorted as part of the original Sort, I don't see how this works with you original data.
Or perhaps you mean you want the code to add the Price column, You need to explain more!!!!

[raj soni]

SHEET2.jpg

this is how i am doing it manually ,
selecting 5 columns to sort ,
but only giving 4 columns for custom sort

also i have attach excel .
for example if you search any number on 5th column in all 4 sheets , its corresponding values are same ,

[raj soni]

please check SHEET2.jpg

[MickG]

Are you saying that you would like the number in column "E" that relates to the Unique row of data in the same row, columns "A to D", to be placed against the same Combinations after the sort.

[raj soni]

yes sir

exactly ,

[MickG]

Try this:-
Its a bit slow approx 30Secs, if too slow, I'll rewrite using arrays ???

Please Login or Register  to view this content.

Regards Mick

[raj soni]

sir , thanks for trying , but this one is giving totally different result .
its copying down data

[MickG]

The code that you said was OK is basically the same as the code you say is not OK. The results from both codes, to me seem the same.
To clarify what you require Please supply an example of you Basic data, and an Example of what you expect to see when the code has run.
The sample need to be large enough to ensure there is no ambiguity.

[raj soni]

hello sir , sorry for trouble.
don't know why its not working now ,
i have attach before and after sample file .

1 ) sheet "order" has values how custom sort order should appear .
2 ) sheet 1 to 4 has 5 different headers in a1:e1
3 ) headers in this 4 sheets are placed differently .
3 ) in every 4 sheets , we have to
custom sort
column A first according to its header A1
then column b according to its header B1,
then column c according to its header C1
then column d according to its header D1
then column e according to its header E1
according to order we have referenced in ORDER sheet .
4 ) most interesting part is here , column f has id number ....here we need to expand selection , no need to sort .
for example i have mark id : 91 .....in all 4 sheets .

if you check values corresponding to 91 ....all are same but in differently place .
Earth April Tuesday Red Cucumber 91...............SHEET1
Red Tuesday Earth April Cucumber 91...............SHEET2
Tuesday Earth April Cucumber Red 91...............SHEET3
Cucumber Earth Red Tuesday April 91...............SHEET4

all values of combination are same , but combination are different

[raj soni]

sorry file above 1 mb , so cant upload here , so sending this two links

https://www.dropbox.com/s/c7boknn7zs...FTER.xlsx?dl=0

https://www.dropbox.com/s/m6h17d05dv...FORE.xlsx?dl=0

[MickG]

Try this:-
NB:- Your sheet lists appear to be all the combination of the data on sheet "Order", But mixed up and in various Header order.
The code now checks each line against a Data set of the appropriate combinations, Replacing the results in order on the sheet, as found , and with the appropriate number, added in column 6.
Hope this is what you want !!!!

Please Login or Register  to view this content.

Regards Mick

[raj soni]

hello sir ,
its 90 % working in right direction ,
but its leaving black cells in between .
if you check my "AFTER" sheet , there is no black cells .

i think vba calculate A1:E1 from order sheet .
but actual order should be
("A1:A10", "B1:B10", "C1:C8", "D1:D13", "E1:E7")
but i am happy with this too , thank you for you help and cooperation

[MickG]

I meant to say, in your sheet "Order" for all data columns, below the cells in each column with Data, there are Blank cells that have something in them, and the code uses the blank cells.
I don't know whats in them but I had the same trouble as you, before I found out what was causing it.
What you need to do is move the whole range to some spare place on the worksheet and then Copy and paste back each individual column of data.
Make sure you only copy the Data in each column.

[raj soni]

wow ....prefect ....excellent sir ,

million thanks for your help and guidance

and for trying again and again till you found end result ...

[MickG]

You're welcome