If and statement

[bflan0524]

hello, looking for a formula that will identify if column A and b matches, example below

Biz Site	Officer code
A000000802778 <tbody> </tbody>	E63 <tbody> </tbody>
A000000802778	E63
A000008803992 <tbody> </tbody>	A6Z <tbody> </tbody>
A000008803992 <tbody> </tbody>	CUT <tbody> </tbody>

<tbody>
</tbody>

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;">

E63 <tbody> </tbody>

<tbody>
</tbody>

</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
1	Biz Site	Officer code
2	A000000802778	E63	Multiple matches
3	A000000802778	E63	Multiple matches	Previous match
4	A000008803992	A6Z
5	A000008803992	CUT

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet11​

Worksheet Formulas Cell Formula C2 =IF(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,B2)>1,"Multiple matches","") D2 =IF(COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)>1,"Previous match","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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]

Bzsite Key	Officer Acct
A000000802778	E63	Previous Match
A000000802778	E63	Previous Match
A000000802778	E63	Previous Match
A000000802778	E63	Previous Match
A000000802778	CPO
A000000802778	CPO	Previous Match
A000000802778	E63	Previous Match
A000000802778	CPO	Previous Match
A000000802778	CPO	Previous Match
A000000802778	E63	Previous Match
A000000802778	E63	Previous Match
A000000802778	E63	Previous Match
A000000802778	E63	Previous Match
A000000802778	E63	Previous Match
A000000802778	E63	Previous Match
A000000802778	E63	Previous Match

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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?

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody></tbody>

 

[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.