Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for sorting data
Hi there
any suggestion for how is possible to sort a data in a column base on the first two digits of the data in each cell. suppose that i have colume in which some names are randomly put, now i would like to make new column in different sheets and sort this random names in them in accordance with their two begining digits. so the code on each sheet should read whole the colume in the first sheet and see if any cell begins with these two spesific digits then copy it in the new colume of the new sheet whithout any empty cells in between. should not be that difficult but i am not very much experienced to write it done in excel, any suggestion is needed and very much welcomed. Thanks a lot MM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for sorting data
Must you use a macro? If not, why not insert a blank column and use the LEFT
function. Suppose the data is in column A, and the helper column is column B. Then you would write: =LEFT(A1,2) in B1 and copy that down to your last row. Then simply sort the data set by column B. HTH, Matthew Pfluger "confused guy and desperately in need !" wrote: Hi there any suggestion for how is possible to sort a data in a column base on the first two digits of the data in each cell. suppose that i have colume in which some names are randomly put, now i would like to make new column in different sheets and sort this random names in them in accordance with their two begining digits. so the code on each sheet should read whole the colume in the first sheet and see if any cell begins with these two spesific digits then copy it in the new colume of the new sheet whithout any empty cells in between. should not be that difficult but i am not very much experienced to write it done in excel, any suggestion is needed and very much welcomed. Thanks a lot MM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for sorting data
try and record macro on this procedure if in case there is some error
in the code post it and some one call solve it for you....its not a dificult marco to right. Use the site to learn basic of macros. http://www.angelfire.com/biz7/julian...ans_macros.htm |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for sorting data
thanks dear Mattew for your reply,
actually i have used it but that way you get empty cells in between , if the word starts say with "co" it should be in one column but for others that cell will return empty , right? that is exactly what i should not do, so if the words begining with "co" on main column are in rows 1, 5, 12, 17 , 23 , they should be in my new sorted column just in cells 1,2,3,4,5 , i think i need macro really dont you think so ? Best wishes, MM "Matthew Pfluger" wrote: Must you use a macro? If not, why not insert a blank column and use the LEFT function. Suppose the data is in column A, and the helper column is column B. Then you would write: =LEFT(A1,2) in B1 and copy that down to your last row. Then simply sort the data set by column B. HTH, Matthew Pfluger "confused guy and desperately in need !" wrote: Hi there any suggestion for how is possible to sort a data in a column base on the first two digits of the data in each cell. suppose that i have colume in which some names are randomly put, now i would like to make new column in different sheets and sort this random names in them in accordance with their two begining digits. so the code on each sheet should read whole the colume in the first sheet and see if any cell begins with these two spesific digits then copy it in the new colume of the new sheet whithout any empty cells in between. should not be that difficult but i am not very much experienced to write it done in excel, any suggestion is needed and very much welcomed. Thanks a lot MM |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for sorting data
Thanks Heera,
actually i have worked with VB6 before but now just trying to catch up with excel's methods a bit. thanks for the site address MM "Heera" wrote: try and record macro on this procedure if in case there is some error in the code post it and some one call solve it for you....its not a dificult marco to right. Use the site to learn basic of macros. http://www.angelfire.com/biz7/julian...ans_macros.htm |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for sorting data
I'm having trouble following here, but from what I can tell, your dataset is
not very consistent. I would remove all spaces or blank rows from a dataset before trying to sort based on the first two letters. Another thought - if you only sort by the first two letters, what happens when you need to sort something like "Tim" and "Timothy"? How do you determine which row comes first? If this is really something you need to do often, please elaborate on your dataset. If not, stick to the manual method. Sometimes manual is better and faster than programming. Matthew Pfluger "confused guy and desperately in need !" wrote: thanks dear Mattew for your reply, actually i have used it but that way you get empty cells in between , if the word starts say with "co" it should be in one column but for others that cell will return empty , right? that is exactly what i should not do, so if the words begining with "co" on main column are in rows 1, 5, 12, 17 , 23 , they should be in my new sorted column just in cells 1,2,3,4,5 , i think i need macro really dont you think so ? Best wishes, MM "Matthew Pfluger" wrote: Must you use a macro? If not, why not insert a blank column and use the LEFT function. Suppose the data is in column A, and the helper column is column B. Then you would write: =LEFT(A1,2) in B1 and copy that down to your last row. Then simply sort the data set by column B. HTH, Matthew Pfluger "confused guy and desperately in need !" wrote: Hi there any suggestion for how is possible to sort a data in a column base on the first two digits of the data in each cell. suppose that i have colume in which some names are randomly put, now i would like to make new column in different sheets and sort this random names in them in accordance with their two begining digits. so the code on each sheet should read whole the colume in the first sheet and see if any cell begins with these two spesific digits then copy it in the new colume of the new sheet whithout any empty cells in between. should not be that difficult but i am not very much experienced to write it done in excel, any suggestion is needed and very much welcomed. Thanks a lot MM |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for sorting data
Dear Mattew,
sorry for late answering, i was busy today. in response to your question i would say that fortunately the data begins in just Two letters and some numbers following the letters, so basicly we have just few begining options, to clarify let's say all the cases will starts with either "co" or "WF" or "Fr" ofcourse the following numbers would be unique. now this data come in long column , 600 or more , the problem is that this input data changes time to time by operator, right , so i dont want to sort them each time manualy but would like to have a code in which when the data changes, the code would resort it immediately. what should happen is that if any data starts with "co" they should be copy in other sheet and ofcourse folowing each other(i.e , without any blank space) again to clarify lets say the cell no1. is "co125" right, now the other cells have value satring with other letters, just cell number 700 is"co126" now i would like to see on other sheet these data are in cell no1 and no2 , hope i could explain in the way you could get the whole picture there. if you have any suggestion, not the complete solution, just a hint, you may kindly let me know Thanks so much MM "Matthew Pfluger" wrote: I'm having trouble following here, but from what I can tell, your dataset is not very consistent. I would remove all spaces or blank rows from a dataset before trying to sort based on the first two letters. Another thought - if you only sort by the first two letters, what happens when you need to sort something like "Tim" and "Timothy"? How do you determine which row comes first? If this is really something you need to do often, please elaborate on your dataset. If not, stick to the manual method. Sometimes manual is better and faster than programming. Matthew Pfluger "confused guy and desperately in need !" wrote: thanks dear Mattew for your reply, actually i have used it but that way you get empty cells in between , if the word starts say with "co" it should be in one column but for others that cell will return empty , right? that is exactly what i should not do, so if the words begining with "co" on main column are in rows 1, 5, 12, 17 , 23 , they should be in my new sorted column just in cells 1,2,3,4,5 , i think i need macro really dont you think so ? Best wishes, MM "Matthew Pfluger" wrote: Must you use a macro? If not, why not insert a blank column and use the LEFT function. Suppose the data is in column A, and the helper column is column B. Then you would write: =LEFT(A1,2) in B1 and copy that down to your last row. Then simply sort the data set by column B. HTH, Matthew Pfluger "confused guy and desperately in need !" wrote: Hi there any suggestion for how is possible to sort a data in a column base on the first two digits of the data in each cell. suppose that i have colume in which some names are randomly put, now i would like to make new column in different sheets and sort this random names in them in accordance with their two begining digits. so the code on each sheet should read whole the colume in the first sheet and see if any cell begins with these two spesific digits then copy it in the new colume of the new sheet whithout any empty cells in between. should not be that difficult but i am not very much experienced to write it done in excel, any suggestion is needed and very much welcomed. Thanks a lot MM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code for sorting | Excel Discussion (Misc queries) | |||
Sorting in code | Excel Discussion (Misc queries) | |||
Sorting by code | Excel Discussion (Misc queries) | |||
Help sorting out my code PLEASE | Excel Programming | |||
Sorting with code | Excel Programming |