Hi All,
I have a dataset (attached) that looks like below. Rows need to be re-ordered by col A as a group. That is, it needs to be 1month, 3months, 6months,12months and YTD. Can someone guide on this?
Input
Col A | Col B | Col C |
1 month | 0.77 | 0.679 |
12 months | 0.09 | 0.755 |
3 months | 0.20 | 0.464 |
6 months | 0.04 | 0.884 |
YTD | 0.80 | 0.931 |
Desired output
Col A | Col B | Col C |
1 month | 0.24 | 0.697 |
3 months | 0.74 | 0.706 |
6 months | 0.80 | 0.215 |
12 months | 0.68 | 0.884 |
YTD | 0.15 | 0.262 |
Solved! Go to Solution.
lots of approaches, a couple i can think of-
make a formula tool with a switch or an if statement that assigns a number in order for each value-
if Field1='1 month' then 1
elseif Field1='3 months' then 2
etc
then you can sort by this new field.
you could also store the sort in a text input or an external file, where column A has "1 month" and column B has 1, then "3 months"/2, and so on. then join that to your data and again have a column with the sort order stored in it. the goal is to have an numeric field you can reliably sort by
if you want to get fancy, you could do something like a regex or substring formulas, or something to parse out the number from the value - i.e. the "1" from "1 month", etc. that wouldn't be the best solution though if it's possible you'd have other values like "2 years" or "12 weeks"
Hey @Mj9715 ,
You can use a lookup table for this, that will give you the correct order for each row.
Assuming that the lookup table looks like the one below
You can use a find and replace tool to append the order field in your main table and then sort based on that field.
Hope that helps,
Angelos
hi @Mj9715 ,
I applied Sort tool for first column of data and it works. But as this column as string type of data it is not the best solution - it usually doesn't work as we expected.
You can create new column with numeric values for corresponding months : 3 Months -> 3 and sort by this column and Union it with YTD row.
Regards,
Karolina
Hello,
Same as @KarolinaRoza I think a basic SORT is working fine as your target respect the alphabetic order
Thanks all. All of them work, but Stephane's was easiest to use since it worked by using dictionary order. 🙂