Group & Sort with formulas (HTML)
 

da
ad
3MOL
a0
1
543
154
0

MOL


0
1
3
54
a
d
MOL


How to have the first column grouped and sorted
as per the second column or as per the third column?

Only worksheet functions please!
No VBA, no helper column.

Bruno

Group & Sort with formulas (HTML)
 

hi,

the following function performs a sort based solely on the ASCII code of first character of each value from the range,
and only if the range do not have any empty cell.

=INDEX(B1:B7,MATCH(SMALL(CODE(LOWER(B1:B7)),ROW(B1 :B7)),CODE(LOWER(B1:B7)),0))

example for the range B1:B7
you must select a range with the same number of cells,
eg select the range F1:F7
copy the formula in the formula bar
and validate the function with the keys ctrl + shift + enter

this formula does not work for values €‹€‹more sophisticated, like:

51
53
Mo
mo
March
Marf



--
isabelle



Le 2012-07-18 18:27, Bruno Campanini a écrit :

d a
a d
3 MOL
a 0
1
54 3
1 54
0

MOL

0
1
3
54
a
d
MOL

How to have the first column grouped and sorted
as per the second column or as per the third column?
Only worksheet functions please!
No VBA, no helper column.
Bruno

Group & Sort with formulas (HTML)
 

on 19-07-12, isabelle supposed :

hi,

the following function performs a sort based solely on the ASCII code of
first character of each value from the range,
and only if the range do not have any empty cell.

=INDEX(B1:B7,MATCH(SMALL(CODE(LOWER(B1:B7)),ROW(B1 :B7)),CODE(LOWER(B1:B7)),0))
[...]

Thanks Isabelle.
Let me explain my state of the art:
1 No problem in grouping and ordering cells with only numbers and
blanks
2 No problem for the ones with only text and blanks
3 No problem in ordering cells with text, numbers, blanks, dups
4 Unable to group and order the ones with text, numbers, blanks, dups.

Well, it's quite a simple job to solve point 4 with VBA code; I'm
wondering if using only worksheet function (and no helper column) we
can do the same job.

I've not to use what I'm looking for, it's only a matter of theory...

Bruno



example for the range B1:B7
you must select a range with the same number of cells,
eg select the range F1:F7
copy the formula in the formula bar
and validate the function with the keys ctrl + shift + enter

this formula does not work for values €‹€‹more sophisticated, like:

51
53
Mo
mo
March
Marf



--
isabelle



Le 2012-07-18 18:27, Bruno Campanini a écrit :

d a
a d
3 MOL
a 0
1
54 3
1 54
0

MOL

0
1
3
54
a
d
MOL

How to have the first column grouped and sorted
as per the second column or as per the third column?
Only worksheet functions please!
No VBA, no helper column.
Bruno