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.

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.

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
---

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.

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

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

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