Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort
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 --------------- Jacob Skaria "osaka78" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort
The values in Column C are repeating any solution for that
"Bernd P" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort
Hello,
So you do have repeating values in column C? Regards, Bernd |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort
yes any suggestion
"Bernd P" wrote: Hello, So you do have repeating values in column C? Regards, Bernd |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating workbook with an alpha sort sheet and a numeric sort shee | Excel Discussion (Misc queries) | |||
sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
Ascending Sort formula, change to neg #: descending sort.. | Excel Discussion (Misc queries) | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
Excel Sort function should not sort the cell formatting! | Excel Worksheet Functions |