Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Manuela25 la Simpatie.ro
 | Femeie
25 ani
Bucuresti
cauta Barbat
25 - 62 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2228
|
|
This is an amazingly well thought method for sorting Slicers.
It is much more useful than creating a custom list, because the custom list will only work on your computer as you know. If you send the file to someone else, unless they create the custom list themselves, it won't work for them.
I recommend you see the video instead of this tutorial, but in case the video is gone, or you can't see videos, here goes.
The method from source video below works a treat and I just created it for something that might be as a list depending on your regional settings, but based on mine (Romania) it is not.
The correct order in English for Monday to Sunday.
In my case, the sorting is done alphabetically, as slicers work.
Now:
Fri
Mon
Sat
Sun
Thu
Tue
Wed
I want, of course:
Mon
Tue
Wed
Thu
Fri
Sat
Sun
For slicers to work, you either need a Table, or a Pivot Table.
I am in a Table with data, that in one of the columns already has the days of the week - a column named Day/Week, and I made a slicer that lists the days in the order from the first list above (alphabetically).
1. In a separate location on the sheet, I create the desired list order (the 2nd one above), then make a table of it with the header of its one column named Order. I changed the table name from Table2 to wkOrder
2. In my original table with data, I add a new column and call it Day/Week Sorted
3. In that column, I write the following formula:
=REPT(UNICHAR(8203); ROWS(wkOrder[Order]) - MATCH([@[day/week]]; wkOrder[Order]; 0) + 1) & [@[day/week]]
Explanation:
A. MATCH([@[day/week]]; wkOrder[Order]; 0) will display the row number from the ordered list (wkOrder table) corresponding to the day of the week from my main table
B. ROWS(wkOrder[Order]) will be equal to 7, as there are 7 days in the week from Mon to Sun
C. ROWS(wkOrder[Order]) - MATCH([@[day/week]]; wkOrder[Order]; 0) + 1 will subtract the row number obtained formula A from the total number of rows in the ordered list (wkOrder table) obtained in formula B above, then add 1 to obtain the correct row again
- eg. for Thursday, the formula in C will return 7 minus 4 = 3 + 1 = 4 (corresponding to Thursday
D. UNICHAR(8203) will insert a blank character the you cannot see, but Excel recognizes
E. REPT(UNICHAR(8203); ROWS(wkOrder[Order]) - MATCH([@[day/week]]; wkOrder[Order]; 0) + 1) will insert in the column the number of blank characters obtained in letter C
- eg. for Thursday, it'll insert 4 such blank characters
F. =REPT(UNICHAR(8203); ROWS(wkOrder[Order]) - MATCH([@[day/week]]; wkOrder[Order]; 0) + 1) & [@[day/week]] adds to the number of blank characters the day of the week from the original column
4. Although the new column (Day/Week Sorted) looks just like the old one (Day/Week), it actually also contains the blank characters we can't see but Excel can
5. Create a slicer from that column, and the slicer will be sorted in the same way the list in the wkOrder table is
Source & all the credits in the world:
_______________________________________
|
|
| pus acum 1 luna |
|