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.
Solved! Go to 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 :)