Monday, July 12, 2010

Export SharePoint 2007 List with Multi Selection Lookup Column into Excel

Whether you use MOSS or WSS 3.0 lists that include multi-selection items, you may find it hard to manipulate the column's items in Excel. For example, if you try filtering by a specific value, any cell with multi item values won't show because it is not identical to the filter value. Excel 2007 includes all the multi selection values in the same cell and treats all the text inside the cell as a single long string. Another issue is that the ;#n; (where n denotes the item-ID) shows next to the lookup.
There is a solution, though not a perfect one, for this issue. You can add to the right side of the Excel table as many columns as the # of values in the dropdown list, and apply formulas to strip one value at a time into a column. Then you can filter the table by the calculated columns as you do for any other table's columns.
This would nicely solve 10-30 list items but any time you have a new item in the drop down list you will have to add a column for that item.

Here is an example using Excel 2007 though it should work for 2003 as well:

The are two tables (lists) exported: the main one and the dropdown.
The exported list had the project column with multi-item selection. Each selection has a value and next to it its ID separated by a semicolon. One of the values is 'Estimating; #1.'
In a new column named Proj-1 you can see all rows that have the value 'Estimating.' You will have to create a separate column for each separate value as explained below.
 

The Project column is associated with the ProjectList dropdown list: (this is an excerpt from the entire list).











For each entry in the selection list (ProjectList) you prepare a column (proj-1, proj-2 etc.) and enter the formula below:







=IF(RIGHT(Source!$B2,3)=";#1",VLOOKUP(1,ProjectList!$A$1:$D$19,2,FALSE),IF(ISERROR(SEARCH(";#1;",Source!$B2)),"",VLOOKUP(1,ProjectList!$A$1:$D$19,2,FALSE)))

 The formula first tests whether that last item in the string is our item (which is ;#1 for Estimating). if not it searches for the string ;#1;. upon failure the cell will stay empty otherwise it will contain 'estimating.'

although estimating can be obtained directly from the cell or entered as a string, I found it easier to export the list and vlookup it.

Of course, for the next value you should test #2 and the lookup will be for 2. When testing for IDs greater than 9 you will need to use RIGHT(Source!$B2,4) as you need to test 4 characters.

To improve the process you can use the row() function to further automate the formula but I didn't want to add another complexity.

1 comment:

  1. Please elaborate on your solution, its difficult to catch...

    ReplyDelete

Enter your comment here