john banks

I have made a text file from a column of an Excel worksheet that was autofiltered. I now want to paste an altered version back over the original filtered data but not over rows that were filtered out. I do need the data that has been filtered out. There are tens of thousands of lines, so I can't do it manually.

Suggestions? I've tried help and searching.

Example if my description was not clear:

A
B
C
D
E

... filters to:

A
D
E

... then this is changed to eg:

B
Q
F

... so when I paste I want:
B
B
C
Q
F

bob269

Is it feasable to hide the rows then paste the new data back and then unhide them?

john banks

There are over 200000 rows over 4 worksheets. If there is an automatic way to hide rows (based on a filter) then yes it would work. I'll look into it thanks.

john banks

Unfortunately pastes of blocks go into the hidden or filtered cells.

bob269

try on here mate, these guys usually respond within 5minutes and pretty much know everything excel related.


MrExcel Message Board :: Index

DJ Dunk

iTrader: (5)

Excel won't paste into multiple ranges, its pretty annoying. I'd use an IF statement and a VLOOKUP to do it. Add a unique column to the data that you're copying so you have something to tie it back to, if there isn't already one.

john banks

Thanks have posted on MrExcel.

IF statement doesn't seem to work with text, and I would be VLOOKUP depending on some rows being present and others not. I'm lost on this one.

DJ Dunk

iTrader: (5)

Original Data on a sheet called 'MyData':
A, B (column headers)
1, A
2, B
3, C
4, D
5, E

Cut data:
A,B
1, A
4, D
5, E

Amended data on a sheet called 'Lookup':
A,B
1, B
4, Q
5, F

Unfilter the data, do a nested vlookup in a new column that says (sorry not got Excel on this machine to get the syntax):

If value in MyData column A exists in Lookup column A then perform the vlookup: Lookup value in MyData column A within Lookup A|B, return value in resulting column 2.

If not, return MyData column B.

Then copy it down and cut and paste the resulting values. Not sure if that makes sense.

john banks

Thanks. Writing code in Excel is new to me. Instead I just wrote an app in Visual Basic to process my text file directly.