CrossTab/if then

mmp-2020

Hi all,

I am trying to do a very simple task. I have a column called "Ages". The values in that column are a mix of 20-30, 30-40, 50-60. I want to be able to see how many of each age. At first I used text to column to separate data, but then I end up with 30-40 in my 20-30 column because it is just splitting the data, but not actually moving it to the right column.

Goal: I want to split the "Ages" column and feed the correct data to the matching column. i.e. 20-30 should only show the rows that have 20-30. 30-40 column should only have 30-40 data in that column, ect.

I have tried Filters, Crosstab, RegEx... Please help.

Common Use Cases

Accepted answers

DavidP

@danilang is right, crosstab is the way to go here, which also gives you the ability to add totals.

age groups.png

age groups.yxmd

All comments

BrandonB

Use a formula tool to create a new column called age groups and a formula like this

IF [Ages] >=20 AND [Ages]<30

THEN “20-30”

ELSEIF [Ages] >=30 AND [Ages]<40

THEN “30-40”

ELSEIF [Ages] >=40 AND [Ages]<50

THEN “40-50”

ELSEIF [Ages] >=50 AND [Ages]<60

THEN “50-60”

ELSE “Other”

ENDIF

Then use a summarize tool and group by this new age groups column and then count one of your fields. This should show you the number of people you have in each group.

danilang

Hi @mmp-2020

If you want have each record showing up in the proper category column, use something like this

The first formula tool just generates a random age for each person in the data set. The second one uses a formula similar to @BrandonB's to generate a category Label. The records are crosstabbed using the person's name as a key and setting the categories as the column headers. After the Dynamic Rename which cleans the messy headers that the Crosstab tool creates, you're left with this

Dan

XIfThen.yxmd

mmp-2020

Hmm - Not quite what I'm trying to do.

I have a sheet:

Name	Ages
Amy Smith	20-30, 40-50
John Adams	40-50
Peter Andrews	60-70

I want to see something like this:

Name	20-30	40-50	60-70
Amy Smith	1	1
John Adams		1
Peter Andrews			1

Even better would be to get to Totals so I can see how many 20-30 vs 60-70s I have. I used a google form to collect Data and now I am trying to break down the results received because google doesn't allow me to do any data cleansing, so their charts are invalid. Everything that was set up as a checkbox on my form needs to be separated and tallied up.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703