Unique values from different sheet

[sylbalm]

I have two sheets in my workbook, the first called "Fall" is courses listed by instructor, subject, and course #. The second, called "Dashboard" is a list of instructors who teach those courses, and I need it to automatically populate with the concatenated subjects and course #s from "Fall". It needs to list each course that each instructor teaches, across three columns, listing each course only once.

 

[DanteAmor]

Hi sylbalm

Thanks for posting on the forum.

Try the following macro:

Sub UniqueValues()
  Dim a As Variant
  Dim f As Range
  Dim i As Long, lr As Long, lc As Long
 
  a = Sheets("FALL").Range("A2", Sheets("FALL").Range("C" & Rows.Count).End(3)).Value
 
  With Sheets("DASHBOARD")
    .Range("A1").Value = "TTF"
    For i = 1 To UBound(a)
      Set f = .Range("A:A").Find(a(i, 1), , xlValues, xlWhole)
      If f Is Nothing Then
        lr = .Range("A" & Rows.Count).End(3).Row + 1
        .Range("A" & lr).Value = a(i, 1)
        .Range("B" & lr).Value = a(i, 2) & " " & a(i, 3)
      Else
        lc = .Cells(f.Row, Columns.Count).End(1).Column + 1
        .Cells(f.Row, lc).Value = a(i, 2) & " " & a(i, 3)
      End If
    Next
  End With
End Sub

Note:
You can update your settings, to know if you have office 365.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[kweaver]

If you're using 365, one way might be:

Book3
	A	B	C	D
3	Joe	AH 115	AHH 233	AHH 333
4	Sam	AH 115
5	Sally	AHR 116
6	Laura	AHB 117	AH 115
7	Donald	AHB 117	AHB 117
8	Colin	AHR 401
DASHBOARD

Cell Formulas
Range	Formula
A3:A8	A3	=UNIQUE(FALL!A2:A11)
B3:D3,B6:C7,B4:B5,B8	B3	=TRANSPOSE(FILTER(FALL!$B$2:$B$11,FALL!$A$2:$A$11=A3)&" "&FILTER(FALL!$C$2:$C$11,FALL!$A$2:$A$11=A3))
Dynamic array formulas.

 

[DanteAmor]

Another way in 365.

In cell A3 of the DASHBOARD sheet:

=UNIQUE(FALL!A2:A11)

In cells B3 to B8 of the DASHBOARD sheet:

=TRANSPOSE(FILTER(FALL!B2:B11&" "&FALL!C2:C11,FALL!A2:A11=A3))

 

[sylbalm]

Another way in 365.

In cell A3 of the DASHBOARD sheet:

=UNIQUE(FALL!A2:A11)

In cells B3 to B8 of the DASHBOARD sheet:

=TRANSPOSE(FILTER(FALL!B2:B11&" "&FALL!C2:C11,FALL!A2:A11=A3))

That works great! I think I like this a little better than the VBA solution, which also works great. Thanks so much.

 

[sylbalm]

Another way in 365.

In cell A3 of the DASHBOARD sheet:

=UNIQUE(FALL!A2:A11)

In cells B3 to B8 of the DASHBOARD sheet:

=TRANSPOSE(FILTER(FALL!B2:B11&" "&FALL!C2:C11,FALL!A2:A11=A3))

Another way in 365.

In cell A3 of the DASHBOARD sheet:

=UNIQUE(FALL!A2:A11)

In cells B3 to B8 of the DASHBOARD sheet:

=TRANSPOSE(FILTER(FALL!B2:B11&" "&FALL!C2:C11,FALL!A2:A11=A3))

Actually, let me ask you one more question about this -- I can't use the Unique formula is A3, since that's a fairly fixed list, and so if the transpose formula doesn't find a match for A3 (or A4,5,6, etc), it shows #CALC!. How can a make a null result show up as just an empty cell?

 

[kweaver]

How about this:

=TRANSPOSE(FILTER(FALL!B2:B11&" "&FALL!C2:C11,FALL!A2:A11=A3,""))

 

[Fluff]

How about

=TOROW(FILTER(FALL!B2:B11&" "&FALL!C2:C11,FALL!A2:A11=A3,""))

 

[sylbalm]

How about this:

=TRANSPOSE(FILTER(FALL!B2:B11&" "&FALL!C2:C11,FALL!A2:A11=A3,""))

Beautiful, thank you.