Delete sequence of rows with same values, while copying data from a cell to another

Hey everyone, I need to reduce the number of rows of a shipping range worksheet that's following a "from zip" / "to zip" and "from weight" / "to weight" method, and has more than 400 thousand rows and 8 columns.

I need to clean these rows because there are thousands of redundant lines in sequence that should be grouped together.
They have the same values, so they should be in a single row with a wider shipping range.

So the need is actually to detect sequential rows that have the same values in all columns except in columns A and B (that`s the shipping range), and when a sequence of rows meets this condition, we need to copy the value from the last row to the first row of the "To zip" column (column B), and then delete the extra rows.

Here is an image that illustrates better what needs to be done:
Excel-forum-image.jpg

I also attached a smaller version of the worksheet (you can see examples of redundant sequential rows at lines 2482, on the last rows and on hundreds of other rows throughout the worksheet).

If I can`t find an automated way of doing this I'm gonna have to do this by hand, so please help if you know how to accomplish this..

Your effort is greatly appreciated.. Thanks in advance!

I managed to turn all redundant lines to "-" , using simple true or false checking if values of 6 columns are the same as the row above.

Please Login or Register  to view this content.

Using this new data it's easy now to make all redundant rows have a zero value and sort them out.
( Updated worksheet attached )

So I made all redundant rows return "-" with this formula:

Please Login or Register  to view this content.

Now I just need to figure out how to get the "To Zip" column value of the last redundant row of a group, and paste it on the first row of the merged group. (reference attached image in first post and illustrations on the updated worksheet)

So, the function should check (in a column) from that point downwards what is the last row with a zero in a sequence, but it should stop checking when there's a number higher than zero in the sequence (it should not check all zeros in total in that column). When it finds a number higher than zero in that sequence, it then stops searching and returns the value of the last zero's row of the "To Zip" column.

I'm just this formula away of being able to just sort and delete all redundant rows.

It's easier to understand viewing the attached worksheet, I made illustrations and comments.
When you open it, please scroll to the right to view the output...

Any hint will be greatly apreciated.

You can use the attached file as a template for use with your real data - just copy the data into columns A to H of the Shipping sheet and copy the formula in I2 down to the bottom of your data. Then in the Extract sheet you should copy the formulae in row 2 down as far as it tells you in cell L1. Then you should fix the values of all those formulae, and then you can delete the Shipping sheet (and maybe rename the Extract sheet). Finally, use File | Save As to save the file with a different name.

I2 of the Shipping sheet contains this formula:

Formula:

Please Login or Register  to view this content.

which identifies the rows with identical entries in columns C to H, and on the last of those rows (or rows where there is not a duplicate set) it generates a sequential number - the record number.

On the Extract sheet, cell I2 contains this formula:

Formula:

Please Login or Register  to view this content.

which identifies the row on the Shipping sheet where those sequential numbers occur.

This formula in B2:

Formula:

Please Login or Register  to view this content.

brings the data from column B of the Shipping sheet on the row given by I2. This formula is copied across to H2, and brings the corresponding data from columns C to H in turn. Cell A2 contains a more complex formula, i.e.:

Formula:

Please Login or Register  to view this content.

where the part highlighted in red looks to see if the previous row on the Shipping sheet is blank (indicating a block of entries that need to be combined), and if so it gets the data from column A on the first row of the block, i.e. the starting zip code.

There is also a simple formula in L2 which identifies how many records you will end up with on this sheet (and thus how far you need to copy the row of formulae down to). It doesn't matter if you copy too far, but you will see hyphens in column I in this case.

To see the effect on your test data, just copy row 2 down to row 2800. Then you would normally fix the values and then you can delete the Shipping sheet. You can also delete column I.

Hope this helps.

Pete

Hey Pete, thanks a lot. Your sheet works perfectly, the only thing is that when I try to work with the full data (420 thousand rows), excel stops responding, and has a hard time processing all calculations.

I'm working now to see if I can do this in batches of 50.000 rows.

Have you taken a look at my second sheet?
Can you think of a formula that calculates the condition in that method?
Maybe that way is less cpu intensive...

But your sheet really works, thanks a lot for your help!