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.

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.

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.

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.

Sorting and inserting problem
 

On 25 Aug 2004 10:15:43 -0700, (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.

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.

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.

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.

Sorting and inserting problem
 

The solution to the problem of the data area changing after a row is inserted
is to process the rows from the bottom up, not from the top down.

On 30 Aug 2004 23:43:39 -0700, (Vlad) wrote:

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.