Displaying large table into smaller table

**FlynHokie** · 12-07-2012, 04:59 PM

Hey all. New to this forum. Glad I found it. I'm fairly experienced in using the basics Excel, though I know nothing about VBA or macros, or other advanced features.

I've got a problem with summarizing a rather large group of cells into a smaller compressed set of cells, without doing a very complicated nested if loop.

See the attachment for a simplified sheet of what I need.

The table on the right has 3 different data sets. I need the simplified version to display like the table on the left. i.e. I need the simplified table to display all populated rows in the full table, without displaying empty rows, and leaving a blank row between each data set.

Can anyone point me in the right direction to do this simply, rather than a large nested loop?

Thanks very much!

FlynHokie

**stanleydgromjr** · 12-07-2012, 05:51 PM

FlynHokie,

Welcome to the Excel Forum.

Your original table is in range K2:O19. It can have more rows, but, it can not have more columns.

If the table can have more columns to the right of column O, then the macro can be re-written so that the new reorged data can be placed in a new worksheet.

The below macro will copy/move/downsize the table, and relocate the data into columns B, C, D, and E, beginning in row 2.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgTable macro.

**FlynHokie** · 12-08-2012, 01:50 AM

Thanks stanleydgromjr! I'll give that a shot when I get back into the office.

I'll have to see if I can understand the language (been ages since I looked at programming languages (PASCAL), so it may take some time to figure out what you wrote).

The spreadsheet I put together was a very very simplified version of what I have, so I'll see how it works. But basically, I have multiple worksheets that take about 50 rows of data, and condense that data into about 30 rows of data for each worksheet. I then manually copy each worksheet into a separate worksheet group of rows/columns, (giving the simplified right table in the test *.xls), and then try to summarize those multiple data sets in the simplified left table in the test *.xls. All together, the left table is approximately 50 columns (many merged) of about 30 rows for a specific format required for a report, and the right column is the same number and type of merged cells, but with an undetermined amount of rows (anywhere from 1 to 20 datasets (so, 30-600 rows)

I'll mess with it monday, and let you know how it goes.

FlynHokie