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 ACol B Col C1 month 0.770.67912 months0.090.7553 months0.200.4646 months 0.040.884YTD0.800.931 Desired output Col ACol B Col C1 month 0.240.6973 months0.740.7066 months 0.800.21512 months0.680.884YTD0.150.262 TestData.xlsx

Designer| Re-ordering rows in data

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

TestData.xlsx

Parse

Transformation

Preparation

Accepted answers

StephaneP

Hello,

Same as @KarolinaRoza I think a basic SORT is working fine as your target respect the alphabetic order

All comments

mst3k

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"

AngelosPachis

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

example.yxmd

KarolinaRoza

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703