How to create a report grouped by a multiple value field from a list in Sharepoint Server 2007 (or in Excel or Access?)

Problem: A list in sharepoint contains a people picker field called "Assigned To" that allows for multiple values. Based on my research, Sharepoint does NOT allow for grouping of fields that allow

for multiple values. I'm stuck between a rock and a hard place. I need to run a report, create a sharepoint view, an access DB, excel spreadsheet, or perform some kind of repeatable process in

order to get some kind of document/report that allows me to have my list grouped by each individual that's in the "Assigned To" field.

So, for example, the sharepoint list is setup similar to this (there are a lot more fields in the list, but I'm simplfying here):

Case Name      Assigned To
Case 1            John, Patrick
Case 2            Larry, Curly, Patrick
Case 3            Patrick, Maria, Lindsey
Case 4            Larry, John

I somehow need a way to create a sharepoint view/run a report/create some kind of document that reads as follows:

Assigned To: Larry
      Case 2
      Case 4

Assigned To: John
      Case 1
      Case 4

Assigned To: Patrick
      Case 1
      Case 2
      Case 3

Assigned To: Curly
      Case 2

Assigned To: Maria
      Case 3

Assigned To: Lindsey
      Case 3

Could someone please point me into the right direction to get this done? I've tried exporting the list out to Excel from Sharepoint and then using the "Text to Column" function to split the

Assigned To field. The People Picker is assigned to our Active Directory. When values are exported Excel, they're done in the following manner:

One User Selected --> USERNAME;#USERID
Multiple Users Selected --> USERNAME1;#USERID1;#USERNAME2;#USERID2;#USERNAME3;#USERID3; etc.

Any help anyone may offer is greatly, greatly appreciated. I really need some :-D

Who is Participating?
redmondbConnect With a Mentor Commented:
Hi, 26fconsulting.

Please run the Text_Columns macro in the attached. A few points...
(1) It's still in a crude form, e.g. there's hard-coding in a few places which means that it only expects the two columns you've shown above. Obviously this will be changed if you're happy with the principle.
(2) The output can easily be pivoted to produce your required end-result - but hasn't been. See point (1).
(3) If you want to rerun Text_Columns then you must first delete the "Groups" sheet, the extra columns in the "Data" sheet and save the file. See point (1).

26fconsultingAuthor Commented:
Hi redmondb,

I ended up writing something in VBA to generate this report. Thank you for your time for working on this -- you get the points for responding.

Thanks, 26fconsulting. Happy you're sorted!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.