-1

I have a list of names in the leftmost column of a table, and between 1 and 4 languages entered into their own columns (lang1, lang2, lang3, lang4). I would like to create an area where names can be entered into any one of six cells and an adjacent list is generated with all of the languages known by this set of individuals.

Essentially I want Excel to compare six 1 row by 4 column arrays and return a single column array of whatever appropriate length. A union of the values in six arrays.

Here is an example I created without the formula in order to help explain.

Example 1

I need to generate a list of all of the languages a group of less than or equal to six people is able to speak. If Jacob were the only one in the list, then the list would simply read "English, Spanish, Portuguese". If the list consisted of only Jacob and Sally, it would read "English, Spanish, Portuguese, French".

I can change the setup of the table if necessary. For instance, originally each language was its own column, with an X or a Y as a flag to indicate that the person knew the language. I thought this current way might be easier.

Thanks and any help is appreciated!

  • Your source data Table has names already looks unique since has no duplicate,, please Edit your post and add proper data (name), I think you want list for each name could speak how may languages!! – Rajesh S Aug 10 at 9:40
  • I want a list of languages spoken by the six as a group, so that I could enter any six names and generate a list specific to them.. There are only no duplicates in the list because it is an example of the outcome I want to generate, but the languages were just manually entered in that case. – C. Pettit Aug 10 at 9:48
  • ,, let me clear Unique list of names is different than list of any Six names!! Better be specific about six names. Also Jacob speaks 3 languages but your output has only one !! – Rajesh S Aug 10 at 9:54
  • Maybe I should have separated the list of names and the output. Jacob's three languages are the first 3 listed: English, Spanish, and Portuguese. I just need to generate a list of all of the languages a group of six is able to speak. If Jacob were the only one in the list, then the list would simply read "English, Spanish, Portuguese". If the list consisted of only Jacob and Sally, it would read "English, Spanish, Portuguese, French". I hope that helps clarify. – C. Pettit Aug 10 at 10:00
  • Check your table in A2:E11 and try to match with Output in I2:I11, for Jacob is correct English but for others like Sally, Susan & Julia language names are not in source data A2:E11?? – Rajesh S Aug 11 at 6:44
0

I couldn't come up with a formula, but you can do this with a VBA macro. You will have to trigger the macro to run.

In case you are unfamiliar, to enter this Macro (Sub), alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens.

To use this Macro (Sub), alt-F8 opens the macro dialog box. Select the macro by name, and RUN.

The macro assumes your name/language table starts in A1, and that your Names column is somewhere in Row 1. And the header of that column must be Names. In the macro that can be changed in various ways.

Also, you will have to ensure that WS refers to the correct worksheet.

Option Explicit
Sub langsKnown()
    Dim WS As Worksheet
    Dim vSrc As Variant, vNames As Variant, vLangs As Variant
    Dim rLangs As Range, C As Range
    Dim dLangs As Object, dNames As Object
    Dim V
    Dim I As Long, J As Long
    Dim sLang As String

Set WS = Worksheets("sheet4") 'or whatever

'Read language table into variant array
'  many ways to do this
'  Assuming it starts in **A1**'
vSrc = WS.Cells(1, 1).CurrentRegion

'Read Names into array
With WS.Rows(1)
    'Find the Names column
    'Assuming it starts in Row 1
    Set C = .Find(what:="Names", after:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole, _
                    searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True)
    If Not C Is Nothing Then
        Set rLangs = C.Offset(0, 1)
        With WS
            vNames = .Range(C.Offset(1, 0), .Cells(.Rows.Count, C.Column).End(xlUp))
            If Not IsArray(vNames) Then 'this will be true when there is only a single name
                V = vNames
                ReDim vNames(1 To 1, 1 To 1)
                vNames(1, 1) = V
            End If
        End With
        If IsEmpty(vNames(1, 1)) Then
            MsgBox "No Names"
            Exit Sub
        End If

        'Read Names into dictionary for easy lookups
        Set dNames = CreateObject("Scripting.Dictionary")
            dNames.comparemode = vbTextCompare
        For Each V In vNames
            If Not dNames.exists(V) Then _
                dNames.Add V, V
        Next V
    Else
        MsgBox "No Names"
        Exit Sub
    End If
End With

'Collect relevant languages into dictionary
Set dLangs = CreateObject("Scripting.Dictionary")
    dLangs.comparemode = vbTextCompare

For I = 2 To UBound(vSrc, 1)
    If dNames.exists(vSrc(I, 1)) Then
        For J = 2 To UBound(vSrc, 2)
            sLang = vSrc(I, J)
            If Len(sLang) > 0 And Not dLangs.exists(sLang) Then _
                dLangs.Add sLang, sLang
        Next J
    End If
Next I

'write results to VBA array
ReDim vLangs(0 To dLangs.Count, 1 To 1)

'Header
vLangs(0, 1) = "Languages Spoken by Group"

'Data
I = 0
For Each V In dLangs.keys
    I = I + 1
    vLangs(I, 1) = V
Next V

'Write to the worksheet
Set rLangs = rLangs.Resize(rowsize:=UBound(vLangs, 1) + 1)
With rLangs
    .EntireColumn.Clear
    .Value = vLangs
    .Rows(1).Font.Bold = True
    With .EntireColumn
        .AutoFit
        .HorizontalAlignment = xlCenter
    End With
End With

End Sub

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.