Groping & Sorting in one step

B

Bruno Campanini

Given:
C
A
... [blank]
D
12
8
A
8
D
... [blank]
C
0 [zero]

is here anybody able to get: (grouping and sorting)
0 [zero]
8
12
A
C
D
... [blank]

with a single formula?

Bruno
 
B

Bruno Campanini

Max said:
Other than the beautiful typo in the subject line <g>,
Click to expand...

I reegret very much for typo...
I think the OP wants to extract a uniques list from a given source list,
and
to have this uniques list sorted in ascending order with numbers ahead of
upper case alphas, without any intervening blank cells, and all in one
stroke ..
Click to expand...

You understood exactly what I mean.
Yes, just in one stroke.

I know how grouping and sorting alphas, in one stroke.
I know how grouping and sorting numerals, in one stroke.
But both formulas fail in grouping and sorting
mixed alphas and numerals.
And I'm unable to assemble a single formula doing the job!

I'm going to check your one additional column support
formula.
Thanks for now

Bruno
 
R

Ron Rosenfeld

Given:
C
A
... [blank]
D
12
8
A
8
D
... [blank]
C
0 [zero]

is here anybody able to get: (grouping and sorting)
0 [zero]
8
12
A
C
D
... [blank]

with a single formula?

Bruno
Click to expand...

If you don't mind having the numbers after the letters, you could use the
UNIQUEVALUES function available in Laurent Longre's free add-in: morefunc.xll
available at http://xcell05.free.fr/


--ron
 
B

Bruno Campanini

If you don't mind having the numbers after the letters, you could use the
UNIQUEVALUES function available in Laurent Longre's free add-in:
morefunc.xll
available at http://xcell05.free.fr/
Click to expand...

What I'm looking for is a worksheet function
assembled with Excel worksheet functions (not a Vba
one, which is a much easier way to do the job).

Anyway thanks for info
Bruno
 
B

Bruno Campanini

Max said:
Hang around for insights from others for this, but in the interim ..
here's
one non-array formulas way to extract the desired results using just one
additional criteria col

With the source data assumed in col A from A1 down

Put in B1:
=IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",
INDEX(A:A,MATCH(SMALL(C:C,ROWS($A$1:A1)),C:C,0)))

Put in C1:
=IF(TRIM(A1)="","",IF(COUNTIF($A$1:A1,A1)>1,"",CODE(TRIM(A1))))

Select B1:C1 and copy down as far as required to say, A100
to cater for the max expected data range in col A

Col B will return the desired results, all neatly bunched at the top, with
blank ("") rows below
Click to expand...

I checked your formula Max but it works nice only
on single character range (single alpha, single 0-9 figure).

Well, for sake of semplicity I proposed such an array but,
to be more precise, I should mention a range containing
alpha words and numbers of any dimension.

Bruno
 
M

Max

Sorry, there's a correction required to the formula in C1
It should be in C1:
=IF(TRIM(A1)="","",IF(COUNTIF($A$1:A1,A1)>1,"",
IF(ISNUMBER(A1),A1,CODE(TRIM(A1)))))
 
M

Max

You're welcome, Bruno. Please note there's a correction
to the criteria formula in C1 (just posted)
 
M

Max

I checked your formula Max but it works nice only
on single character range (single alpha, single 0-9 figure).

Well, for sake of semplicity I proposed such an array but,
to be more precise, I should mention a range containing
alpha words and numbers of any dimension.
Click to expand...

I've just posted a correction to the criteria formula in C1, but it still
implicitly assumes the original data as posted was representative. Hang
around for better answers from others.
 
R

Ron Rosenfeld

What I'm looking for is a worksheet function
assembled with Excel worksheet functions (not a Vba
one, which is a much easier way to do the job).
Click to expand...

Ah -- you could have posted that requirement initially.


--ron
 
Top