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

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.

[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 · Accepted solution]

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!

NJ

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

[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. 🐩🐾❤️