If and statement

[bflan0524]

hello, looking for a formula that will identify if column A and b matches, example below[TABLE="width: 500"]
<tbody>[TR]
[TD]Biz Site[/TD]
[TD]Officer code[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl67, width: 102"]A000000802778[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl67, width: 77"]E63[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]E63[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl67, width: 102"]A000008803992[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 58"]
<tbody>[TR]
[TD="class: xl67, width: 77"]A6Z[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl67, width: 102"]A000008803992[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl67, width: 77"]CUT[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

so for the first two rows i would want to be able to identify that both the biz site and officer code match, and in rows 3 & 4 the sites match but the officer codes dont
****** id="cke_pastebin" style="position: absolute; top: 68.8px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl67, width: 77"]E63[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[bflan0524]

not really sure why it formatted like this but hopefully still makes sense

 

[Eric W]

I'm not sure if you want to flag all instances of matches, or only if there's a previous match, so here are 2 formulas for both ways:

	A	B	C	D	E
Multiple matches
Multiple matches	Previous match

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Biz Site[/TD]
[TD="bgcolor: #FAFAFA"]Officer code[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]A000000802778[/TD]
[TD="bgcolor: #FAFAFA"]E63[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]A000000802778[/TD]
[TD="bgcolor: #FAFAFA"]E63[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]A000008803992[/TD]
[TD="bgcolor: #FAFAFA"]A6Z[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]A000008803992[/TD]
[TD="bgcolor: #FAFAFA"]CUT[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,B2)>1,"Multiple matches","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)>1,"Previous match","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hope this helps.

 

[bflan0524]

hmm, this didn't work, is there a way for me to upload the file i did so i can show?

 

[Eric W]

This forum doesn't allow uploads. You can use a tool like the HTML Maker in my signature to show a screenprint, which is what I used. You can also upload a picture or a full file to a file sharing service and post a link. However, many people will not or cannot open such files for security reasons.

When you say "didn't work", what do you mean? Error message? If so, what? Wrong results? Please post a sample screen shot with the bad results on it, and the expected results. What version of Excel and/or Windows are you using? COUNTIFS was created in Excel 2007, so if you have an older version, this formula won't work, but we can probably come up with something else.

 

[bflan0524]

[TABLE="width: 289"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Bzsite Key[/TD]
[TD]Officer Acct[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A000000802778 [/TD]
[TD]E63[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]E63[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]E63[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]E63[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]CPO[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]CPO[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]E63[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]CPO[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]CPO[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]E63[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]E63[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]E63[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]E63[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]E63[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]E63[/TD]
[TD]Previous Match[/TD]
[/TR]
[TR]
[TD]A000000802778[/TD]
[TD]E63[/TD]
[TD]Previous Match[/TD]
[/TR]
</tbody>[/TABLE]

so i know this isnt great but this is the only way i could figure out how to put it up, you could assume columns are A, B and C
maybe i am not thinking of the right approach but in this example you could see under officer code column there are two what i will call owners, E63 and CPO. i would identify this as what i call a split ownership as opposed to one owner for all the sites.
that is what i am trying to identify if the ownership of these sites are split or just one officer code who owns it, does that make sense?[TABLE="width: 1405"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>[/TABLE]

 

[Eric W]

That's a little different from what I originally understood you to want. So given the example you showed, what would you want the results to be? You have a single site ID, and 2 owners. Would you want every line identified as "split"? If so, put this in C2:


=IF(COUNTIF($A$2:$A$20,A2)<>COUNTIFS($A$2:$A$20,A2,$B$2:$B$20,B2),"Split","")

 

[bflan0524]

that worked thank you, so i always took countif as if you are actually counting the # of times a data point is the same, which is obv wrong looking at this. can you break this formula down for me? thanks again for the help

 

[Eric W]

It's actually pretty easy once you grasp the clever idea behind it (which I happily stole from someone else!). The first COUNTIF simply counts how many keys you have which match the key on the current line. The second COUNTIFS counts how many keys you have that match the key on the current line, AND that match the owner on the current line. If those 2 numbers are the same, then all the keys must have the same owner. If the 2 numbers are different, then there must be at least one matching key with a different owner.

Glad it works for you.