How would you Handle rows greater than excels limit?
After searching he sub, I couldn't find a complete answer.nWould be grateful if anyone replied or just pointed me to a source where I can learn. So I have two queries,
- Let's say, I have some excel files in a folder - all with one sheet and all have the same columns and formatting. Now when I combine these using power query I get data beyond excel's row limit. I have been combining first few files, copy pasting them in a new finaldata file in sheet1, them continue for sheet 2,3 and so on. Result is the final data file with 4/5 sheets. If I only want to use excel is there a way to automate this with VBA and powQuery?
- There are multiple excel files in different sharepoint or teams channel locations. I have to pull few columns from each file into one master data file. I have been using xlookup in my master file as it automatically updates when the original data is updated. While this has been functional the resulting master file is often times slow and sometimes and lookup formula needs to be double clicked by me so that it is applied again to the whole column. Is there a more efficient way of doing this or is it fine?
Also, I have learned alot from just lurking and searching posts here. Thank you everyone.