blooddiamondkim
New Member
- Joined
- May 10, 2016
- Messages
- 2
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:
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:
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
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:
| Doc Date | Doc Number | Cost Element | Amount | New Network | New Activity | Current NW | Current Activity |
| 2016-02-10T00:00:00 2016-05-26T00:00:00 2016-05-18T00:00:00 | 1234567890 5468546314 568921345 | 511965 5299.8555 710007 | 2000 3211.52 4000 | 987654321987 100001234567 100001216899 | 0100 0090 0020 | 123456789123 100001215689 100000659822 | 0060 0080 0100 |
| 2016-02-29T00:00:00 | 12115589 | 960209 | 3000 | 100005678910 | 0020 | 100001234567 | 0010 |
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:
| Doc Date | Doc Number | Cost Element | Amount | New Network | New Activity | Current NW | Current NWA |
| 20160210 | 1234567890 | 511965 | 2000 | 987654321987 | 0100 | 123456789123 | 0060 |
| 20160526 | 5468546314 | 5299.8555 | 3211.52 | 100001234567 | 0090 | 100001215689 | 0080 |
| 20160518 | 568921345 | 710007 | 4000 | 100001216899 | 0020 | 100000659822 | 0100 |
| 20160229 | 12115589 | 960209 | 3000 | 100005678910 | 0020 | 100001234567 | 0010 |
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
