Tabulating Multi Level Data

keericks

I have a two field/column data file that has values like this (example)

STAGE FILENAME

Closed laptop.xls

Trial desktop.xls

Closed tablet.xls

Trial laptop.xls

Trial desktop.xls

Closed tablet.xls

What I'm after is the ability to show a column of each file name, and then columns showing the stages, and then the count of each time the file appears for each stage, such as:

FILENAME Closed Trial

laptop.xls 1 1

desktop.xls 0 2

tablet.xls 3 0

Hope this makes sense.

Common Use Cases

Accepted answers

NicoleJohnson

See attached!

1. Summarize the data first, grouping by Filename & Status, then count the # of records for each status.

2. Cross-Tab to pivot the data, grouping by Filename, then using Status as your header and Count as your values.

3. (Optional): Use the Data Cleansing tool (or a formula) to convert Null to 0 if desired.

Hope that helps!

EDIT: i.e. exactly what @MarqueeCrew posted, he's just a faster typer

Tabulating.yxmd

All comments

MarqueeCrew

Step 1: summarize

Group by FILENAME
Group by STAGE
COUNT (either)

You'll get:

FILENAME, stage, count as output rows

Step 2: Cross-tab

Group By FILENAME
Header = Name
Value = Count
Method = Sum

This will get you your desired data

Cheers,
Mark

NicoleJohnson

See attached!

1. Summarize the data first, grouping by Filename & Status, then count the # of records for each status.

2. Cross-Tab to pivot the data, grouping by Filename, then using Status as your header and Count as your values.

3. (Optional): Use the Data Cleansing tool (or a formula) to convert Null to 0 if desired.

Hope that helps!

EDIT: i.e. exactly what @MarqueeCrew posted, he's just a faster typer

Tabulating.yxmd

MarqueeCrew

If Nicole and I describe it the same way, then this way is a winner for sure!

Ps. I typed mine on my iPhone while playing with my puppy. 🐩🐾❤️

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703