Multiple Sheets and Calculation

[Tamzid]

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?

[SophiaF · Accepted 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