SOLVED

Compiling Data from SharePoint Export in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-3074038%22%20slang%3D%22en-US%22%3ECompiling%20Data%20from%20SharePoint%20Export%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3074038%22%20slang%3D%22en-US%22%3E%3CP%3EAll%2C%3CBR%20%2F%3EI%20appreciate%20anyone's%20thoughts%20on%20this%20problem%20I%20am%20having%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20SharePoint%20list%20that%20exceeds%205K%20items.%20I%20have%20built%20a%20number%20of%20filtered%20views%20to%20ensure%20that%20no%201%20view%20exceeds%205K%20(the%20ceiling%20for%20a%20view%20in%20our%20environment%20is%205K).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20one%20spreadsheet%20that%20I%20use%20to%20export%20all%20of%20the%20data%20from%20the%20various%20views%20to%20separate%20worksheets.%20I%20would%20like%20to%20automate%20a%20function%20that%20would%20compile%20the%20data%20from%20the%20various%20spreadsheets%20into%201%20collective%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20formulas%20that%20can%20pull%20the%20data%20from%20the%20exported%20sheets%20but%20ideally%20I%20would%20like%20to%20place%20the%20formulas%20in%20successive%20rows%2C%20so%20that%20no%20additional%20sorting%20would%20be%20needed.%20When%20I%20place%20the%20formulas%20in%20successive%20rows%20I%20get%20the%20spill%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20a%20PC%20with%20Excel%202008.%20I%20have%20some%20experience%20with%20excel%20formulas%20but%20none%20with%20VBA.%3C%2FP%3E%3CP%3EAny%20recommendations%20are%20appreciated.%20Thank%20you!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Export%20Compile%20Problem.png%22%20style%3D%22width%3A%20549px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342974i911825B9776D4A32%2Fimage-dimensions%2F549x287%3Fv%3Dv2%22%20width%3D%22549%22%20height%3D%22287%22%20role%3D%22button%22%20title%3D%22Export%20Compile%20Problem.png%22%20alt%3D%22Export%20Compile%20Problem.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3074038%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3077428%22%20slang%3D%22en-US%22%3ERe%3A%20Compiling%20Data%20from%20SharePoint%20Export%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3077428%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1288905%22%20target%3D%22_blank%22%3E%40HopelessGent%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20all%20your%20lists%20have%20the%20same%20structure%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fgetting-started-with-get-transform-in-excel-a8310388-2a12-438c-9d29-c6d29cb8df6a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EGet%20%26amp%3B%20Tranform%3C%2FA%3E%20aka%20Power%20Query%20is%20your%20friend%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1%2F%20For%20each%20list%2Ftable%3A%3CBR%20%2F%3E-%20Go%20to%20Data%20(tab)%20%26gt%3B%20From%20Table%2FRange%20(this%20opens%20the%20Power%20Query%20editor)%3CBR%20%2F%3E-%20Click%20on%20the%20little%20arrow%20down%20next%20to%20Close%20%26amp%3B%20Load%20%26gt%3B%20Close%20%26amp%3B%20Load%20To...%20%26gt%3B%20Check%20Only%20Create%20Connection%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2%2F%20Once%20all%20your%20connections%20are%20established%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fappend-queries-power-query-e42ca582-4f62-4a43-b37f-99e2b2a4813a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAppend%3C%2FA%3E%20all%20queries%20as%20a%20single%20table%20that%20you'll%20load%20on%20a%20new%20worksheet%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

All,
I appreciate anyone's thoughts on this problem I am having:

 

I have a SharePoint list that exceeds 5K items. I have built a number of filtered views to ensure that no 1 view exceeds 5K (the ceiling for a view in our environment is 5K).

 

There is one spreadsheet that I use to export all of the data from the various views to separate worksheets. I would like to automate a function that would compile the data from the various spreadsheets into 1 collective sheet.

 

I have formulas that can pull the data from the exported sheets but ideally I would like to place the formulas in successive rows, so that no additional sorting would be needed. When I place the formulas in successive rows I get the spill error.

 

I am using a PC with Excel 2008. I have some experience with excel formulas but none with VBA.

Any recommendations are appreciated. Thank you!

Export Compile Problem.png

2 Replies
best response confirmed by HopelessGent (New Contributor)
Solution

Hi @HopelessGent 

 

Assuming all your lists have the same structure Get & Tranform aka Power Query is your friend

 

1/ For each list/table:
- Go to Data (tab) > From Table/Range (this opens the Power Query editor)
- Click on the little arrow down next to Close & Load > Close & Load To... > Check Only Create Connection

 

2/ Once all your connections are established Append all queries as a single table that you'll load on a new worksheet

@L z.   Thank you so much!  This worked beautifully.

HopelessGent_0-1643654342968.png