Formula needed to identify unique string then capture the entire row it appears in

[66mhz]

So I have a spreadsheet that contains the string "N/A" in multiple cells. This string signifies data that is missing and will eventually need to be populated. How can I search for each instance of "N/A" then capture the entire row that it appears in and copy it somewhere?

Ideally, I'd like to copy each row that contains "N/A" to a separate workbook. Any guidance would be most appreciated.

Thanks.

[Ken Puls]

Is it in tabular format? If it is then you can sort your data by the #N/A's and copy the range to an alternate sheet.

[66mhz]

Is it in tabular format? If it is then you can sort your data by the #N/A's and copy the range to an alternate sheet.

So I ended up using Autofilter to identify all the instances of '#N/A'. I then copied those rows out to their new home.

Any thoughts on this method? It seems to have worked fine. Always willing to hear other points of view.

Thanks for your reply.

[Ken Puls]

Right, that's actually what the main thrust of the tabular format question was directed at. As soon as you get into a table format with headers, you can sort and turn on autofilter. Personally, I would have used Autofilter myself.

One thing to be aware of with autofilter though... if you have a ton of non-contiguous rows, sometimes the copy method will fail because the selection is too complex. If that happens... sort your data first. That way when you autofilter the range you want to select is contiguous (grouped together).

[66mhz]

Excellent insight! Thanks again for your reply.