Solved Multiple Unique Searches in Excel performed at one time

January 3, 2017 at 11:36:27
Specs: Windows 7
Hello all. So I'll do my best to describe the question simply, though it may be a bit verbose. I'm a bit of a novice at Excel, I should say.

So essentially, I'm tasked with taking all of the values in one column of a spreadsheet (let's call it spreadsheet "limited"), which has about 1000 values, all being unique, and finding these same values in an altogether different spreadsheet, which has these 1000 unique values (let's call it "master list"), but they are somewhat lost as there are an additional 3000 unique values which I am not interested in. The purpose of doing this is the "master list" sheet has more detailed information than the spreadsheet containing the 1000 unique values (the "limited" sheet). Now I would love to copy all of the 1000 unique values in the first spreadsheet and locate them all at once in the 2nd spreadsheet (and just simply highlight them in blue, as I need someone else to process that information at a later time), but I'm unsure of how to do that. Instead, I've been pursuing a tedious task of one by one locating these 1000 unique values in "master list" with ctrl+f function, and its a horrible waste of time. Any help would be appreciated, and if you need more specific details please let me know. Thanks all!

message edited by chowder225


See More: Multiple Unique Searches in Excel performed at one time

Report •

#1
January 3, 2017 at 11:53:55
✔ Best Answer
A macro can handle that task with relative ease.

Let's say that your 1000 unique values are in Limited!A1:A1000.

Let's say that your 4000 unique values are in Master List!A1:A4000.

The following code will Find each of the 1000 values in the Master List and turn the cell a pretty Blue. Modify to fit your exact needs.

The code basically "automates" the Find feature of Excel.

Sub PrettyBlueValues()
'Loop Through Limited List
   For Each myVal In Sheets("Limited").Range("A1:A1000")
'Find value in Master List and fill the cell with Blue
    With Sheets("Master List").Range("A1:A4000")
       Set c = .Find(myVal, LookIn:=xlValues, lookat:=xlWhole)
          If Not c Is Nothing Then c.Interior.ColorIndex = 5
     End With
    Next
End Sub

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


Report •
Related Solutions


Ask Question