Column Aligned - Smart View (Essbase Download)

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,831
Office Version
  1. 365
Platform
  1. Windows
I need to compare two worksheets’ multiple rows headings. One worksheet has 435 columns, and the other has 445, so ten extra columns and ten extra headings are in between 445 columns. I need to align those headings so I can retrieve the data from Smart View, and compare the data. But there is another problem, in one of the sheets, the departments have gaps above them because this is a retrieve of Smart View. So even though the headings are the same, it shows not equal, and the TRIM function does not work here because the space or gap is above the data, not before or after the data.

Please advise the best way to align the two sheets’ headings so for example, B1 of sheet 1, should be the same as B1 of sheet 2, and so, B2, B3, ….QE1 = QE1 (of another sheet)

Should show TRUE.

Thanks
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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!)

big files.jpg


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).
Missing Data Screen-shot.jpg


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:
mapping.jpg

(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 . . .
Not enough memory.png

  • 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.
 
Upvote 0
Solution
It just came to mind that I have already written a generic "column shifter": VBA function to shift columns by variable value starting with variable column number It shifts any subset of rows in either the left or right direction (and more importantly, it does this by actually transferring the values and/or formatting, rather than actually moving the rows, which is very RAM expensive), and it determines which direction to shift the rows by (already calculated) horizontal offsets that are in a specific column. Your case would probably require around 20 or so of these shifts (which can be done in blocks = make the horizontal shifts for all rows the same), and thus you could use it as a subroutine if you would first make a column heading index subroutine (as I suggested above) to ultimately get the offsets.
 
Upvote 0
Hi cmowla,
I appreciate you taking the time to respond to me in detail, and with your experience. I have worked with SCADA results while working for oil and gas companies.
Thanks for the code, but unfortunately I can not use VBA as this file is used by other people as well, but I learned something from your post.
Thanks again for your time and effort.

Best regards,
 
Upvote 0
but unfortunately I can not use VBA as this file is used by other people as well
You don't need to put the VBA in the file itself. The VBA can go into a separate "program" Excel Workbook. (Excel Workbooks as a whole can be taken as arguments in given parameters, and others just used to run code.) The way it would work could be, the starter (main) VBA subroutine would make a copy of all Excel Workbooks to be merged. Then it would manipulate each of them. (This way, you don't have to set any of them to RAM.)

This way, the VBA stays out of the actual files themselves. The "program" Excel Workbook just has the VBA code in it, but only one (blank) sheet, like "Sheet1".

If you were thinking that using dynamic array formulas as a possibility, sure it is possible to write some long dynamic formula(s) to do this, but when the range that they manipulate is this LARGE, it's going to be much more RAM expensive than VBA ever could be.

But if you have another option (software) to use, I'm not insisting on VBA. But I assumed you were requesting VBA, because this is an Excel forum.
 
Last edited:
Upvote 0
Thank you very much,
I haven’t tried it yet, but I will at my work on Monday.
Most regards,
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top