1. I have some data in two sheets in a file named 'Sample'. I tried to use dynamic input to extract the data from both the sheets. It seems like I am getting an error because the columns are not in the same order. I have the same number of columns in each sheet. So is there a way around it other than editig the first "Sample" file to make sure that first column headings are the same in each sheet?
Or am I doing this completely wrong and there is a different way to extrract multiple sheets in the same workbook?
2. I have attached a second file with sample data that has same columns and in same order in both sheets in file named 'Sample 2'
How can I count the each category of color? Any color value with text or value greater than zero should equal to one. Blanks are zeroes. I am notlooking to just the values and a person can pick multiple colors.
How can I measure how many times each individual picked what color on 12/1 and 12/2. And at what proportion each color was picked on those specific days?
Solved! Go to Solution.
Hi @Tamzid,
1. Check out this article for instructions on how to pull in multiple excel files/sheets with different schemas: The Ultimate Input Data Flowchart
2. Here's what I came up with:
- Multi-Field Formula replaces all null values in the "color" fields with 0, and any other fields (non-null fields) with a 1
- Transpose to flip the data
- Select tool changes the [Value] column (with the number of selections for each color) from a String to an Int type
- Summarize (1) Groups By the Date and Color and sums the number of times picked
- Summarize (2) Groups By the Date and gives a total sum of all colors picked (used for the percentage calculation later)
- Join the data back together to append each Date with the appropriate Total
- Formula calculates the percentage