Do the Columns Shift on You from Download to Download?

Most of the time, when I do a download of data, the numbers all fall in nice orderly columns, and if I have to do multiple downloads, the data consistently fall into the same columns, from download to download. However, when I do a download from a software called OneSite, the numbers can shift from download to download. Say in download 1, your field called names falls under column C but in download 2, it falls under column E. This variation makes it very difficult to set your spreadsheet to pull numbers from the same spot because it changes from download to download.

But I have figured out an ingenious way to make the numbers line up the way I want them to.

I’m trying to compare data from OneSite with data in the new software to make sure that the data in the new software is correct (we’re migrating data). First thing you need to do is set up Excel to figure out which columns hold the data and number those columns. In my case, the header fields also did not lie in the same column as the data, so that was an additional issue. If your field header and data line up, then you don’t need to worry.

Figure A below shows the formulas that I used to capture which columns the headings and data fall under. The first two rows just announce where the data lie by marking it 1 if the cell is not empty and marking it as empty (“”) when empty. Columns 3 and 4 counts off the columns by adding one whenever there is a mark of 1.

Figure B shows the end result of the formulas.

Now I’m ready to line up the data in the order I want. I used the columns off to the left to build my table, A through D in this instance. I lay out the order of the headers that I want and then designate the count number. In my example, I wanted Field3 to be the second item in the table of data but the count is still 3 because it came in as the 3rd column field in the download. Figure C might help you understand what I’m saying.

The final step is to do a form of vlookup using a combo of match and index. Match finds the column number and then index takes the current row and the column number to pull in the correct data. Figure D shows the formulas and Figure E shows the end result.

Now, each time I do a download, I place the data into this file, starting in cell E9 and the columns A through D will sort through the data and pull the number into column order.

Right now the migration is on hold until the issues that we’ve unearthed are resolved, but once we get back on to rolling out the software, I will be able to use this new technique to aid us in reconciling data more quickly.