Hello caluflower and Welcome to Excel Forum.
Since you are using the 2016 version of Excel, you may be able to resolve the issue using the built in Get & Transform feature (Power Query).
To be of any real help we need to see a sample of the .xlsx file with which you are working (instructions are in the banner at the top of the page).
Let us know if you have any questions.