Find data in a huge pile

[bigdan]

I'm playing around with different systems which haven't been mapped properly. Essentially I'm trying to see if data in one system exists in the other, and if so what the fields would be called.

Once I get all the data into Excel, is there a way to see if data in group A exists anywhere in group B? Essentially this would be like a vlookup, except instead of looking at one column I want it to look at all the data in 30 columns. Can that be done?

 

[Rick Rothstein]

Re: how to find data in a huge pile

I'm playing around with different systems which haven't been mapped properly. Essentially I'm trying to see if data in one system exists in the other, and if so what the fields would be called.

Once I get all the data into Excel, is there a way to see if data in group A exists anywhere in group B? Essentially this would be like a vlookup, except instead of looking at one column I want it to look at all the data in 30 columns. Can that be done?

Try using the COUNTIF function... the first argument would be the absolute range for Group B and the second argument would be an individual cell value from Group A. You did not give us enough about your layout to guess at an example formula for you, so I hope the above description is enough for you to go on.

 

[Kenneth Hobson]

Re: how to find data in a huge pile

A Range's Find should suffice. e.g.

Sub Main()
  Dim r As Range, c As Range, f As Range
  Set r = [B1:C10]
  r.Value = "Hello World!"
  [C3].Value = "Ken"
  
  [A1].Value = "Ken"
  Set f = r.Find([A1].Value)
  If Not f Is Nothing Then MsgBox f.Address
  
  [A1].Value = "Kenneth"
  Set f = r.Find([A1].Value)
  If Not f Is Nothing Then MsgBox f.Address
End Sub

 

[bigdan]

Re: how to find data in a huge pile

Try using the COUNTIF function... the first argument would be the absolute range for Group B and the second argument would be an individual cell value from Group A. You did not give us enough about your layout to guess at an example formula for you, so I hope the above description is enough for you to go on.

This works great, thanks.

I'm now finding a similar issue. I want to compare two sets of data, but I'd like to match even if they're an approximate match, not necessarily an exact. In order to consider something a match I'd like if the first word matched. So for example "USA 001" should match with "USA 002".

Can this be done?

 

[Rick Rothstein]

Re: how to find data in a huge pile

I'm now finding a similar issue. I want to compare two sets of data, but I'd like to match even if they're an approximate match, not necessarily an exact. In order to consider something a match I'd like if the first word matched. So for example "USA 001" should match with "USA 002".

Can this be done?

The COUNTIF function works with wildcards (? for single characters, * for zero or more characters) so your second argument could be "USA00?" or "USA*" depending on how "fuzzy" you want to allow the match to be.

 

[Kenneth Hobson]

Re: how to find data in a huge pile

We can do an xlPart in Find. Of course the part can be anywhere in the string. If you want a prefix or suffix, then a find with Split() might be needed.

e.g.

Sub Main()
  Dim r As Range, c As Range, f As Range
  Set r = [B1:C10]
  r.Value = "Hello World!"
  [C3].Value = "Ken Ray Hobson"
  
  [A1].Value = "Ken"
  Set f = r.Find([A1].Value)
  If Not f Is Nothing Then MsgBox f.Address, , [A1]
  
  [A1].Value = "ken"
  Set f = r.Find([A1].Value, MatchCase:=True) 'case senitive
  If Not f Is Nothing Then MsgBox f.Address, , [A1]
  
  [A1].Value = "ray"
  Set f = r.Find([A1], lookat:=xlPart)
  If Not f Is Nothing Then MsgBox f.Address, , [A1]
End Sub

 

[bigdan]

Re: how to find data in a huge pile

Thanks!

 

[bigdan]

Re: how to find data in a huge pile

The COUNTIF function works with wildcards (? for single characters, * for zero or more characters) so your second argument could be "USA00?" or "USA*" depending on how "fuzzy" you want to allow the match to be.

I tried this method. The thing is, in the specific formulas you wrote for me, it requires me to manually type out "USA00?" or "USA*". Instead lets say the field just says "USA-001" and I want it to return a match if anywhere in the data range we see a word/field starting with "USA".

 

[Rick Rothstein]

Re: how to find data in a huge pile

I tried this method. The thing is, in the specific formulas you wrote for me, it requires me to manually type out "USA00?" or "USA*". Instead lets say the field just says "USA-001" and I want it to return a match if anywhere in the data range we see a word/field starting with "USA".

The formula cannot know how to determine the text substring to search for without being told... and I don't know what to tell it. What is the general rule for determining the text substring... will it always be the text before a dash? If not, what other text strings are there and what is the text substring for each?

 

[bigdan]

Re: how to find data in a huge pile

The formula cannot know how to determine the text substring to search for without being told... and I don't know what to tell it. What is the general rule for determining the text substring... will it always be the text before a dash? If not, what other text strings are there and what is the text substring for each?

Let's say the first 5 letters being the same? I may tweak that later but that's a good start.