Return multiple values for one person

Microsoft Office access 2007 (upgrade, p...
September 7, 2010 at 08:08:11
Specs: Windows XP
I have an excel spreadsheet or access database with 3 columns:
"ID#"; "Name"; "Case#"
1 Jon Smith 123
1 Jon Smith 321
2 Jane Doe 456

How can I run a formula or query to return all the case numbers associated with a particular person? For Example:

Name Cases
Jon Smith 123, 321
Jane Doe 456

See More: Return multiple values for one person

Report •

September 7, 2010 at 08:14:05
I would like to clarify that I need the returned values (cases) to be contained within a single cell.

Thank you!

Report •

September 7, 2010 at 08:37:01
I can't speak to an Access Query, but I can comfortably say that there is no Excel formula that will easily get you the results you want.

A fairly simple VBA macro could be written to search for each unique name and build a string of Case # ' s which could then be placed in a cell next to each name.

Before I offer any code, I'll tell that this will work in Excel only, so I need to know if that's what you want.

Report •

September 7, 2010 at 08:41:04

I am working in both Excel and Access trying to figure out which one can easily perform this task for me. Your solution sounds exactly like what I am trying to accomplish. Please advise.

Report •

Related Solutions

September 7, 2010 at 11:11:45
The following code assumes the input data you supplied is in Columns A:C.

It also assumes you have room in Column K and L for the output data.

The code will create a list of unique names in Column K, loop through the original list finding all occurrences of each name and build a string of Case Numbers which it will place in Column L.

Adjust as required.

Option Explicit
Sub GroupCaseNums()
Dim Last_Name, last_uName, nxtName As Integer
Dim caseStr, firstAddress As String
Dim c As Range
'Find last Row with data in Column B
 Last_Name = Range("B" & Rows.Count).End(xlUp).Row
'Filter Unique names into Column K
   Range("B1:B" & Last_Name).AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=Range("K1"), Unique:=True
'Find last Row with data in Column K
    last_uName = Range("K" & Rows.Count).End(xlUp).Row
'Set range for Find
     With Range("B1:B" & Last_Name)
'Loop through list of unique values
       For nxtName = 2 To last_uName
'Find each unique name in original list
         Set c = .Find(Cells(nxtName, "K"), LookIn:=xlValues, _
          If Not c Is Nothing Then
           firstAddress = c.Address
'Build Case Number String, add comma and space after each Case Number
              caseStr = caseStr & c.Offset(0, 1) & ", "
              Set c = .FindNext(c)
             Loop While Not c Is Nothing And c.Address <> firstAddress
          End If
 'Strip off last comma and space, place Case Number String in Column L
        Cells(nxtName, "L") = Left(caseStr, Len(caseStr) - 2)
 'Clear Case Number String
        caseStr = ""
 'Do it all again
     End With
End Sub

Report •

September 8, 2010 at 07:48:52
The VBA code did exactly what I wanted it to do! Thanks for the knowledge on this, I greatly appreciate it!!!

Report •

September 8, 2010 at 08:01:06
Glad I could help.

Report •

Ask Question