SharePoint Export from Infopath to Excel - How to Split Multiple Lines of Data into Separate Rows?

[blooddiamondkim]

I am desperately hoping someone can help me, as I've repeatedly run into a wall for about two weeks now trying to solve this problem and I've finally admitted to myself that a solution is beyond my scope of knowledge. I should note I'm working in Excel 2010.

I have an InfoPath form that submits data in repeating tables to SharePoint. People export the SharePoint list data to Excel and need it formatted a certain way; the problem is that each column in a repeating table pulls all of the entered data into one cell separated by carriage returns (ALT+ENTER) when viewed in Excel.

The number of items in each cell is variable (depending on how many repeating table fields the person fills out), but the number of items across cells stays the same. For example, cell A1 has 3 items in one cell, but that means that columns B - H also have three items in each cell.

Each export can have hundreds of line items - it will increase throughout the year as more forms are submitted, so the number of lines is unknown. The number of items in each cell, as mentioned above, is unknown.

An example of how the data exports into Excel is below:

[table="width: 500, class: grid"]
[tr]
[td]Doc Date[/td]
[td]Doc Number[/td]
[td]Cost Element[/td]
[td]Amount[/td]
[td]New Network[/td]
[td]New Activity[/td]
[td]Current NW[/td]
[td]Current Activity[/td]
[/tr]
[tr]
[td]2016-02-10T00:00:00
2016-05-26T00:00:00
2016-05-18T00:00:00
[/td]
[td]1234567890
5468546314
568921345
[/td]
[td]511965
5299.8555
710007
[/td]
[td]2000
3211.52
4000
[/td]
[td]987654321987
100001234567
100001216899
[/td]
[td]0100
0090
0020[/td]
[td]123456789123
100001215689
100000659822
[/td]
[td]0060
0080
0100[/td]
[/tr]
[tr]
[td]2016-02-29T00:00:00
[/td]
[td]12115589
[/td]
[td]960209
[/td]
[td]3000
[/td]
[td]100005678910[/td]
[td]0020[/td]
[td]100001234567
[/td]
[td]0010[/td]
[/tr]
[/table]

I need the cell content split into separate rows with the formatting needs listed on the "Final" tab's headers, and with Doc Date formatted as YYYYMMDD; the macro needs to continue until the export data has all been split; I've tried recording a macro that:

1) Copies a row to "Working" tab
2) Goes column by column with text to columns, setting "TEXT" format for the defined columns (noted on the "Final" tab) transpose, then paste values into "Final" tab to use as the final summary
3) Changes "Doc Date" formatting to YYYYMMDD format

Essentially, it needs to come out looking like this:

[table="width: 500, class: grid"]
[tr]
[td]Doc Date[/td]
[td]Doc Number[/td]
[td]Cost Element[/td]
[td]Amount[/td]
[td]New Network[/td]
[td]New Activity[/td]
[td]Current NW[/td]
[td]Current NWA[/td]
[/tr]
[tr]
[td]20160210[/td]
[td]1234567890[/td]
[td]511965[/td]
[td]2000[/td]
[td]987654321987[/td]
[td]0100[/td]
[td]123456789123[/td]
[td]0060[/td]
[/tr]
[tr]
[td]20160526[/td]
[td]5468546314[/td]
[td]5299.8555[/td]
[td]3211.52[/td]
[td]100001234567[/td]
[td]0090[/td]
[td]100001215689[/td]
[td]0080[/td]
[/tr]
[tr]
[td]20160518[/td]
[td]568921345[/td]
[td]710007[/td]
[td]4000[/td]
[td]100001216899[/td]
[td]0020[/td]
[td]100000659822[/td]
[td]0100[/td]
[/tr]
[tr]
[td]20160229[/td]
[td]12115589[/td]
[td]960209[/td]
[td]3000[/td]
[td]100005678910[/td]
[td]0020[/td]
[td]100001234567[/td]
[td]0010[/td]
[/tr]
[/table]

The macros I have tried recording on my own stop working because of the variability of the number of line items in each cell. No matter what I have tried, I cannot get it to work and I have realized maybe this needs to be VBA, which is far beyond the scope of my abilities. I feel defeated by this issue and desperately hope someone can help me out. I would greatly appreciate it.

A link to the workbook itself is below:

https://onedrive.live.com/redir?res...468&authkey=!ABCQiMxPVmQU_g4&ithint=file,xlsm