Sort

O

osaka78

I have the following Data
A B C D
1 X1 10 X2 2
2 X1 15 X3 5
3 X2 5
4 X2 6
5 X3 11
6 X3 17
i need to sort them to look like
A B C D
1 X1 10
2 X1 15
3 X2 5 X2 2
4 X2 6
5 X3 11 X3 5
6 X3 17

My file contain more then 10,000 line
 
J

Jacob Skaria

I think you have misunderstood the SORT functionality in Excel. If we include
all 4 columns and do a SORT; Excel retains the data in a row. For example
your Row 1 is X1-10-X2-2 . After the SORT suppose this moves to the 5th Row
still the data in the 5th row will be X1-10-X2-2.

To understand SORT please refer
http://www.contextures.com/xlSort01.html

Now for your query; could you please let us know what you are looking for
once you match the data in col C (eg: X2) and place it to the side of the
first instance of X2 in colA..


If this post helps click Yes
 
B

Bernd P

Hello,

If your values in column C are non-repeating and if they all exist in
column A:

Enter into E1:
=IF(C1<>"",MATCH(C1,$A$1:$A$20000,0),"")

Enter into F1:
=IF(ISERROR(MATCH(ROW(),$E$1:$E$20000,0)),"",INDEX(C$1:C$20000,MATCH
(ROW(),$E$1:$E$20000,0)))

Copy F1 to G1.

Then copy E1:G1 down as far as necessary.

After recalculation you might want to copy values in F:G to C:D.

Regards,
Bernd
 
B

Bernd P

Hello,

Same as before but enter into E2:
=IF(C2<>"",IF(ISERROR(LOOKUP(2,1/(C$1:C1=C2),E$1:E1)),MATCH(C2,$A$1:$A
$20000,0),LOOKUP(2,1/(C$1:C1=C2),E$1:E1)+MATCH(C2,INDEX($A
$1:$A19997,1+LOOKUP(2,1/(C$1:C1=C2),E$1:E1)):$A$20000,0)),"")
and copy down.

E1 stays as I mentioned before!

Regards,
Bernd
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top