Quick and Easy Way to Align Rows

[STUARTXL]

Hi,

I have an accounts report (spanning several pages) that I have exported to Excel however, the export isn't fit for purpose. The underlying issue here rests with the reporting format in the accounts software, which hasn't been set up properly by the accounts software support team. This will be addressed separately but for now I need a short term solution.

Essentially the report consists of a few different columns, the last three of which are debit, credit and cumulative balance. If I were to print the report and it was say, across 100 pages, the export is such that Excel recognises each page by leaving a few blank rows between each page, so that there are effectively 100 blocks of info on Excel. I have now condensed the information so that these blank rows have been deleted and I now have just one block on information approximately 9,000 rows long.

The problem lies in the fact that, when the report is initially exported, each of the 100 blocks of information present the debits, credits and balances in different columns (the scope of the shift is 3 columns i.e. if the debits, credit and balance column should be in columns F, G and H respectively, most of the block will present them in these columns however, some will present them in E, F and G and others present them in G, H and I. The only consistent here is that the debit, credit and balance column are always found to be together i.e. in 3 adjoining columns.

I wonder if there is a way to align all debits, credits and balance columns in one swift manoeuvre?

AttachedExcerpt.xlsx is an excerpt of the spreadsheet to demonstrate. Debits are highlighted yellow, credits green and cumulative balances are red.

[dflak]

This is going to take some VB. Use the attached spreadsheet as a "template." That is, click on the Clear Source Button and copy and paste the data into cell A4. Then click on the Condense Button and read the results on the results page.

The spreadsheet makes use of Excel Tables. For more information about Excel Tables see: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

I'll explain the VB code if you are up to it. Otherwise, enjoy.