Matching multiple values in Excel

Question

I am trying to get a nice list from a data sheet. My data looks like this

HospitalCode   Name    SHAK   Subdivision    Specialty1    Specialty2   Specialty3    Specialty4
1301           Rich    5435   Copenhagen     84            65
1301           Rich    4434   Heart med      91            44           22
1301           Rich    9944   Lung med       33                         99
1309           Bisp    4324   London         32
1309           Bisp    8483   GP             21            44                          22
...

And so on for approximately 4000 rows. What I need is an output of each hospital code and a list of all the unique specialties on the specific hospital. Something like this

Hospital code    Specialty1     Specialty2    Specialty3 ... Specialty99
1301             84             65            91         ... 33
1309             32             21            44 

Where Specialty99 is just chosen to indicate, that I need all specialties that are connected to the specific hospital code. I have tried vlookup, but naturally this just gives me the first value. I do not understand sumproduct, but maybe it can be of use here? All help will be greatly appriciated. Have a nice day.

Answer 1

I think VBA may be your best solution, since Pivot tables will not help find unique values over multiple columns, like Spec1, Spec2 etc.

As far as VBA goes, this is pretty basic looping - the only tricky bit is the uniqueness. To handle that, I'm using a Collection object - these can be used to get unique values, since it won't let you add a second copy of the 'key'.

This solution also presumes that your data is sorted by HOSPITAL_CODE (which it looks like from your example). If not, please sort it before running this code

Here is a working sample workbook

Sub makeTable()

    Dim rngHospId As Range
    Dim rngSpec As Range
    Dim listOfSpecs As New Collection
    Dim hosp As Range
    Dim spec As Range
    Dim wsOut As Worksheet

    'Settings - change these for your situation
    Set wsData = Worksheets("Data")
    Set rngHospId = wsData.Range("A2:A7") ' Single column with Hosp IDs
    Set rngSpec = wsData.Range("B2:F7") 'All columns with Specialties

    'Create new Worksheet for output
    Set wsOut = Worksheets.Add(After:=wsData)
    wsOut.Range("A1") = "Enter Headers Here ..."

    'Process each row
    outRow = 2 'row to print to in output
    For i = 1 To rngHospId.Cells.Count
        Set hosp = rngHospId(i, 1)
        'Add every specialty from the current row
        For Each spec In Intersect(rngSpec, hosp.EntireRow)
            If spec.Value <> "" Then
                On Error Resume Next
                    'Entries will only be added if unique
                    listOfSpecs.Add spec.Value, CStr(spec.Value)
                On Error GoTo 0
            End If
        Next spec

        'If last row for a hospital, output the final list of specs
        If rngHospId(i + 1).Value <> hosp.Value Then
            'Output
             wsOut.Cells(outRow, 1) = hosp.Value
             cnt = 0
            'Print all elements of collection
             For Each entry In listOfSpecs
                cnt = cnt + 1
                wsOut.Cells(outRow, 1 + cnt) = entry
             Next entry
            'Clear Collection
             Set listOfSpecs = Nothing
             Set listOfSpecs = New Collection
            'Move to next row
             outRow = outRow + 1
        End If
    Next i

End Sub

Answer 2

you can use SUMIF to create this, simply create a new tab for the totals.

i've uploaded a basic example on the following link:

here