Help w/ complex copy paste using VBA

Hello Experts! I am completely stuck on trying to figure out how i can automate a copy/paste in VBA. I'm trying to take a list of cells in a single column, for each unique value, copy it 16 times into a new column. Then in an adjacent column count up each unique cell. Each value should count up to 16 times. The tricky part is that the unique values in the original column will change each day and I need the vba to be adaptable if the list is shorter or longer each day. I'm probably not explaining this well enough, so I attached a example spreadsheet to this post. I'm beginning to wonder if this is possible to do using vba.

For each unique value in Column A, I would like to copy each one 16 times each. I would then like to add the a position or count for each one, each "Site" will count up to 16. The number of values in column A will constantly change so I need the vba to be adaptable to shorter or longer lists of Sites.

Column A: This is the original data I have to work with.

Columns E:F, This is what I need the data to look like.

Late entry:

Please Login or Register  to view this content.

Regards, TMS