Forum Discussion
Excel formulas to find and consolidate data points from multiple work sheets
Hi everyone, I'm really hoping someone can assist.
Background:
I am attempting to migrate some data from a SharePoint 2010 wiki page library to a new O365 list. The SharePoint 2010 data consists of a number of data points relating to a customer, entered as text onto multiple wiki pages (about 100 of them). In order to migrate to the new O365 list, I want to segment each data point into individual rows, for better data management.
Data points include the customer's phone number, email address, support guidelines, etc.
To start, I am cutting and pasting each page of data into separate sheets in a spread sheet. The data points are inconsistently organized on the originating wiki pages, so they appear in slightly different cells in many of the work sheets. Each worksheet is simply named 1, 2, 3, etc. One page for each customer's data. I am then attempting to collate each data point into rows in a new 'Collation' work sheet, using the following calculation:
=IFERROR(INDEX('1'!$A$1:$A$52, MATCH(0, INDEX(COUNTIF($A$1:A2, '1'!$A$1:$A$52), 0, 0), 0)), "")
From there, I need to re-order the information into a consistent format, so it can be migrated. I have mocked up a 'Final' tab to achieve this, with examples\subsets of the types of data I need to be migrated, and how it should look.
As an example of the calculations I need:
1) In the 'Final' tab, cell A4 needs to look at each work sheet, find occurrences of the string 'Hours of Operation':, and copy the text in the same cell after that, to 'Final'!A4.
2) In the 'Final' tab, cell A7 needs to look at each work sheet, find occurrences of the string 'Priority', and copy the text from the cell DIRECTLY BELOW the cell it found with Priority in it, to 'Final'!A7.
If I can get some help determining the two formulas above, I can tweak them slightly for each data point & it's location. I've attached the spread sheet with the customer data & mock-up below.
It may be that my interim step of collecting & organizing data into columns in the 'collation' work sheet isn't needed, and someone could assist with a more elegant solution. Any help would really be appreciated!
2 Replies
Michael__HutchensSharepoint came along after I retired from the workplace, so this may be a completely ignorant question: Is it not possible for your Sharepoint data to be exported as an Excel Table?
A search on-line led me to this page, where it certainly seems that the answer is "Yes."
mathetes, thanks for the feedback. I can only export the data I need into Excel from a SharePoint list, not a wiki page library. A wiki page library does have an 'export to excel' function, but you only end up with metadata about the wiki pages themselves, not the actual content.
(I've edited the original post to make this clearer, as in retrospect I had done a poor job of it)
