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. 

[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

[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. 

[DavidP · Accepted solution]

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

[mmp-2020]

THANK YOU!!!! Worked Perfectly!