Tjyylli

Separate environment for personal and development use under macOS

Why does PHOTOREC keep finding files?

Non-Cancer terminal illness that can affect young (age 10-13) girls?

Macro expansion inside href

TikZ graph edges not drawn nicely

Building an exterior wall within an exterior wall for insulation

I have trouble understanding this fallacy: "If A, then B. Therefore if not-B, then not-A."

Count repetitions of an array

Boss asked me to sign a resignation paper without a date on it along with my new contract

Why didn't Tom Riddle take the presence of Fawkes and the Sorting Hat as more of a threat?

Short story where statues have their heads replaced by those of carved insect heads

How does Leonard in "Memento" remember reading and writing?

How do you voice extended chords?

Is a new boolean field better than null reference when a value can be meaningfully absent?

Current across a wire with zero potential difference

How does one write from a minority culture? A question on cultural references

Why did Democrats in the Senate oppose the Born-Alive Abortion Survivors Protection Act?

Citing paid articles from illegal web sharing

Has Britain negotiated with any other countries outside the EU in preparation for the exit?

Bash script to truncate subject line of incoming email

In Linux what happens if 1000 files in a directory are moved to another location while another 300 files were added to the source directory?

Is using an 'empty' metaphor considered bad style?

Square Root Distance from Integers

Why are all my replica super soldiers young adults or old teenagers?

Find matching rows between two different data sets in Excel

Find the different two Excel sheets when having identical rowMerge rows and join matching valuesIn Excel I need to find data matches between two arrays - one horizontal and one verticalexcel: merging to data sets based on a common fieldExcel: Matching and counting records (rows) to multiple conditions and unique valuesExcel : Merging Data setsHow can I combine all of the rows from two Excel worksheets into a third worksheet?Find row matching two criteriaconvert one column data to two column data in excelExcel: Aggregating two column sets into one

0

1

I have two data sets as shown below. In the first dataset I want to know if the row exists in the 2nd dataset and return either "Found" or "Missing"

Is there any way I can achieve this in Excel?

microsoft-excel

share|improve this question

edited Feb 8 '15 at 7:22

fixer1234

18.8k144982

asked Feb 8 '15 at 6:54

OmniOmni

87

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Oh sorry my bad, I'm new to the stack network. Is it okay if I change it to Excel?
  
  – Omni
  Feb 8 '15 at 7:16
  

  
  
  
  

add a comment | 

0

1

I have two data sets as shown below. In the first dataset I want to know if the row exists in the 2nd dataset and return either "Found" or "Missing"

Is there any way I can achieve this in Excel?

microsoft-excel

share|improve this question

edited Feb 8 '15 at 7:22

fixer1234

18.8k144982

asked Feb 8 '15 at 6:54

OmniOmni

87

bumped to the homepage by Community♦ 6 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Oh sorry my bad, I'm new to the stack network. Is it okay if I change it to Excel?
  
  – Omni
  Feb 8 '15 at 7:16
  

  
  
  
  

add a comment | 

I have two data sets as shown below. In the first dataset I want to know if the row exists in the 2nd dataset and return either "Found" or "Missing"

Is there any way I can achieve this in Excel?

microsoft-excel

share|improve this question

edited Feb 8 '15 at 7:22

fixer1234

18.8k144982

asked Feb 8 '15 at 6:54

OmniOmni

87

share|improve this question

edited Feb 8 '15 at 7:22

fixer1234

18.8k144982

asked Feb 8 '15 at 6:54

OmniOmni

87

share|improve this question

edited Feb 8 '15 at 7:22

fixer1234

18.8k144982

asked Feb 8 '15 at 6:54

OmniOmni

87

edited Feb 8 '15 at 7:22

fixer1234

18.8k144982

edited Feb 8 '15 at 7:22

fixer1234

18.8k144982

asked Feb 8 '15 at 6:54

OmniOmni

87

asked Feb 8 '15 at 6:54

OmniOmni

87

1 Answer
1

active

oldest

votes

0

One way to do this is with a helper column so that you can compare all four cells against a single cell and use straightforward functions. Say your first dataset is in A1:D3 and the second is in F1:I3. In J1, enter:

=F1&G1&H1&I1

Copy this down the column. This will concatenate the four cells into one value. Enter in E1:

=IF(ISNA(MATCH(A1&B1&C1&D1,J$1:J$3,0)),"Missing","Found")

Adjust the J$1:J$3 range to include all of your actual rows and then copy this down the column.

For each row in dataset1, it concatenates the four cells into a single value and looks for a match in the helper column. If there is no match, the ISNA test will be true and it will return "Missing". Otherwise, it means it found a match and it returns "Found".

This screenshot is from LibreOffice Calc, so the layout will look a little different from Excel.

share|improve this answer

edited Feb 8 '15 at 7:47

answered Feb 8 '15 at 7:42

fixer1234fixer1234

18.8k144982

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
});


}
});

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f875154%2ffind-matching-rows-between-two-different-data-sets-in-excel%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

One way to do this is with a helper column so that you can compare all four cells against a single cell and use straightforward functions. Say your first dataset is in A1:D3 and the second is in F1:I3. In J1, enter:

=F1&G1&H1&I1

Copy this down the column. This will concatenate the four cells into one value. Enter in E1:

=IF(ISNA(MATCH(A1&B1&C1&D1,J$1:J$3,0)),"Missing","Found")

Adjust the J$1:J$3 range to include all of your actual rows and then copy this down the column.

For each row in dataset1, it concatenates the four cells into a single value and looks for a match in the helper column. If there is no match, the ISNA test will be true and it will return "Missing". Otherwise, it means it found a match and it returns "Found".

This screenshot is from LibreOffice Calc, so the layout will look a little different from Excel.

share|improve this answer

edited Feb 8 '15 at 7:47

answered Feb 8 '15 at 7:42

fixer1234fixer1234

18.8k144982

add a comment | 

0

One way to do this is with a helper column so that you can compare all four cells against a single cell and use straightforward functions. Say your first dataset is in A1:D3 and the second is in F1:I3. In J1, enter:

=F1&G1&H1&I1

Copy this down the column. This will concatenate the four cells into one value. Enter in E1:

=IF(ISNA(MATCH(A1&B1&C1&D1,J$1:J$3,0)),"Missing","Found")

Adjust the J$1:J$3 range to include all of your actual rows and then copy this down the column.

For each row in dataset1, it concatenates the four cells into a single value and looks for a match in the helper column. If there is no match, the ISNA test will be true and it will return "Missing". Otherwise, it means it found a match and it returns "Found".

This screenshot is from LibreOffice Calc, so the layout will look a little different from Excel.

share|improve this answer

edited Feb 8 '15 at 7:47

answered Feb 8 '15 at 7:42

fixer1234fixer1234

18.8k144982

add a comment | 

One way to do this is with a helper column so that you can compare all four cells against a single cell and use straightforward functions. Say your first dataset is in A1:D3 and the second is in F1:I3. In J1, enter:

=F1&G1&H1&I1

Copy this down the column. This will concatenate the four cells into one value. Enter in E1:

=IF(ISNA(MATCH(A1&B1&C1&D1,J$1:J$3,0)),"Missing","Found")

Adjust the J$1:J$3 range to include all of your actual rows and then copy this down the column.

For each row in dataset1, it concatenates the four cells into a single value and looks for a match in the helper column. If there is no match, the ISNA test will be true and it will return "Missing". Otherwise, it means it found a match and it returns "Found".

This screenshot is from LibreOffice Calc, so the layout will look a little different from Excel.

share|improve this answer

edited Feb 8 '15 at 7:47

answered Feb 8 '15 at 7:42

fixer1234fixer1234

18.8k144982

=F1&G1&H1&I1

=IF(ISNA(MATCH(A1&B1&C1&D1,J$1:J$3,0)),"Missing","Found")

share|improve this answer

edited Feb 8 '15 at 7:47

answered Feb 8 '15 at 7:42

fixer1234fixer1234

18.8k144982

answered Feb 8 '15 at 7:42

fixer1234fixer1234

18.8k144982

answered Feb 8 '15 at 7:42

fixer1234fixer1234

18.8k144982