Missing Numbers from a Custom Sequence According to Multiple Unique Identiers

[JJohnson07]

I am using Excel 10. If I have multiple reps that have scheduled visits, and the scheduled visits I want to track are identified with the numbers 2-51. How do I identify the missing visits according to the rep? I have found a few great VBAs for the missing numbers, have not found a solution tying the missing numbers to the rep's name. I would greatly appreciate any support! Below is an example of the data:

[TABLE="width: 250"]
<tbody>[TR]
[TD]Reps
[/TD]
[TD]Visit
[/TD]
[/TR]
[TR]
[TD]Jim
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Jim
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Jim
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Jim
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Jim
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]Beth
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Beth
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Beth
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Josh
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Josh
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Josh
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Josh
[/TD]
[TD]7
[/TD]
[/TR]
</tbody>[/TABLE]

The output I need is as follows:

[TABLE="width: 250"]
<tbody>[TR]
[TD]Jim[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Josh[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Josh[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]reps[/TD]
[TD]Visit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Jim[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jim[/TD]
[TD][/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Jim[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beth[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Jim[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Josh[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Jim[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Jim[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Beth[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Beth[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Beth[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Josh[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Josh[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Josh[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]Josh[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


f2=SUM(IF(FREQUENCY(IF($A$2:$A$13=$F2,$B$2:$B$13),$B$2:$B$13),1)) control+shif +enter copy down

After you type in the formula, don't just press enter.
Instead, press CTRL + SHIFT + ENTER


If you've already entered the formula, then highlight the cell with the formula and press F2.
Then press CTRL + SHIFT + ENTER




When entered correctly, the formula will be enclosed in {brackets}

 

[JJohnson07]

Thank you for your reply Marziotullio! However, it's not the a visit count based on the rep's name that I'm looking for. It's the missed visits according to the reps name. Note that in my output example Beth does not show up because there are no missing numbers between 2 and 4. I am actually able to use your array for another need though.