Using Offset & If/Then Functions to create list of Duplicates/Uniques from Single Column

Hi everybody,

I was given a homework problem today and I am looking for information on how you all would recommend I go about solving it. My professor wants us to use strictly VBA functions, and every place I've seen online is using CountIf functions. I did a little research, but if for some reason I missed a thread, please just let me know.

I have a list of names -
Jeff
Jill
Ted
Jeff

I need to list each repeated name in a column called duplicates, and each single name in a unique column.

Here is where I am:
I know I'm going to need to create a new variable for each unique name, and each duplicate, but I would like to know if there is any way I can loop code to automatically create them? If this list was 1000 names long, or if a few names were changed on the list, the function would still have to work.

Also, how would you recommend listing these values? Maybe just use an ifempty statement on the duplicate list to change the value to the name in the column?

I'm not necessarily looking for a solution to the problem, just some help to see where I should get started and what functions I should look at.

Thanks a lot!

Well, what you could do is create two arrays; one of names, the other of statuses.

You read the first name and add it to the array of names, in the same place in the status array you add a status of unique.

Then you loop to the second name, and loop through your array of names to see if it matches a name in there. Then you have a simple condition to do one of three things:

1. If the new name does not match any name in your names array you add it to the end of the array, and at the same point in the status array add a status of unique.

2. If the name matches an existing name, and the status array for the match point is "Unique" you set it to "Duplicate".

3. If the name matches an existing name, but the corresponding status is already "Duplicate" you do nothing.

You keep doing this, using the Offset function to move to the next row until you reach a blank row.

Finally, the simplest thing to do would be to loop through the status array twice - the first time looking for unique values and writing them out to one column (using Offset to keep moving down a row), and the 2nd time looking for duplicate values and writing them to another column.

This isn't a particularly fast way of doing it, but it's fairly straightforward.

I'll be honest, I'm really confused with splitting it into two arrays. I'm sure I'm just not that advanced in VBA yet. Is there any way to do it with just if else statements, and offsets?

I was thinking: Read the first value, set that equal to a variable.
Check all values in the array with a loop until we hit an empty space (isempty function), and if we do, list the number in the first space open in the list on the right.

Not sure if that is efficient though.

That should work, yes. Are you having trouble with it?

I haven't started writing my vba yet, just planning everything out. I plan to start writing tonight. I'll let you know if I have any issues!

Thanks so much for the help!