Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting and inserting problem
Hi All!
I have a set of data which i need to sort in a kind of complicated way. The source data is in 2 columns and goes as follows: 1 2 1 2 1 3 1 3 1 4 1 5 1 5 2 1 2 1 3 1 4 1 4 1 4 1 etc.. what i need to get is: 1 2 1 2 2 1 2 1 1 3 1 3 3 1 1 4 4 1 4 1 4 1 1 5 etc.. In other words after each group of similiar data (i.e. 1 2) i should put a group of inverse data (i.e. 2 1) . Have no idea where to start. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting and inserting problem
Hi Vlad,
C1: =MIN(A1,B1) doubleclick on fill handle to copy down Sort on C, A, B all ascending --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Vlad" wrote in message om... Hi All! I have a set of data which i need to sort in a kind of complicated way. The source data is in 2 columns and goes as follows: 1 2 1 2 1 3 1 3 1 4 1 5 1 5 2 1 2 1 3 1 4 1 4 1 4 1 etc.. what i need to get is: 1 2 1 2 2 1 2 1 1 3 1 3 3 1 1 4 4 1 4 1 4 1 1 5 etc.. In other words after each group of similiar data (i.e. 1 2) i should put a group of inverse data (i.e. 2 1) . Have no idea where to start. Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting and inserting problem
I originally thought about the min function but all of the entries have a 1
in them so the sort does not work... "Vlad" wrote: Hi All! I have a set of data which i need to sort in a kind of complicated way. The source data is in 2 columns and goes as follows: 1 2 1 2 1 3 1 3 1 4 1 5 1 5 2 1 2 1 3 1 4 1 4 1 4 1 etc.. what i need to get is: 1 2 1 2 2 1 2 1 1 3 1 3 3 1 1 4 4 1 4 1 4 1 1 5 etc.. In other words after each group of similiar data (i.e. 1 2) i should put a group of inverse data (i.e. 2 1) . Have no idea where to start. Any help would be greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting and inserting problem
What criteria do you use to know whether you have a
correct solution solution or not ? The following will produce the order you indicate that you want. C1: =MIN(A1,B1) D1: =MAX(A1,B1) fill down by double click on the fill handle Sort C ascending, D ascending, A ascending It would also work same if D1: =A1+B1 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Jim Thomlinson" wrote in message ... I originally thought about the min function but all of the entries have a 1 in them so the sort does not work... "Vlad" wrote: Hi All! I have a set of data which i need to sort in a kind of complicated way. The source data is in 2 columns and goes as follows: 1 2 1 2 1 3 1 3 1 4 1 5 1 5 2 1 2 1 3 1 4 1 4 1 4 1 etc.. what i need to get is: 1 2 1 2 2 1 2 1 1 3 1 3 3 1 1 4 4 1 4 1 4 1 1 5 etc.. In other words after each group of similiar data (i.e. 1 2) i should put a group of inverse data (i.e. 2 1) . Have no idea where to start. Any help would be greatly appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting and inserting problem
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting and inserting problem
On Thu, 26 Aug 2004 03:59:47 -0600, Justyaz
wrote: On 25 Aug 2004 10:15:43 -0700, (Vlad) wrote: There is probably a better way to write this out but I'm still a newbie. C1:=IF(INT(VALUE(CONCATENATE(A1,B1))/10)=2,VALUE(CONCATENATE(A1,B1)/10+10),(VALUE(CONCATENATE(A1,B1)))) Sort on C Ascending. Peace, Vm Yaz Providing complicated solutions to simple problems since 1997. Ignore my suggestion. It doesn't work for number 10 and above. Peace, Vm Yaz Providing complicated solutions to simple problems since 1997. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting and inserting problem
Thanks a lot everyone for their input, especially for the idea with
summation -that was simple, but really helpful. Now i have another problem. I need to insert an empty row after each group of data and calculate a sum for this group. Can anyone suggest how can i do this in VBA, cause the the size of the data area is changing with each inserted row, so i can't use a simple loop here.. TIA. Justyaz wrote in message . .. On Thu, 26 Aug 2004 03:59:47 -0600, Justyaz wrote: On 25 Aug 2004 10:15:43 -0700, (Vlad) wrote: There is probably a better way to write this out but I'm still a newbie. C1:=IF(INT(VALUE(CONCATENATE(A1,B1))/10)=2,VALUE(CONCATENATE(A1,B1)/10+10),(VALUE(CONCATENATE(A1,B1)))) Sort on C Ascending. Peace, Vm Yaz Providing complicated solutions to simple problems since 1997. Ignore my suggestion. It doesn't work for number 10 and above. Peace, Vm Yaz Providing complicated solutions to simple problems since 1997. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting dates and inserting rows | Excel Worksheet Functions | |||
Automatic Sorting and Inserting Rows | Excel Discussion (Misc queries) | |||
Column Inserting and sorting problem | Excel Discussion (Misc queries) | |||
Problem inserting a new worksheet | Excel Discussion (Misc queries) | |||
Inserting pictures then sorting | Excel Discussion (Misc queries) |