Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group sort | Excel Discussion (Misc queries) | |||
Group & Sort | Excel Discussion (Misc queries) | |||
sort a group | Excel Discussion (Misc queries) | |||
Sort by Group Header or by Group SubHeader | Excel Programming | |||
Sort by group | New Users to Excel |