Groping & Sorting in one step

[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

 

[Silvabod]

COUNTIF?

 

[damorrison]

what are you trying to do??

 

[Max]

with a single formula?

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

 

[Max]

what are you trying to do??

Other than the beautiful typo in the subject line <g>,
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 ..

 

[Max]

Oops, typo, A100 should read C100 in the line:

Select B1:C1 and copy down as far as required to say, A100

 

[Bruno Campanini]

Other than the beautiful typo in the subject line <g>,

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

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

 

[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

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

 

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

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

 

[Bruno Campanini]

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

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

 

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

 

[Max]

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

 

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

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.

 

[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).

Ah -- you could have posted that requirement initially.


--ron