Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enhance Sort Results
Could you help me with tis matter please?
I have these values that came from a text file: Found 10 20 4 12 8 However, I want to know how can I sort them by leaving the row blank where that number does not exist?. The numbers are from 1 to 20.. Basically, I want to have a twenty rows showing just the numbers found and leave the row empty for the rest. Thnaks in advance. Maperalia. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enhance Sort Results
One crack at deciphering this ..
Assuming source data in A2 down, with data being all text numbers (or a mix of text and real numbers) Place in B2, then array-enter* the formula by pressing CTRL+SHIFT+ENTER, instead of just pressing ENTER: =IF(ISNUMBER(MATCH(ROW(A1),A:A,0)),ROW(A1),"") Copy B2 down to B21. The source data in A2:A21 will be "sorted" neatly into B2:B21 in ascending order exactly where they should appear, with the rest of the cells appearing blank, as required. *Done correctly, Excel will wrap curly braces around the formula: { ... } You should see this happening within the formula bar, as a visual check that the formula is correctly array-entered. If these curly braces don't appear, click inside the formula bar and try it again (CTRL+SHIFT+ENTER). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "maperalia" wrote: Could you help me with tis matter please? I have these values that came from a text file: Found 10 20 4 12 8 However, I want to know how can I sort them by leaving the row blank where that number does not exist?. The numbers are from 1 to 20.. Basically, I want to have a twenty rows showing just the numbers found and leave the row empty for the rest. Thnaks in advance. Maperalia. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enhance Sort Results
Sorry, pasted the wrong formula earlier ..
Line: =IF(ISNUMBER(MATCH(ROW(A1),A:A,0)),ROW(A1),"") should read as: =IF(ISNUMBER(MATCH(ROW(A1),$A$2:$A$21+0,0)),ROW(A1 ),"") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enhance Sort Results
Max;
Thank you ver much. It is working perfectly!!!!!. However, I could not make it work with the sample shown bellow. My base data is: 1 1a 2 3* 4 5b 6 7 8 9 10 The text data to be sorted is: 10 1a 4 5b 3* I have formatted them as a number but still do not work.. Do you think is possible to work with this? Thanks in advance. Maperalia "maperalia" wrote: Could you help me with tis matter please? I have these values that came from a text file: Found 10 20 4 12 8 However, I want to know how can I sort them by leaving the row blank where that number does not exist?. The numbers are from 1 to 20.. Basically, I want to have a twenty rows showing just the numbers found and leave the row empty for the rest. Thnaks in advance. Maperalia. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enhance Sort Results
... ugh, its considerably tougher now <g,
but here's one way which seems to work ok .. Assuming source data within A2:A21 as before First we'll use a UDF StripTxt by Niek Otten to strip out all the non numerics Steps: Press Alt+F11 to go to VBE Click Insert Module Copy n paste the UDF StripTxt by Niek Otten below into the code window on the right (white space) Press Alt+Q to get back to Excel Then Put in B2: =striptxt(A2) Put in C2, array-enter with CTRL+SHIFT+ENTER: =IF(ISNUMBER(MATCH(ROW(A1),$B$2:$B$21+0,0)),ROW(A1 ),"") Put in D2, array-enter with CTRL+SHIFT+ENTER: =IF(C2="","",INDEX($A$2:$A$21,MATCH(C2&"",$B$2:$B$ 21,0))) Select B2:D2, copy down to D21. D2:D21 returns the required results. '------begin--- Function StripTxt(a As String) As String ' Niek Otten, March 22 2006 ' Strips all non-numeric characters from a string, but leaves any decimal separator ' Returns a string, not a number! ' If you need a number, use =Value(StripTxt(...)) Dim i As Long Dim b As String For i = 1 To Len(a) b = Mid$(a, i, 1) If ((Asc(b) 47 And Asc(b) < 58) Or b = Application.DecimalSeparator) Then StripTxt = StripTxt + b Next i End Function '----end---- -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "maperalia" wrote: Max; Thank you ver much. It is working perfectly!!!!!. However, I could not make it work with the sample shown bellow. My base data is: 1 1a 2 3* 4 5b 6 7 8 9 10 The text data to be sorted is: 10 1a 4 5b 3* I have formatted them as a number but still do not work.. Do you think is possible to work with this? Thanks in advance. Maperalia |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enhance Sort Results
Max'
Thanks very much. I set up the function and the formulas in the way you advised me. It is working perfectly. However, when I want the result (D2:D21) to be read as number to vlookup from another column just the number without text is read the rest not. Can you help me please? Thanks. Maperalia "Max" wrote: .. ugh, its considerably tougher now <g, but here's one way which seems to work ok .. Assuming source data within A2:A21 as before First we'll use a UDF StripTxt by Niek Otten to strip out all the non numerics Steps: Press Alt+F11 to go to VBE Click Insert Module Copy n paste the UDF StripTxt by Niek Otten below into the code window on the right (white space) Press Alt+Q to get back to Excel Then Put in B2: =striptxt(A2) Put in C2, array-enter with CTRL+SHIFT+ENTER: =IF(ISNUMBER(MATCH(ROW(A1),$B$2:$B$21+0,0)),ROW(A1 ),"") Put in D2, array-enter with CTRL+SHIFT+ENTER: =IF(C2="","",INDEX($A$2:$A$21,MATCH(C2&"",$B$2:$B$ 21,0))) Select B2:D2, copy down to D21. D2:D21 returns the required results. '------begin--- Function StripTxt(a As String) As String ' Niek Otten, March 22 2006 ' Strips all non-numeric characters from a string, but leaves any decimal separator ' Returns a string, not a number! ' If you need a number, use =Value(StripTxt(...)) Dim i As Long Dim b As String For i = 1 To Len(a) b = Mid$(a, i, 1) If ((Asc(b) 47 And Asc(b) < 58) Or b = Application.DecimalSeparator) Then StripTxt = StripTxt + b Next i End Function '----end---- -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "maperalia" wrote: Max; Thank you ver much. It is working perfectly!!!!!. However, I could not make it work with the sample shown bellow. My base data is: 1 1a 2 3* 4 5b 6 7 8 9 10 The text data to be sorted is: 10 1a 4 5b 3* I have formatted them as a number but still do not work.. Do you think is possible to work with this? Thanks in advance. Maperalia |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enhance Sort Results
Just point the vlookup to lookup C2:C21 instead of D2:D21. C2:C21 contains
the numbers in alignment. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "maperalia" wrote in message ... Max' Thanks very much. I set up the function and the formulas in the way you advised me. It is working perfectly. However, when I want the result (D2:D21) to be read as number to vlookup from another column just the number without text is read the rest not. Can you help me please? Thanks. Maperalia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort by column problems | Excel Worksheet Functions | |||
Excel "mode" function returns different results based on sort orde | Excel Worksheet Functions | |||
How to sort but keep linked formulas? | Excel Discussion (Misc queries) | |||
How do I format a column in alpha order? | New Users to Excel | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) |