create a column of unique names from multiple rows and columns

musoguy · May 19, 2011

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 · May 20, 2011

musoguy said:
Hi Peter, thank you for your reply. I followed your instructions to the letter and nothing happened when I entered data into sheet 3. Do you have any idea why?

Yes, I had the sheets, and my instructions, the wrong way around.

The correct instructions would be ..

1. Right click the Sheet3 name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Make change(s) on Sheet3 and review Sheet2.

You need to move the code from the Sheet2 module to the Sheet3 module (do not leave the code in the Sheet2 module!) and make one change to the code

Rich (BB code):

With Sheets("Sheet2")

musoguy · May 20, 2011

That works! Thank you so much. I do have a couple more requests for the code if that is okay.

In the second sheet I have the grid lines off and have created a border around each cell as there are other values in other columns. I have made the table about 100 rows long. However, when I add names to sheet 3, the borders in sheet 2 go a little haywire! I end up with (seemingly) random cells with no borders a few rows below where the data has been added. Any idea why? It's only cosmetic and I could just turn the grid lines back on if I need to, so no biggy.

More importantly, is it possible to sort the list by the final word in the cells (i.e. the last name). I'd rather not, if possible, have to create a firstname and lastname column if possible as I am using so many already.

Thank you so much for your help so far, it has been invaluable

Peter_SSs · May 20, 2011

You say you are using a lot of columns already. I am assuming you are only talking about Sheet3 here and that on Sheet2, columns B & C are available to use as helper columns. If this is not the case post back & I'll modify the code again.

If my assumption about the columns on Sheet2 is correct, then try this version.

 Private Sub Worksheet_Change(ByVal Target As Range) Dim aData, aList, t Dim i As Long, j As Long, R As Long, C As Long, k As Long Dim s As String aData = ActiveSheet.UsedRange.Offset(1).Value R = UBound(aData, 1) C = UBound(aData, 2) ReDim aList(1 To R * C, 1 To 3) For i = 1 To R For j = 1 To C s = aData(i, j) If Len(s) > 0 Then k = k + 1 aList(k, 1) = s t = Split(s, " ") aList(k, 2) = t(UBound(t)) On Error Resume Next aList(k, 3) = t(UBound(t) - 1) On Error GoTo 0 End If Next j Next i With Sheets("Sheet2") Intersect(.UsedRange.Offset(1), .Columns("A")).Clear With .Range("A2").Resize(R * C, 3) .Value = aList .Sort Key1:=.Cells(1, 2), Order1:=xlAscending, _ Key2:=.Cells(1, 3), Order2:=xlAscending, Header:=xlNo .Offset(, 1).Resize(, 2).ClearContents .RemoveDuplicates Columns:=1, Header:=xlNo End With With .Range("A1", .Range("A" & .Rows.Count).End(xlUp)) .BorderAround LineStyle:=xlContinuous .Borders(xlInsideHorizontal).LineStyle = xlContinuous End With End With End Sub

musoguy · May 20, 2011

Hi Peter, I am using a few columns after Column A in Sheet 2. Columns G on are free though. I'd try and figure it out myself to save you the time but the whole think looks Greek to me!

Peter_SSs · May 20, 2011

I think it would be easier - and shouldn't adversely affect your other Sheet2 data - if we just temporarily added 2 new columns beside column A and deleted them again when we are finished. I'm using these 2 columns to split the names so I can sort on last name and if last names are equal then sort on first name(s).

Try the code I posted last time but replace the section near the bottom with this:

 With Sheets("Sheet2") Intersect(.UsedRange.Offset(1), .Columns("A")).Clear .Columns("B:C").Insert With .Range("A2").Resize(R * C, 3) .Value = aList .Sort Key1:=.Cells(1, 2), Order1:=xlAscending, _ Key2:=.Cells(1, 3), Order2:=xlAscending, Header:=xlNo .RemoveDuplicates Columns:=1, Header:=xlNo End With .Columns("B:C").Delete With .Range("A1", .Range("A" & .Rows.Count).End(xlUp)) .BorderAround LineStyle:=xlContinuous .Borders(xlInsideHorizontal).LineStyle = xlContinuous End With End With End Sub

musoguy · May 20, 2011

Thanks again Peter. Will try it in the morning

Peter_SSs · May 20, 2011

musoguy said:
Thanks again Peter. Will try it in the morning

If my calculations are correct you are up VERY late and it is already nearly morning!!

musoguy · May 21, 2011

Hi Peter, I have created the sheet and tried out the formula and it works perfectly. Thank you so much. Now that it is up and running in the worksheet, I've realized that there is one altercation that I would love to make. How do I alter the code so that the border goes around columns A and B with an inside vertical line as well? I tried to come up with a solution myself, and failed! I tried altering your code from:

Rich (BB code):

With .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
            .BorderAround LineStyle:=xlContinuous
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous

to

Rich (BB code):

With .Range("A1:B1", .Range("A" & .Rows.Count).End(xlUp))
         .BorderAround LineStyle:=xlContinuous
         .Borders(xlInsideHorizontal).LineStyle = xlContinuous
         .Borders(xlInsideVertical).LineStyle = xlContinuous

And while it kind of works, when I delete a name, column A adjusts the border accordingly, but column B does not delete the border around the now empty cell at the bottom.

Peter_SSs · May 21, 2011

Re Sheet2

1. What is in column B?

2. Does the number of entries in B2 change when names are added/subtracted from Sheet3?

3. If so, how (it isn't part of my macro)?

If I can understand better what is going on there is a better chance of getting a better solution.

musoguy · May 21, 2011

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

create a column of unique names from multiple rows and columns

musoguy

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

musoguy

Board Regular

Peter_SSs

MrExcel MVP, Moderator

musoguy

Board Regular

Peter_SSs

MrExcel MVP, Moderator

musoguy

Board Regular

Peter_SSs

MrExcel MVP, Moderator

musoguy

Board Regular

Peter_SSs

MrExcel MVP, Moderator

musoguy

Board Regular

Similar threads

Forum statistics

Share this page

create a column of unique names from multiple rows and columns

Board Regular

MrExcel MVP, Moderator

Excel Facts

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock