Hi,
We are trying to build a basic dashboard that is automated having to only paste in a raw data sheet that is cleaned and then uses the data from the 'clean data' sheet.
At the minute we are trying to work out MODE of a text string within a range per week client activity is live. The formula that works for this is:
=INDEX('Clean Data'!D2:D20,MODE(MATCH('Clean Data'!D2:D20,'Clean Data'!D2:D20,0)))
However, there are two more variables we need to use to get the right value.
1. The 'clean data' sheet is an array from A1:H2000 for example. In column B there are names of clients that we need to match with the name of the client on the tab we are using in cell A2.
2. We only want to capture the mode of text within a certain week. We currently have the dates in 'clean data' sheet in the dd/mm/yyyy format in column G. The date the client beings activity is in the current sheet in cell D1. We then need to be able to drag this formula to the right to show increments of 7 days.
For example Week 1 matches if date in 'clean sheet' is equal to or greater than D1 by 7days. Week 2 would be greater than D1 by 7 to 14 days.
I have uploaded screenshots with some cells redacted but hopefully gives enough of a picture.
We are trying to build a basic dashboard that is automated having to only paste in a raw data sheet that is cleaned and then uses the data from the 'clean data' sheet.
At the minute we are trying to work out MODE of a text string within a range per week client activity is live. The formula that works for this is:
=INDEX('Clean Data'!D2:D20,MODE(MATCH('Clean Data'!D2:D20,'Clean Data'!D2:D20,0)))
However, there are two more variables we need to use to get the right value.
1. The 'clean data' sheet is an array from A1:H2000 for example. In column B there are names of clients that we need to match with the name of the client on the tab we are using in cell A2.
2. We only want to capture the mode of text within a certain week. We currently have the dates in 'clean data' sheet in the dd/mm/yyyy format in column G. The date the client beings activity is in the current sheet in cell D1. We then need to be able to drag this formula to the right to show increments of 7 days.
For example Week 1 matches if date in 'clean sheet' is equal to or greater than D1 by 7days. Week 2 would be greater than D1 by 7 to 14 days.
I have uploaded screenshots with some cells redacted but hopefully gives enough of a picture.
