Copying and pasting pre-filtered results into same row number?

**Twomsta** · 06-06-2012, 02:35 AM

Hi all,

Hopefully I've posted this in the correct area of the forum!

For the sake of context what I have done thus far is categorized rainfall levels into 3 threshold points. e.g.

'rain_event': => 5mm/day

'Significant': => 25mm/day

'Heavy': => 100mm/day

In order to do this I used the following formula:

=IF(F2>=100,"HEAVY",IF(F2>=25,"SIGNIFICANT",IF(F2>=5,"Rain_Event",IF(F2<5,"N"))))

The corresponding (adjacent) column now includes the name of the threshold (e.g. 'significant')

I then filtered the worksheet removing all rows with 'N' (i.e. non events)

What I now need to do is copy and paste the results/data into a new worksheet whilst retaining the same numbered cell position (i.e. row number) with regards to the 'y' axis (the column position will not matter). Presently, when copying and pasting the results they are all pasted below each other.

If this makes sense and someone can help me it'd be greatly appreciated. If its too confusing I will make up some simple visual examples of what I need in terms of before and after!

Thanks!

**Ace_XL** · 06-06-2012, 02:43 AM

Good day,

Copy all the data (unfiltered) and paste it on the new worksheet. On this new worksheet filter on 'N' and instead of deleting the rows, just clear contents of relevant cells. This way you will have the row numbers intact.

**Twomsta** · 06-06-2012, 03:36 AM

Thanks for the reply,

Sorry to be a pain, but how do I get it to clear the cells containing 'N' and not just delete or hide them?

Cheers!

**Ace_XL** · 06-06-2012, 03:37 AM

The 'delete' button on the keyboard usually does the trick. Alternatively, right clicking displays the clear contents option too!

**Twomsta** · 06-06-2012, 03:44 AM

I see, however manual deleting is not an option (hence why I was filtering the results!)

I have almost 30 different sheets with around 20,000 to 60,000 rows of data. After filtering I average around 2,000 to 8,000 rows of data.

So you can now see the extent of my problem with regards to 'manual' processing.

**Twomsta** · 06-06-2012, 10:44 PM

So does anyone know if this is possible?

Really need to work this out as not only do I have rainfall to analyse, I have wind and wave height to organise too!

Thanks!