create a column of unique names from multiple rows and columns

[musoguy]

Hi there!

I have a worksheet with people's names listed on it across multiple rows and columns (some duplicated). Is it possible to have excel take all the data, filter out any duplicates and create a column of unique names? I know I can do this if I have a single column, but not sure if it's possible with multiple columns. The other thing, which is quite a big thing really, is that the list that is created has to be dynamic, so when the user adds a new unique name to the main worksheet, it also gets updated on the unique name list.

I presume this is a VBA project, but am not sure where to start!

Thanks in advance for any help you can offer

 

[Peter_SSs]

Column B counts how many times the person named in Column A appears on a different worksheet. It is not tied to your code. There is a formula in the cells in Column B copied down more rows than I will ever need, and conditionally formatted to show only if there is a name next to it. So, if possible, all I need is for the border to extend to that row as well as when I name appears in Column A, the count appears in Column B.

Hope that makes sense

In that case..

1. Leave my code as is.

2. Remove any borders that have been placed around any column B cells.

3. Alter your Conditional Formatting in column B to include a border around the cell (as well as the font colour I presume you already used) if there is a name next to it.

 

[musoguy]

That fixed it, thanks.

 

[musoguy]

Hi Peter,

I am trying to alter the code you wrote a touch. I realized a few days ago that on the finished sheet, the activesheet that the list is created from started on row C, not B as I stated. I changed the line of code:

    aData = ActiveSheet.UsedRange.Offset(1).Value

to

    aData = ActiveSheet.UsedRange.Offset(2).Value

and that fixed it. However I have now found the need to add another row before the the list begins, so it now starts on row 4. I tried changing the offset to (3) which worked, but screwed up the formatting on the page it pastes it to, and I'm not sure why. Hoping you can help.

James

 

[musoguy]

Fixed it. It was a problem with the conditional formatting in neighboring cells, not the code.