|Please note that when you post everything in one long paragraph it's hard for readers to figure out what you are trying to do. It all runs together and gets confusing. Feek free to toss in a line feed here and there to make your posts easier to read.|
Here are some things that don't make sense to me.
"So, I have only one color to show duplicates, eg. purple."
Why do you only have 1 color? There are 56 fill colors to choose from.
"We are trying to find out if these Social Security numbers belonging to each member on this sheet where I have to work from which have the non- duplicates have been worked on the other sheet with the duplicates. "
Huh? I really don't understand what you mean by "the non- duplicates have been worked on the other sheet with the duplicates"
"So what I have been doing so far is using control find all and replace all one 9 digit number at a time and there are thousands of these.'
Find and replace? I thought you trying to fill the cells that contain duplicates with a color? How does replace enter into this?
"I tried to control find all a whole bunch of these 9 digit numbers at the same time, even separating them by spaces or commas, but it looks like it only can find one set of 9 digit numbers at a time."
Correct. Find searches for the search string, so it is considering the entire string, including the spaces and commas, as one long string to search for. Obviously it will never find something like 123456789, 987654321 since that string does not occur anywhere in the spreadsheet.
"Another problem I noticed is when I tried to search for a 9 digit number and it has a prefix or suffix, eg. one or two letters, it cannot find it unless I delete the one digit suffix"
I'm not sure if you mean that the string you are entering in the "Find what" field has the extra characters or the values in the cells have the extra characters.
If you are entering 123456789A in the "Find what" field, that is what Excel is going to search for, and will only find 123456789A in the sheet, not 123456789.
However, if the cells contain 123456789A and you enter 123456789 in the "Find what" and the option to "Match entire cell contents" is not selected, then Excel will consider 123456789A to be a match. In other words, partial matches will be found as long as what you are searching for is a partial match of the contents of the cell, but not vice versa.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.