I have a 50,000 row spreadsheet with 56 columns. The item in Col A Row 5
repeats in rows 6, 7, 8, 9 and 10 (all Col A). There is data in cells
(Row/Col) 5/B, 5/D, 6/L. 6/Z, 7/AH, 8/AP, 9/AR, 9/AT and 10/AW.

I want to move the data from cells 6/L, 6/Z, 7/AH, 8/AP, 9/AR, 9/AT and 10AW
to cells 5/L, 5/Z, 5/AH, 5/AP, 5/AR, 5/AT and 5AW.

There will never be a cell in row 5 that will contain data that will be
written over by moving the data from rows 6, 7, 8, 9 and 10.

In this way I will reduce the 50,000 rows to a more manageable 10,000 or so.
I have tried importing into Access but there are too many columns to allow me
to run a query or report.

Many thanks

Hi,

Beyond the row 5 to row 10 area what happens? Is it always exactly the same
structure - row 11 should contain the entries from the next 5 rows and so on?
Each item in column A repeats 6 times and there are no blank lines between
each one? Is the data numeric, text or both?

A few asides:

1. The best way to describe a cell is to use the A1 style, to the best of
my knowledge no one who uses spreadsheets uses a 1/A notation so this makes
it very hard for us to follow your question.
2. Access will allow you to query on 56 columns in fact up to 255 columns.
Access reports will allow you to have 56 columns its just a matter of either
placing the column output on different rows or grouping it or increasing the
page width - maximum page width is 22 inches.

In Excel there is no command to do exactly what you ask so you can go
through the following steps or write a macro to do so.

1. Highlight the range B5:BD5 (your 55 columns of data).
2. Press F5, choose Special, Blanks, and click OK.
3. Enter
=IF(ISNA(LOOKUP(9^9,D6:D10)),IF(ISNA(LOOKUP(REPT(" z",255),D6:D10)),"",LOOKUP(REPT("z",255),D6:D10)), LOOKUP(9^9,D6:D10))
4. Press Ctrl R.
5. Highling the range B5:BD5 and press Ctrl C
6. Choose Edit, Paste Special, Values.
7. Highlight the row numbers 6:10 and press Ctrl - (minus)

You have compacted the first set of rows.
--
Cheers,
Shane Devenshire


"GfW" wrote:

I have a 50,000 row spreadsheet with 56 columns. The item in Col A Row 5
repeats in rows 6, 7, 8, 9 and 10 (all Col A). There is data in cells
(Row/Col) 5/B, 5/D, 6/L. 6/Z, 7/AH, 8/AP, 9/AR, 9/AT and 10/AW.

I want to move the data from cells 6/L, 6/Z, 7/AH, 8/AP, 9/AR, 9/AT and 10AW
to cells 5/L, 5/Z, 5/AH, 5/AP, 5/AR, 5/AT and 5AW.

There will never be a cell in row 5 that will contain data that will be
written over by moving the data from rows 6, 7, 8, 9 and 10.

In this way I will reduce the 50,000 rows to a more manageable 10,000 or so.
I have tried importing into Access but there are too many columns to allow me
to run a query or report.

Many thanks

Shane,

Unfortunately it is not always the same number of rows to compact ... it can
range from 0 (where I would not have to do anything) to 5 rows.

I will try your solution and make different marcos, 1 for each number of
rows to compact.

Thanks for your help

"ShaneDevenshire" wrote:

Hi,

Beyond the row 5 to row 10 area what happens? Is it always exactly the same
structure - row 11 should contain the entries from the next 5 rows and so on?
Each item in column A repeats 6 times and there are no blank lines between
each one? Is the data numeric, text or both?

A few asides:

1. The best way to describe a cell is to use the A1 style, to the best of
my knowledge no one who uses spreadsheets uses a 1/A notation so this makes
it very hard for us to follow your question.
2. Access will allow you to query on 56 columns in fact up to 255 columns.
Access reports will allow you to have 56 columns its just a matter of either
placing the column output on different rows or grouping it or increasing the
page width - maximum page width is 22 inches.

In Excel there is no command to do exactly what you ask so you can go
through the following steps or write a macro to do so.

1. Highlight the range B5:BD5 (your 55 columns of data).
2. Press F5, choose Special, Blanks, and click OK.
3. Enter
=IF(ISNA(LOOKUP(9^9,D6:D10)),IF(ISNA(LOOKUP(REPT(" z",255),D6:D10)),"",LOOKUP(REPT("z",255),D6:D10)), LOOKUP(9^9,D6:D10))
4. Press Ctrl R.
5. Highling the range B5:BD5 and press Ctrl C
6. Choose Edit, Paste Special, Values.
7. Highlight the row numbers 6:10 and press Ctrl - (minus)

You have compacted the first set of rows.
--
Cheers,
Shane Devenshire


"GfW" wrote:

I have a 50,000 row spreadsheet with 56 columns. The item in Col A Row 5
repeats in rows 6, 7, 8, 9 and 10 (all Col A). There is data in cells
(Row/Col) 5/B, 5/D, 6/L. 6/Z, 7/AH, 8/AP, 9/AR, 9/AT and 10/AW.

I want to move the data from cells 6/L, 6/Z, 7/AH, 8/AP, 9/AR, 9/AT and 10AW
to cells 5/L, 5/Z, 5/AH, 5/AP, 5/AR, 5/AT and 5AW.

There will never be a cell in row 5 that will contain data that will be
written over by moving the data from rows 6, 7, 8, 9 and 10.

In this way I will reduce the 50,000 rows to a more manageable 10,000 or so.
I have tried importing into Access but there are too many columns to allow me
to run a query or report.

Many thanks

If you get the macro together that will do one range, we can automate it to
handle any number of repeat lines. So test out my suggestion and see if it
does what you want, then record a macro that does the steps you need and post
it here.

--
Thanks,
Shane Devenshire


"GfW" wrote:

Shane,

Unfortunately it is not always the same number of rows to compact ... it can
range from 0 (where I would not have to do anything) to 5 rows.

I will try your solution and make different marcos, 1 for each number of
rows to compact.

Thanks for your help

"ShaneDevenshire" wrote:

Hi,

Beyond the row 5 to row 10 area what happens? Is it always exactly the same
structure - row 11 should contain the entries from the next 5 rows and so on?
Each item in column A repeats 6 times and there are no blank lines between
each one? Is the data numeric, text or both?

A few asides:

1. The best way to describe a cell is to use the A1 style, to the best of
my knowledge no one who uses spreadsheets uses a 1/A notation so this makes
it very hard for us to follow your question.
2. Access will allow you to query on 56 columns in fact up to 255 columns.
Access reports will allow you to have 56 columns its just a matter of either
placing the column output on different rows or grouping it or increasing the
page width - maximum page width is 22 inches.

In Excel there is no command to do exactly what you ask so you can go
through the following steps or write a macro to do so.

1. Highlight the range B5:BD5 (your 55 columns of data).
2. Press F5, choose Special, Blanks, and click OK.
3. Enter
=IF(ISNA(LOOKUP(9^9,D6:D10)),IF(ISNA(LOOKUP(REPT(" z",255),D6:D10)),"",LOOKUP(REPT("z",255),D6:D10)), LOOKUP(9^9,D6:D10))
4. Press Ctrl R.
5. Highling the range B5:BD5 and press Ctrl C
6. Choose Edit, Paste Special, Values.
7. Highlight the row numbers 6:10 and press Ctrl - (minus)

You have compacted the first set of rows.
--
Cheers,
Shane Devenshire


"GfW" wrote:

I have a 50,000 row spreadsheet with 56 columns. The item in Col A Row 5
repeats in rows 6, 7, 8, 9 and 10 (all Col A). There is data in cells
(Row/Col) 5/B, 5/D, 6/L. 6/Z, 7/AH, 8/AP, 9/AR, 9/AT and 10/AW.

I want to move the data from cells 6/L, 6/Z, 7/AH, 8/AP, 9/AR, 9/AT and 10AW
to cells 5/L, 5/Z, 5/AH, 5/AP, 5/AR, 5/AT and 5AW.

There will never be a cell in row 5 that will contain data that will be
written over by moving the data from rows 6, 7, 8, 9 and 10.

In this way I will reduce the 50,000 rows to a more manageable 10,000 or so.
I have tried importing into Access but there are too many columns to allow me
to run a query or report.

Many thanks