Macro to create unique id from email address and assign to repeated entries

Hello, I need help.

I am creating a rollup file from hundreds of excel files each has the same structure

Each file will have a dozens to hundreds of names and some files will share entries i.e Juan Alvarez might be in 10 different files

I need to create a unique numerical identifier starting at 0 for everyone based off there unique email address for each individual in the rollup file

So Juan Alvarez would need to be given the same numerical ID every time he showed up in the rollup


Thanks

File1 
Name         email
Bob Johnson  bobjohnson@email.com
sue Berg     sueberg@email.com
 .              .
 .              . 
 .              .
Juan Alvarez Juanalvarez@email.com
.

File2  
Name           email
Joe Jenson     joejenson@email.com
Juan Alvarez   Juanalvarez@email.com
sally Bergson  sallybersong@email.com
 .                 .
 .                 .
 .                 .
Frank Carpet   frakkcarpet@email.co
Bob Johnson    bobjohnson@email.com

Rollup
After filtering on "Name"

Name         email                    ID
Bob Johnson  bobjohnson@email.com      0
Bob Johnson  bobjohnson@email.com      0
Frank Carpet   frakkcarpet@email.com   1
 .                  .
 .                  . 
 .                  .
Juan Alvarez   Juanalvarez@email.com   17
Juan Alvarez   Juanalvarez@email.com   17
Joe Jenson     joejenson@email.com     18
sally Bergson  sallybersong@email.com  19
 .                 .
 .                 .
 .                 .
sue Berg       sueberg@email.com       53