How to compare data on two excel sheets with VBA code?Compare two excel sheets with almost identical data and...
What species should be used for storage of human minds?
Can a player sacrifice a creature after declaring that creature as blocker while taking lethal damage?
How to not let the Identify spell spoil everything?
Why is it that Bernie Sanders is always called a "socialist"?
How do you funnel food off a cutting board?
Square Root Distance from Integers
Describe a span of time of 1 or 2 years, without being too literal
How do I prevent a homebrew Grappling Hook feature from trivializing Tomb of Annihilation?
Translation needed for 130 years old church document
What is the industry term for house wiring diagrams?
Single-row INSERT...SELECT much slower than separate SELECT
Not a Long-Winded Riddle
"Starve to death" Vs. "Starve to the point of death"
How to politely refuse in-office gym instructor for steroids and protein
Memory usage: #define vs. static const for uint8_t
Categorical Unification of Jordan Holder Theorems
Is there a file that always exists and a 'normal' user can't lstat it?
Why is that max-Q doesn't occur in transonic regime?
Website seeing my Facebook data?
Should I cite R or RStudio?
What senses are available to a corpse subjected to a Speak with Dead spell?
What is a good reason for every spaceship to carry a weapon on board?
Can you determine if focus is sharp without diopter adjustment if your sight is imperfect?
Boss asked me to sign a resignation paper without a date on it along with my new contract
How to compare data on two excel sheets with VBA code?
Compare two excel sheets with almost identical data and come out with matched and mismatched dataCompare the value change of data in Excel between two similar sheets without VBA?In Excel I need to find data on sheet3 from data on sheet1Microsoft Excel: Merge two sheets and dedupExcel: How to compare two text strings and flag the onesHow do I compare a cell value with a range of values on 2 dirrerent sheets and return a Text I specifyExcel Match Data Multiple ColumnsExcel if cell from two different sheets match delete lineexcel vba: copy rows if data match with values in column in another sheetExcel 2016 Find and highlight data match across two worksheets
Sample data set:
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
microsoft-excel vba
asked Feb 19 at 20:45
Jamie WhiteJamie White
11
New contributor
Jamie White is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Sample data set:
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
microsoft-excel vba
asked Feb 19 at 20:45
Jamie WhiteJamie White
11
New contributor
Jamie White is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Can you please edit your question to include sample data, expected result and what you've tried so far
– cybernetic.nomad
Feb 19 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
Feb 19 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
Feb 20 at 6:40
(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
Feb 20 at 6:40
add a comment |
Sample data set:
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
microsoft-excel vba
asked Feb 19 at 20:45
Jamie WhiteJamie White
11
New contributor
Jamie White is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Sample data set:
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
microsoft-excel vba
microsoft-excel vba
asked Feb 19 at 20:45
Jamie WhiteJamie White
11
New contributor
Jamie White is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Feb 19 at 20:45
Jamie WhiteJamie White
11
New contributor
Jamie White is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 6 hours ago
Jamie White
asked Feb 19 at 20:45
Jamie WhiteJamie White
11
New contributor
Jamie White is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Feb 19 at 20:45
Jamie WhiteJamie White
11
asked Feb 19 at 20:45
Jamie WhiteJamie White
11
11
New contributor
Jamie White is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Jamie White is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Jamie White is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Can you please edit your question to include sample data, expected result and what you've tried so far
– cybernetic.nomad
Feb 19 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
Feb 19 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
Feb 20 at 6:40
(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
Feb 20 at 6:40
add a comment |
2
Can you please edit your question to include sample data, expected result and what you've tried so far
– cybernetic.nomad
Feb 19 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
Feb 19 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
Feb 20 at 6:40
(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
Feb 20 at 6:40
2
2
Can you please edit your question to include sample data, expected result and what you've tried so far
– cybernetic.nomad
Feb 19 at 20:47
Can you please edit your question to include sample data, expected result and what you've tried so far
– cybernetic.nomad
Feb 19 at 20:47
1
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
Feb 19 at 21:06
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
Feb 19 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
Feb 20 at 6:40
(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
Feb 20 at 6:40
(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
Feb 20 at 6:40
(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
Feb 20 at 6:40
add a comment |
2 Answers
2
active
oldest
votes
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.
`=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.
answered Feb 20 at 0:36
Alex MAlex M
1578
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
Feb 20 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 aFORloop, you're doing VBA. It's possible to do with a worksheet function but, really, it would be prohibitively complex.
– Alex M
Feb 21 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
Feb 21 at 1:49
Thanks, Alex! I really appreciate all your time and help with this!
– Jamie White
Feb 21 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
6 hours ago
|
show 1 more comment
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.
answered Feb 19 at 21:18
schlebeschlebe
13310
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Jamie White is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1407605%2fhow-to-compare-data-on-two-excel-sheets-with-vba-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
`=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.
answered Feb 20 at 0:36
Alex MAlex M
1578
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
Feb 20 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 aFORloop, you're doing VBA. It's possible to do with a worksheet function but, really, it would be prohibitively complex.
– Alex M
Feb 21 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
Feb 21 at 1:49
Thanks, Alex! I really appreciate all your time and help with this!
– Jamie White
Feb 21 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
6 hours ago
|
show 1 more comment
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.
`=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.
answered Feb 20 at 0:36
Alex MAlex M
1578
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
Feb 20 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 aFORloop, you're doing VBA. It's possible to do with a worksheet function but, really, it would be prohibitively complex.
– Alex M
Feb 21 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
Feb 21 at 1:49
Thanks, Alex! I really appreciate all your time and help with this!
– Jamie White
Feb 21 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
6 hours ago
|
show 1 more comment
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.
`=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.
answered Feb 20 at 0:36
Alex MAlex M
1578
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.
`=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.
answered Feb 20 at 0:36
Alex MAlex M
1578
answered Feb 20 at 0:36
Alex MAlex M
1578
answered Feb 20 at 0:36
Alex MAlex M
1578
answered Feb 20 at 0:36
Alex MAlex M
1578
1578
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
Feb 20 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 aFORloop, you're doing VBA. It's possible to do with a worksheet function but, really, it would be prohibitively complex.
– Alex M
Feb 21 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
Feb 21 at 1:49
Thanks, Alex! I really appreciate all your time and help with this!
– Jamie White
Feb 21 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
6 hours ago
|
show 1 more comment
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
Feb 20 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 aFORloop, you're doing VBA. It's possible to do with a worksheet function but, really, it would be prohibitively complex.
– Alex M
Feb 21 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
Feb 21 at 1:49
Thanks, Alex! I really appreciate all your time and help with this!
– Jamie White
Feb 21 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
6 hours ago
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
Feb 20 at 18:11
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
Feb 20 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
Feb 21 at 1:48
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
Feb 21 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
Feb 21 at 1:49
...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
Feb 21 at 1:49
Thanks, Alex! I really appreciate all your time and help with this!
– Jamie White
Feb 21 at 18:00
Thanks, Alex! I really appreciate all your time and help with this!
– Jamie White
Feb 21 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
6 hours ago
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
6 hours ago
|
show 1 more comment
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.
answered Feb 19 at 21:18
schlebeschlebe
13310
add a comment |
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.
answered Feb 19 at 21:18
schlebeschlebe
13310
add a comment |
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.
answered Feb 19 at 21:18
schlebeschlebe
13310
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.
answered Feb 19 at 21:18
schlebeschlebe
13310
answered Feb 19 at 21:18
schlebeschlebe
13310
answered Feb 19 at 21:18
schlebeschlebe
13310
answered Feb 19 at 21:18
schlebeschlebe
13310
13310
add a comment |
add a comment |
Jamie White is a new contributor. Be nice, and check out our Code of Conduct.
Jamie White is a new contributor. Be nice, and check out our Code of Conduct.
Jamie White is a new contributor. Be nice, and check out our Code of Conduct.
Jamie White is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1407605%2fhow-to-compare-data-on-two-excel-sheets-with-vba-code%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Can you please edit your question to include sample data, expected result and what you've tried so far
– cybernetic.nomad
Feb 19 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
Feb 19 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
Feb 20 at 6:40
(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
Feb 20 at 6:40