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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.