[Excel 2007] Paste filtered records into different column without deleting the other hidden records

[The Zealot]

This one is driving me nuts so your help is much appreciated.

I have 4 columns, Asset ID, Issues, Formula and Results. Formula and Results are just temporary fields, and Asset ID is an alphanumeric unique identifier. Column "Results" is a Paste Values of Column "Formula". Column P would be the Serial Number.

The formula appends the text "; Duplicate S/N" if the cell in column B isn't empty.


<table style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:58px;"><col style="width:57px;"><col style="width:21px;"><col style="width:56px;"><col style="width:58px;"><col style="width:57px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>F</td><td>G</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">Asset Number</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Issues</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Formula</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Results</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="text-align:center; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A1</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Incorrect Model</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">=IF(AND(B2<>"",P2<>"None", P2 <> "N/A", P2 <> "Not taken", P2 <> "NOE", P2 <> "No Serial Number", P2 <> "N", P2 <> "NSNo"), B2 & "; Duplicate S/N","")
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Incorrect Model; Duplicate S/N
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Incorrect Model</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Incorrect Model; Duplicate S/N</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Incorrect Model; Duplicate S/N</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A3
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A4
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A5
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A6
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">No location; Incorrect Model
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A7
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">S/N not taken
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A8
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Incorrect Model</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Incorrect Model; Duplicate S/N</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">Incorrect Model; Duplicate S/N</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A9
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">A10
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">
</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">_</td></tr></tbody></table>


So now the problem that I'm having is that when I click to filter Results (D) and remove (Blanks), then select the field Results and paste it on column Issues (B), the data in Assets A6 and A7 gets overwritten with a Blank!!!

Now, if this was Office 2003, that stuff used to happen, but I've been using Office 2007 for a few years already and I really don't remember this happening! Excel should be smart enough not to touch the hidden records!

So now how I get the new data in column Issues (B) without losing what's already recorded?

 

[The Zealot]

Anyone?
Does my question even make sense to you?