0

Sample data set: enter image description here

Is there a VBA formula that will compare the dates, and the similar words, between the two data sets? For example, if it contains "Ter" and "1/9/18", then I can find out that the location is "Grass".

My goal is to find the cells where the "Dates" are the same and where "Away or Home Club" matches "Organization", to ultimately find the location.

I have two excel sheets and I need to find the rows which are comparable. I have a "Date" column that needs to be an exact match on both sheets. In addition I have two columns and need to match up with one column on the second sheet. (They are not exact matches, but both include similar words for example, "Ter").

Sample Workbook

Improve this question
4
  • 2
    Can you please edit your question to include sample data, expected result and what you've tried so far
    – cybernetic.nomad
    Commented Feb 19, 2019 at 20:47
  • 1
    Can you please mock up an example of the expected result and explain the logic? It's not clear what you want to achieve. Also, there is no Excel worksheet function that does a fuzzy match for text. You would need complicated VBA for that.
    – teylyn
    Commented Feb 19, 2019 at 21:06
  • (1) As cybernetic.nomad said, please show expected results.  I guess you want results in Column H; e.g., H6 = “Grass”.  And, based on my guess of what you want, only four of the nine values you have in Column G would have results — am I missing something?  (2) Will you be dealing only with initial substrings (e.g., “Ter” is the first three letters of “Terps”), or might there be middle strings (e.g., “ants”, “nights” or “arg”)?  … (Cont’d)
    – Scott - Слава Україні
    Commented Feb 20, 2019 at 6:40
  • 1
    (Cont’d) … (3) “Ter” (in Column G) is a substring of “Terps” (in Column B), but “Eag” (in Column C) is a substring of “Eagles” (in Column G).  Are you serious?  You want to do substring matching in both directions? … … … … … … … … … … … … … … … … … … … … Please do not respond in comments; edit your question to make it clearer and more complete.
    – Scott - Слава Україні
    Commented Feb 20, 2019 at 6:40

2 Answers 2

Reset to default
0

Your sample data set has no duplicate dates, and each team name has a unique three-character identifier. For data meeting those constraints, this will work. For more than one contest per date, there may not be a worksheet function solution.

enter image description here

`=IF(OR(UPPER(LEFT(INDEX(Data1,MATCH(F2,$A$2:$A$10,0),2),3))=UPPER(LEFT(G2,3)),UPPER(LEFT(INDEX(Data1,MATCH(F2,$A$2:$A$10,0),3),3))=UPPER(LEFT(G2,3))),INDEX(Data1,MATCH(F2,$A$2:$A$10,0),4),"no match")`

I named the range $A$2:$D$10 'Data1' for simplicity.

We account for the matching text by taking UPPER(LEFT([text],3)).

This follows the general outline:

=IF(OR([one_thing],[another_thing]),[do_something],[something_else])

[one_thing], excluding the upper and left text manipulation, expands to:

INDEX(Data1,MATCH(F2,$A$2:$A$10,0),2)=G2

and [another_thing], similarly:

INDEX(Data1,MATCH(F2,$A$2:$A$10,0),3)=G2

Those say, using the date (F2) as a key, search the data set and bring back the 2nd and 3rd columns' values, respectively, and compare to G2.

If there's a match, [do_something]:

INDEX(Data1,MATCH(F2,$A$2:$A$10,0),4)

Get the 4th column of the data set record where the date matches, and print it; otherwise

[something_else]

"no match"

You could wrap with an IFERROR to eliminate the #N/A results for rows where there's no matching date in the data set, if you want.

Improve this answer
8
  • Thank you!! That was exactly what I was looking for!!! Unfortunately, my data set does have duplicate dates. You mentioned there is not something I can add to the formula to account for that?
    – Jamie White
    Commented Feb 20, 2019 at 18:11
  • Not with this approach. This approach relies on, basically, finding a matching date in the data set and then double checking that that date also contains a matching team name. To do something over multiple date matches, you're basically doing a FOR loop, you're doing VBA. It's possible to do with a worksheet function but, really, it would be prohibitively complex.
    – Alex M
    Commented Feb 21, 2019 at 1:48
  • ...Which is fine, VBA is fine, I'm just not practiced enough with it to be qualified to use it to write an answer on SuperUser. All the VBA I've written is cribbed from existing code I found on SuperUser to begin with. Maybe edit your question to add a VBA tag and one of the resident whizzes will be along to show you how it's done - this is an easy task in VBA.
    – Alex M
    Commented Feb 21, 2019 at 1:49
  • Thanks, Alex! I really appreciate all your time and help with this!
    – Jamie White
    Commented Feb 21, 2019 at 18:00
  • Hi Alex, I was wondering if the formula you provided would work if I combine the cells to get rid of the duplicate dates. I can do so on both columns if need be. For example, something like the attached picture. I know it won't tell me which location for which game, but it will at least eliminate the dates where the location is not available (less I have to manually check).
    – Jamie White
    Commented Feb 25, 2019 at 21:05
-1

The first solution would be to develop VBA code to do what you want.

An alternative would be to extract 2 CSV files and to compare them using WinMerge.

The best solution depend of the complexity of your search.

Before comparing the 2 files, you can load them using Notepad++ and use specific Regex to remove non significant rows.

Improve this answer

You must log in to answer this question.

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