445 Columns sounds like a large Excel file! I recall trying to merge several large spreadsheets for my local SCADA department where one of them had 333 columns and 95,000 rows. (It's the one that is 110mb in this screen-shot!)
My job was to convert .csv files that the SCADA equipment generated into searchable Excel Workbooks. What I didn't expect was that there was often sparse data (both horizontally and vertically) between (sometimes several) different .csv files that I had to
merge into one, because it was data for the same SCADA equipment but just at different times of the year (or different years . . . before they had sufficient funds to get more equipment . . . or when old equipment broke or became obsolete).
Unlike what it sounds like with your case (
but we can't be sure unless you are), the columns were literally missing for one month (the equipment was turned off, was being renovated, etc.), and sometimes new columns (new/updated equipment) were in the sheets. The point being, I literally had to write a
mapping VBA script to ensure that I got the heading name of every column in ALL sheets that I was to merge together as one:
(Those file paths were the literal names of the column headings . . . they represented the location of the equipment in the city.)
So, anyway, what I
also learned, was that manipulating such
large files can be hazardous if one doesn't handle them with caution. Due to their size, removing columns and rows can make it appear as if Excel is going to crash. So by your description, it appears that you know this and would like to minimize the amount of "fidgeting" with these large files as much as possible.
In the mapping image above, I wrote a search routine which would search every Excel file that needed to be merged. I gave every heading an index number. In the image above, you can see the index numbers in the middle. They happened to be in the same order for the two .csv files in view, but that wasn't always the case! (That was the scary part. It was unpredictable!) So,
unfortunately, if you want to be on the safe side, you will probably want to:
- Either write (or have someone write) a mapping function like this for you for the 2 sheets. Have it loop through all headers in each of the sheets and give the headings (by their exact title, verbatim) an index number. That means you need to pretend/consider the one from Smart View as a 3rd sheet and not "Smart View". (No pun intended.) Besides an index to identify the headings, you need to choose one of them (in this case, Smart View) to be the one that's the "frame of reference" . . . the one that gets priority. So for example, if the sheet from Smart View's columns are offset differently from either (or all) sheets, you need to record the row and column offsets that each unqiue column heading is, with respect to the Smart View sheet.
- Once you have indexed all headings and have calculated their offsets, now comes the code to actually rearrange the columns in the 435 and 445 column spreadsheets that are in house. As I mentioned earlier, fidgeting with such LARGE Excel files can cause Excel to crash. Even worse, it can cause corruption of data if a solid algorithm isn't developed. That being, an algorithm which is a healthy balance between CPU usage and RAM usage . . .
- We need to give the computer time to release previously used memory ^^^ (so between shifting X number of columns, you might want to add in a subroutine which lets the computer reset for 2 seconds before resuming the operation, for example), but it's also important to pay attention to setting objects (like Worksheets and ranges) to variables (memory) if you don't have to.
But it looks like you have ANOTHER problem to deal with. The headings themselves:
and the TRIM function does not work here because the space or gap is above the data, not before or after the data
Take a look at the comments of this Function I have in my VBA project library:
VBA Code:
'Note that Trim( is used to remove white spaces at the beginning and end of strings.
'And from here, https://strugglingtoexcel.com/2018/08/16/clean-non-printing-characters-excel-vba/
'"Note that I use Excel’s TRIM Function instead of VBA’s TRIM Function.
'VBA’s function removes only leading and trailing spaces, while Excel’s function also replaces
'multiple spaces with a single space."
Sub Test__Remove_All_Non_Printing_Characters_Except_For_White_Spaces()
Debug.Print Remove_All_Non_Printing_Characters_Except_For_White_Spaces(Chr(8) & " " & Chr(10) & "AA" & Chr(10) & "BE" & " ")
End Sub
Function Remove_All_Non_Printing_Characters_Except_For_White_Spaces(str As String)
'Important note from: https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.clean
'The Clean function was designed to remove the first 32 nonprinting characters
'in the 7-bit ASCII code (values 0 through 31) from text. In the Unicode character set,
'there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157).
'By itself, the Clean function does not remove these additional nonprinting characters.
Remove_All_Non_Printing_Characters_Except_For_White_Spaces = Application.WorksheetFunction.Clean(str)
End Function
So other functions besides trim exist and can be used to better compare the literal text of headings. I would say that it may even be smart to make the characters in all header cells the same CASE, just in case of a case. You know? (Hehe.)
I think the next step for you to take is to get just a gist of the differences between the sheets. Just try to find two (three) matching headings and see if they differ and how they differ. But yeah,
if it is unpredicable of the order of the headings . . . they keep CHANGING/adding/removing header names, you will have to write an mapping function of some kind like I mentioned to be on the safe side. And you cannot use "the most efficient code" for such LARGE documents. It needs to be purposely SLOWED DOWN (with reasonable time increments) to ensure no crashes and chances of corrupt files/data.
And if it's bad enough, you may need to
split up the files into smaller pieces/sheets. (It won't make a difference if you use a mapping function anyway.) So for example, if you wanted to split these BIG sheets into 3 parts, programmatically make 3 copies of each file. Delete the contents
but not the columns! of the first 3rd of the the columns in the sheet. For the second copy, delete the middle third's column's contents (but NOT the columns). And for the last . . . okay, you get it.