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

 

[Ruddles]

I think we're going to need to see a representative sample of your data. If you put borders around the worksheet cells and select a fairly small font, it should paste fairly legibly directly into a forum message.

 

[Peter_SSs]

Further to Ruddles' post ..

1. The Borders-Copy-Paste method does require a few other things to be in place as well. It does not work with all web browsers and requires certain UserCP settings. More details here. My signature block contains a couple of other methods for showing small screen shots as well.

2. Is there other data besides names on the 'main' worksheet? If so, how doe we identify people's names? For example, if the user enters "June" on the main sheet, how would we know to check/add that to the names list or whether they are just entering a month name? Knowing more about your layout may help.

 

[musoguy]

Thank you both for your replies, they make perfect sense. The worksheet is very large, there is a lot of information on it so I don't think it would be legible to paste into the forum. Will try it though if this post doesn't make sense!

To give more information on the workbook:

I am creating the workbook to allow a user to assign teachers to classes. Sheet1 contains a table where columns correspond to class rooms, the second row allows the user to choose a discipline from a drop down list. Depending on their choice of discipline, they choose the class subject from the cell below, using a dependent list. The cell below that contains another dependent list made up of the teachers able to teach in the discipline they chose earlier. This sheet works fine.

The second sheet contains a list of all the teachers and a count of how many classes they are currently teaching, dependent on how many times their names appear in sheet1.

The third sheet contains the teacher's name data. So that the correct list can be called for depending on the user's selection of discipline in sheet1, sheet3's headers is made up of all the different disciplines. Below each discipline is a list of teachers that can teach it. Some teachers will simply appear in one column, others in multiple. This table range is A2:K39. The whole table is just compromised of people's names.

The user when opening the workbook for the first time, starts in the data page and adds the teacher's names in as many columns as necessary. What I would love to happen is as they add a name into the range data!A2:K39, if the name is unique it also gets added to Sheet2, Column A, starting in row 2. If possible if this list also stayed in alphabetical order that would be amazing.

There are other sheets involved in the workbook, but it is sheets 2 and 3 that are specific to this issue.

Thanks for taking the time to ask for more info, I hope this all makes sense and you can help!

James

 

[danzon]

I fall of the names were being entered in a single column, I could offer a solution... but going across multiple columns make it very tough...

 

[Peter_SSs]

The worksheet is very large, there is a lot of information on it so I don't think it would be legible to paste into the forum. Will try it though if this post doesn't make sense!

James

Definitely do not post it if it is large. If you do want to post a screen shot or two to demonstrate, make up a new dummy workbook with just enough size/variation to demonstrate your principles and post small screen shots from that. I'm guessing that you could get your idea across with just 2-3 each of teachers, rooms, disciplines ...

I don't think I have your exact layout & requirements clear in my head yet but I suspect that a macro approach would be required. Users would therefore have to have macros enabled. Would that be acceptable?

 

[musoguy]

Absolutely. I am using a couple of macros in the workbook already so that is not a problem. Will figure out how to post a couple of screenshots!

 

[musoguy]

Okay, so I have generate a couple of screenshots from a dummy workbooks. This is the third page, the data page that the user enters the teacher's names into the table by discipline:

Excel Workbook
	A	B	C
1	Acting	Dance	Music
2	Teacher 1	Teacher 6	Teacher 8
3	Teacher 2	Teacher 2	Teacher 1
4	Teacher 3	Teacher 7	Teacher 4
5	Teacher 4	Teacher 1
6	Teacher 5
Sheet3

#VALUE!

Excel 2010


As sheet 3 is updated by the user, sheet two should automatically update as well if a unique name is added to sheet 3, and if possible stay in alphabetical order.

Hope this makes more sense now.

 

[Peter_SSs]

Test this in your dummy workbook first.

To implement ..

1. Right click the Sheet2 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 Sheet2 and review Sheet3.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> aData, aList<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, R <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, C <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    aData = ActiveSheet.UsedRange.Offset(1).Value<br>    R = <SPAN style="color:#00007F">UBound</SPAN>(aData, 1)<br>    C = <SPAN style="color:#00007F">UBound</SPAN>(aData, 2)<br>    <SPAN style="color:#00007F">ReDim</SPAN> aList(1 <SPAN style="color:#00007F">To</SPAN> R * C, 1 <SPAN style="color:#00007F">To</SPAN> 1)<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> R<br>        <SPAN style="color:#00007F">For</SPAN> j = 1 <SPAN style="color:#00007F">To</SPAN> C<br>            k = k + 1<br>            aList(k, 1) = aData(i, j)<br>        <SPAN style="color:#00007F">Next</SPAN> j<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet3")<br>        Intersect(.UsedRange.Offset(1), .Columns("A")).ClearContents<br>        <SPAN style="color:#00007F">With</SPAN> .Range("A2").Resize(R * C)<br>            .Value = aList<br>            .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlNo<br>            .RemoveDuplicates Columns:=1, Header:=xlNo<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[musoguy]

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?