Results 1 to 5 of 5

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Quote Originally Posted by Ken Puls View Post
    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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).

    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Excellent insight! Thanks again for your reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •