Excel: Auto Assign Staff to a their area of expertise-CodePudding

I have been trying to build a table that will have 3 columns and assign staff based on a criteria from one of the columns. Our columns are simply A, B, and C. In column A, there will be a filing number that will be added manually and column B will be the Area and will be added manually from a drop down list via Data Validation. Column C I would like to have 'auto-populated' from a list of staff that are 'experts' in that area. Each staff is available 100% of the time so there can be times of rotation that will have their name more than once. Here is an example of those list for visual aid:

Data set

So when each area from column B (Area) is selected, Column C will 'random select' from the staff in the table shown in column F, G, and H with their respective areas of expertise. Here is a another visual of the result I would like:

Result Table

Is there a code for column C to get that result? I have tried a number of =rand or =randbetween and I also used the following formula to create another drop down list to manual select, again through data validation and this works:

=OFFSET(TABLE1!$F$1,1,MATCH($B2,TABLE2!$F$1:$H$1,0)-1,COUNTA(OFFSET(Settings!$F$1,1,MATCH($B2,Settings!$F$1:$H$1,0)-1,3)),1)

but I want it to be automated to eliminate that step of selecting from a dropdown since this is done in column B as well. Or is this a VBA type of build as this list will grown over time?

Thank you for taking the time to assist and I will be available for any questions.

CodePudding user response：

See this google sheet. The formulas should be the same in Excel:

https://docs.google.com/spreadsheets/d/1bCVsCxXnG3an1LZSCwVdzlZCSSctxdfy3WHQ7RisOY4/edit#gid=0

If you reformat the specialty list to two columns, with one column for the name and one for the specialty, you can use

=index(unique(filter(E:E, F:F=B2)), randBetween(1,COUNTA(unique(filter(E:E, F:F=B2)))))

Reformatted for easier reading:

=index(
  unique(filter(E:E, F:F=B2)), 
  randBetween(1, COUNTA(unique(filter(E:E, F:F=B2))))
)

You use FILTER to get the list of matching names. I wrapped that in UNIQUE just in case there were mistaken multiple entries for the same name and same specialty.

unique(filter(E:E, F:F=B2))

You can pass that to COUNTA to get the count of matching names:

COUNTA(unique(filter(E:E, F:F=B2)))

And use that in RANDBETWEEN to get a random number between 1 and the number of matches:

randBetween(1, COUNTA(unique(filter(E:E, F:F=B2))))

Then use INDEX to get the nth name out of the list, where the first argument is the unique filtered list and the second argument is the number generated with RANDBETWEEN.

Excel has a LET function which will let you reuse arguments, and may be simpler to read or maintain. This is not in the sheet, since I don't think Google Sheets has that function.

=LET(
  list, UNIQUE(FILTER(E:E, F:F=B2)),
  INDEX(list, RANDBETWEEN(1, COUNTA(list)))
)

LET takes pairs of name/values as arguments, with a final argument that is the output. Here list is the name and the unique/filter combo is the definition. Then you can use the name in the output formula

INDEX(list, RANDBETWEEN(1, COUNTA(list)))

Let me know if this works for you.