Replace a drop-down with auto assignment based on a mapping guide

Good evening,

I am able to create a unique list of names, and the number of times that unique name shows in my data set. That part is great, via a macro and formulas in that macro.

However, from there I have to go to each unique name/count and assign it to its appropriate destination bucket, via a drop-down option (column D on the Source sheet in the attached sample workbook).

MY GOAL
I would like to automate the assignment of the unique names from the SOURCE sheet to the appropriate buckets on the DESTINATION sheet, based on the rules in the MAPPING GUIDELINE sheet.

The attached sample file has notes that explain my challenge.

Thank you much for any help!

Ugh, great point - my fault for an erroneous sample file. You found a flaw in the sample data mapping guideline (on the "Mapping Guideline" sheet).

Rather than the "sport" this sheet should have listed the "team." Listing by team is how my real data works.

The new attached sample workbook corrects that issue.

Thanks so much!!

Jeff

The "Mapping Guidelines" would be better organised as:

Team Bucket

Braves A
Cowboys A
Raiders A
Sharks B
Wizards C

then use:

=VLOOKUP(B3,"Mapping Range",2,0)

where "Mapping Range" is a named range as per above.

Thanks JohnTopley.
Apologies for my inexperienced questions.
To be clear, I'm pulling data from the "Source" sheet and placing it into the "Destination" sheet, based on the "Mapping Guidelines" sheet.
So now I have the mapping guidelines labeled as you suggested, and I named the range on the "Mapping Guideline" sheet (range is called "Mapping Range"). I assume your solution places your vlookup on the "Destination" sheet. However, I don't see how I'm pulling the name and respective count from Source to Destination.
Again, thanks for the help!!

I'm going to think of another way to organize my data. Thanks for the help. Closing this message.