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

66mhz

New member
Joined
Aug 17, 2011
Messages
3
Reaction score
0
Points
0
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? :confused2:

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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

New member
Joined
Aug 17, 2011
Messages
3
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

New member
Joined
Aug 17, 2011
Messages
3
Reaction score
0
Points
0
Excellent insight! Thanks again for your reply.
 
Top