Sorting and inserting problem

[Vlad]

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.

 

[David McRitchie]

Hi Vlad,
C1: =MIN(A1,B1) doubleclick on fill handle to copy down
Sort on C, A, B all ascending

 

[Guest]

I originally thought about the min function but all of the entries have a 1
in them so the sort does not work...

 

[David McRitchie]

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

 

[Justyaz]

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.

 

[Justyaz]

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

 

[Vlad]

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.

 

[Myrna Larson]

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.