Using control find to find multiple sets of numbers

December 15, 2011 at 11:35:14
Specs: Windows XP
Is is possible to "control find all" a whole bunch of numbers, instead of one set at a time in each cell and "replace all" with a format, eg. a specific color? I am using Excel 2010. I tried doing macros, but it did not seem to work, maybe I was not setting it up correctly? Thank you.

See More: Using control find to find multiple sets of numbers

Report •

#1
December 16, 2011 at 04:09:36
You'll need to be more specific about what you are trying to do.

What do you mean by "whole bunch of numbers" and "one set at a time in each cell"?

Specific examples would help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 16, 2011 at 06:07:59
I am trying to find if there are more than one of the same 9 digit number, eg. Social Security number in a column or anywhere else on the sheet. I want to fill the duplicate cells with the same number the same color to show the duplicates. So, I have only one color to show duplicates, eg. purple. I have another sheet in the same workbook where I have to work from which shows the 9 digit numbers which are not duplicates. This sheet which has the 9 digit numbers where I am supposed to match have the duplicates already taken out. 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. 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. 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. 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, eg. 123456789A, some times I have to delete the "A", so I can find the 9 digit number. I tried checking the format, but it looks okay, maybe it was not entered correctly, like the zero is the letter"o" ???

Report •

#3
December 16, 2011 at 07:32:55
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.


Report •

Related Solutions

#4
December 16, 2011 at 08:08:50
"So, I have only one color to show duplicates, eg. purple."

What I mean is I am coloring the cells only one color to show all the duplicates.

"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. "

I have 2 sheets in the workbook. The first sheet has all the 9 digit social security numbers, all unique, no duplicates. The 2nd sheet has all these same 9 digit numbers, many are duplicates because they show up on each month that they were worked on. This sheet has many duplicate 9 digit numbers, some showing up once each month or several times each month and so there are months showing chronologically, eg. Jan 2010, Feb 2010 to Nov 2011 with duplicate 9 digit numbers in each month derived from the 1st sheet which has unique 9 digit numbers, no 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.'

I enter each 9 digit number on the 1st sheet and trying to "control find all" in 2nd sheet to find the duplicates, then I "control replace all" in the same 2nd sheet with the same color, eg. purple to show that they are duplicates. I have to do this for each 9 digit number from the 1st sheet and find it in the 2nd sheet with the duplicates. I have thousands of these 9 digit numbers from the 1st sheet that I have to find duplicates in the 2nd sheet and to show that they are duplicates in the 2nd sheet, I color them with one color, eg. purple. I can replace all the duplicates with a format, eg. color to show that they are duplicates.

I noticed when I enter "control find all" a specific set of numbers, eg. 123456789A, and the numbers that I am searching for has 123456789, it will find it. All that I am looking for most important of all is the exact numbers must match, so if I am entering 123456789A, I want to find 123456789 and it does not matter if it has a suffix or prefix, but the reason that I am entering 123456789A is because I am copying from the 1st sheet which has these unique (non duplicate) 9 digit numbers with prefixes and suffixes already there, therefore, I am trying to save time by copying and pasting into "control find all" exactly from the 1st sheet with the prefixes and suffixes already there in each cell.


Report •

#5
December 16, 2011 at 08:24:35
The following code will read down through Sheet 1 Column A and search Sheet 2 for each value.

If it finds a match, or a partial match, it will fill the cell with ColorIndex # 39, which is Purple in Excel 2003.

Sub ColorMePurple()
'Determine last row with data in Sheet 1 Column A
 lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through Sheet 1 Column A
   For nxtRw = 1 To lastRw
'Look for matching value on Sheet 2
'If found, color the cell purple
    With Sheets(2).Cells
      Set c = .Find(Sheets(1).Range("A" & nxtRw), lookat:=xlPart)
        If Not c Is Nothing Then
           firstAddress = c.Address
             Do
              c.Interior.ColorIndex = 39
               Set c = .FindNext(c)
             Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 16, 2011 at 09:18:35
Hello Tim. Wow that is amazing. How do I copy and paste this in Excel 2010???

Report •

#7
December 16, 2011 at 09:57:29
Who's Tim?

Use either Alt-F11 or right-click a sheet tab choose View Code to open the VBA editor.

Copy/paste the code from my response into the VBA editor and run it.

I suggest that you try this in a backup copy of your workbook since macros cannot easily be undone. Since I can't see your workbook from where I'm sitting, I can't offer any guarantees that the code will not screw up something in your workbook.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
December 19, 2011 at 10:14:41
Ohhh, sorry for the name screw up, I was thinking of Tom's Guide, ha ha.

Thank you for information. This will definitely reduce the amount of repetitive work involved. However, I need to ask another big favor.

I also need to have the program updated to include a 2nd step:

From sheet 2, I need to find each block of 9 digit numbers. For example, there could be 20 of the same unique 9 digit number: 123456789. I need one of these 9 digit numbers to be colored purple and the rest (19 of the same) to be colored yellow. The purpose I was told was to differentiate which 9 digit number was done and the rest were supposed to be duplicates of that same 9 digit number.

Once you have updated the above program, I will paste it into the VBA editor, so I can run it every time I work on these 2 sheets which will be on-going for each product we carry. We have to work on one product number (on a different worksheet) at a time, so I have to run this program every time.


Report •

#9
December 19, 2011 at 12:40:27
Let me ask you a quick question...

When you are asked to do a project at work and you get it all done and delivered, do you find it annoying when they suddenly add more requirements?

Do you find that you often end up having to rework the original solution because the additional requirements, while sounding simple, would have been more efficiently handled if you had known about them upfront?

That's where we are at this point.

You specifically asked that all the duplicates be colored purple. I even asked you to clarify that when I questioned your statement "I have only one color to show duplicates, eg. purple."

Now you've changed the requirements and made the solution more complicated.

re: "Once you have updated the above program..."

Once I have updated the program? That's a pretty bold assumption.

Perhaps you should try and firgure out how the code works and see if you can modify it yourself. After all, I'm not going to be available to maintain the code for you on an ongoing basis and if you are going to be using it in a business critical application, you should understand how it works.

Here's a link that might help you:

http://www.computing.net/howtos/sho...


Report •

#10
December 20, 2011 at 07:19:14
Thank you for the link.

I tried copying and pasting the code above in VBA in Excel 2010 and running it. But, I get a blank screen and an hour glass, then an error message that the program is not responding. Is it because my computer is not set up to handle this or there is a mistake in the code?


Report •

#11
December 20, 2011 at 11:13:54
The code works fine for me.

Since I can't see your spreadsheet from where I'm sitting, it's hard for me to tell what your problem is.

Here is how my spreadsheet is set up:

Sheet1 Column A contains a list of 9 digit numbers. They start in A1 and there are no blanks cells in between the numbers.

e.g.

        A
1   123456789
2   253614789
3   123456780

Sheet2 contains a bunch of random data, some of which are 9 digit numbers that match the list in Sheet1 Column A, some of which are matching numbers with a letter before or after the matching number and the rest are non-matching values.

When I run the code, only those cells with numbers that contain a sequence of 9 digits that match the 9 digits from a number in Sheet1 are filled with Purple.

If there are blank cells between the numbers in Sheet1 Column A, then you'll probably have a problem since the empty cells will match all the empty cells in Sheet 2 and the code will find them and color them Purple.


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question