Splitting a Population into Two Studies|Power Query|Excel Forum|My Online Training Hub

Hi All,

I have what may be a bit of a tall order issue, and not sure whether it goes beyond the scope of this thread, but thought I would give it a shot.

I’m currently working that includes respondent ID (of which there are 972), Education Level, Age Group, Region, Race, and Gender.

I am looking for a way to bundle each respondent in either “Study 1” or “Study 2”, however, the part I’m having some difficulty with is that this grouping rule also needs to take stratification into account.

So for example, if I create that “Study 1” and “Study 2” grouping, and I filter only to White males from the Midwest who are age 4 to 6 (let’s say there are a total of 20 that show up using those filter criteria), the sample needs to be split evenly (or evenly-ish). So half of those respondents would need to be in Study 1, and the res in Study 2 (with no overlap, e.g., participant 1 can’t show up in both studies).

This stratification rule needs to hold true if I use different combinations of the other filters (so let’s say of those 972 respondents there are 13 Hispanic females who are from the South that have an education level of 4 and are age 7 to 9), I would need to split up that sample so that 7 of those respondents are in Study 1 and the remaining 6 are in Study 2.

I'm not sure if this is the most easily done with Power Query/Pivot, but I’ve attached a sample excel spreadsheet to (hopefully) help better visualize the set-up of the data

Again, I’m not sure if this is outside of the scope of this forum, but thought I'd check in with some experts.

Darko