Excel formating from SharePoint List / Infopath

[TonyO76]

Hi –

I have a SharePoint list which is populated by an InfoPath form. On completion of the form, it is printed as a PDF.

The fields within the form are set up something like this;

ID
Name

Food
Category
Comments

Food
Category
Comments

Food
Category
Comments



The data held within the list is exported to Excel for analysis against all other forms in the list.

A new row is produced within Excel for each form submitted, so the data is presented as follows

ID	Name	Food	Category	Comments	Food	Category	Comments	Food	Category	comments
1	John	apple	fruit	green	carrot	vegetable	orange	salmon	fish	pink

<tbody>
</tbody>

For the purpose of analysis I need the data to be shown as follows.

[TABLE="width: 381"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Name
[/TD]
[TD]Food
[/TD]
[TD]Category
[/TD]
[TD]Comments
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]John
[/TD]
[TD]apple
[/TD]
[TD]fruit
[/TD]
[TD]green
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]carrot
[/TD]
[TD]vegetable
[/TD]
[TD]orange
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]salmon
[/TD]
[TD]fish
[/TD]
[TD]pink
[/TD]
[/TR]
</tbody>[/TABLE]


I have looked into the following options without much luck;

• Send the Data to MS Access, format a report and export to Excel – problem is that the report does not retain its formatting when exported.
• I have looked at some formatting options in Excel including creating pivot tables and slicers from the raw data.
• Looked at changing the SharePoint List views, and InfoPath controls

The restraints I have are as follows.

• The data must be collected from a SharePoint list.
• All data must collected in the same form.
• The data has to be exported to Excel for analysis.

Hoping someone can offer some advice.

Thanks in advance.