Bad Data Pull, trying to correct by pulling select Data into a new sheet. Row Help Needed

Copper Contributor

Good morning,

We purchased a new company and received a data pull from their system (Solomon) so we can add the customers into our Database (Epicor P21). I am using the =[AR_CustomerListing.xlsx]Sheet1!$B$108 formula to add the data to the new spreadsheet.  The problem is, the data sets seems to be 10-12 lines apart (i.e. first company name is B2 and second company name is B15) We also have the issue that the columns are not where the data presents itself.   Is there a way to fill down my spreadsheet and have excel pull data only every 10 lines (or more that would be manually changed)? We do not want to give this file to our team to work off of as it is garbage but we have to have 600+ customers in our system by January 4. Any help or advise would be greatly appreciated.  Our IT team said there is nothing more then can do with the file. (Microsoft Office Home and Business 2019)Solomon Data .pngcorrected sheet .png

3 Replies

@MLewis72 I'd suggest you have your IT people look into PowerQuery. A tool, integrated in Excel since 2016, which is very capable of transforming "garbage" data to something useful. Exactly how is difficult to say without having the file to play with.