Dynamic/Unique List Data Validation

[Winn88]

Excel Wizards,

I am looking for a way to make a dynamic list for a subset of values and another list for another subset. For example- take the below data:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]Customer1[/TD]
[TD][B2][/TD]
[/TR]
[TR]
[TD]Customer1[/TD]
[TD][B3][/TD]
[/TR]
[TR]
[TD]Customer1[/TD]
[TD][B4][/TD]
[/TR]
[TR]
[TD]Customer2[/TD]
[TD][B5][/TD]
[/TR]
[TR]
[TD]Customer2[/TD]
[TD][B6][/TD]
[/TR]
[TR]
[TD]Customer3[/TD]
[TD][B7][/TD]
[/TR]
[TR]
[TD]Customer3[/TD]
[TD][B8][/TD]
[/TR]
[TR]
[TD]Customer3[/TD]
[TD][B9][/TD]
[/TR]
[TR]
[TD]Customer4[/TD]
[TD][B10][/TD]
[/TR]
</tbody>[/TABLE]

Use Case: For every customer a user has to select the rank of the record, the ranks have to be unique for each customer.

I would like a list box in B2 with the options (rank) 1,2,3 because there are 3 records with Customer1. If for example rank 2 is selected in B2, in B3 the remaining options are 1,3 and so on...

for B5 there are two records (Customer2) in columnA so the options (ranks) in the list are 1,2. Once 1 is selected, 2 will be the only remaining option for the second record with Customer2


This has to be dynamic as this happens once a month and therefore I cannot set a static list because next month Customer1 might have 10 records instead of 3.

Please help!