0

I have an Excel spreadsheet that has a list of names in column A. What I'd like to do is parse out all the names in the column that I don't know and their rows. For example, in a spreadsheet of 100 names, let's say I know 39 of them and their row data. I don't need to see them in the spreadsheet - what I need to see are the remaining 61 that I don't know and their rows.

Hope that makes sense, and that something like this is possible!

Thanks!

3
  • Straightforward but you should provide us with your data layout, ie.e where are the names stored that you do know?
    – brettdj
    Aug 30, 2012 at 0:11
  • Thanks for the fast response! Where should the names be stored? I can easily store them in the same spreadsheet, in a separate text file, or I can enter them directly in a formula, whichever is most flexible.
    – user1631862
    Aug 30, 2012 at 0:24
  • It depends whether this is a one off (put them inside the spreadsheet), or repetitive. And whether the known names change or stay stable
    – brettdj
    Aug 30, 2012 at 0:32

3 Answers 3

1

Throw the names you do know in a separate sheet in the same workbook, then do a vlookup in the first worksheet to determine if the names are known or not known, and filter on that?

You'll get much better answers, I'm sure, but that's what I do, ad hoc.

1
  • I rethought this a little, and realized that you also want the row number. If you can sort your known names in alpha order, to get the row, you can use match. I tried to add more details about this, but now I'm called away, and I can't delete this, for some reason. Anyway, look into the Match function in Excel; it would return the row number, assuming the range that it's searching is in alpha order.
    – Julie
    Sep 1, 2012 at 2:22
1

Yea, like julie said - this is a very basic use of vlookup.

Vlookup Example

You can then sort on the "Do we Know?" column - to put all the ones we don't "know" to the top.

0

Since you don't mention just how the names you "do know" exist, I'll take the simplest explanation and answer based on the idea you literally know them, like friends are known, or customers, maybe a batch of whom are resolved situations, or what-have-you.

You can use Excel's column filtering feature. Add a column, then go down it placing something simple like an "x" in cells next to rows for people you do know.

Then check the column filter's settings and make sure "blanks" is the only marked option. Close that and the rows that have your simple marks will be hidden leaving only the rows for people you don't know. Mark more? Do the checking on the options again and close and they will be hidden as well. Any time you need access to the hidden rows, just select "Select All" option instead.

1
  • Nice, and more elegant than the provided answer as even I can do this w/o understanding "vlookup" :-D For the benefit of people who don't know Excel even to my meager level of knowledge, maybe you could edit the question and add clarifying screenshots? Mar 5, 2023 at 12:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .