Provide Outcome based on three conditions.

Good Morning Team,

Hope all is well.

I am hoping this lovely forum could help me build a formula, which summarizes the two different scenarios i am encountering in my attached workbook.

1) If the "Comp Country (Column B) is different than "name Country" (Column E) then Column H should say move
"Re-Move Record to Equivalent "

For example
Column B = HK
Column E = UK
Column H = "Re-Move Record to Equivalent "

2)
In total there is 17 records, (6 = UK), (11 = USA). (Given that the UK Scenario is handled in point 1) we only need to compare records where the Comp country (Column b) and Name Country( Column e) are the same.
Out of those 11 records i need find who the majority Name for a given country/ For example in my scenario "RP" owns 10 out of the 11 records, and "TG" only owns one" therefore the record with "TG" needs to change from "TG" to "RP"

If you want to display "Change account ......." to all lines than use:

=IFERROR(IF(VLOOKUP($B2,Sheet2!$A$2:$B$3,2,0)<>$E2,"Re-Move Record to Equivalent","Change Account Name from "&$D2&" to "&INDEX($D$2:$D$18,MODE(IF($E$2:$E$18=$E2,MATCH($D$2:$D$18,$D$2:$D$18,0))))),"")

If you want only first occurrence then use:

=IF(VLOOKUP($B2,Sheet2!$A$2:$B$3,2,0)<>$E2,"Re-Move Record to Equivalent",
IF($E2<>$E1,"Change Account Name from "&$D2&" to "&INDEX($D$2:$D$18,MODE(IF($E$2:$E$18=$E2,MATCH($D$2:$D$18,$D$2:$D$18,0)))),""))

Both are array formulas, so use "Ctrl+Shift+Enter instead of regular Enter for it to take effect.

Also, if you have multiple countries than use a small database for country name and code.

Did you look at Paul's attachment?

Makes sense!

Try this:

=IF(VLOOKUP($B2,Sheet2!$A$2:$B$3,2,0)<>$E2,"Re-Move Record to Equivalent",
IF(OR(INDEX($D$2:$D$18,MODE(IF($E$2:$E$18=$E2,MATCH($D$2:$D$18,$D$2:$D$18,0))))<>$D2,$E2<>$E1),"Change Account Name from "&$D2&" to "&INDEX($D$2:$D$18,MODE(IF($E$2:$E$18=$E2,MATCH($D$2:$D$18,$D$2:$D$18,0)))),""))

HI PaulM100 - This is exactly what i want thank you. However i think i have noticed some additional scenarios (which i missed . doh ).

I have attached an updated file.

But i think i need to include a way that the formula only calculates against a given company aswell. So the formula checks and validates against that particular company, when a new company is found the logic will re-start.

Scenario 3 ) We will have scenarios (row 19-26) where the all the accounts are aligned, so there no real action. So doesn't need a reason.

Scenario 4 ) If a new company is on row 27 - 34 , i need to ensure that formula calculates against that given company.

Bump thread*

Bumping after two days is perfectly acceptable. We recommend once a day.

Try this array formula:

=IF(VLOOKUP($C2,Sheet2!$A$2:$B$3,2,0)<>$F2,"Re-Move Record to Equivalent",
IF(OR(INDEX($E$2:$E$34,MODE(IF($F$2:$F$34=$F2,IF($B$2:$B$34=$B2,MATCH($E$2:$E$34,$E$2:$E$34,0)))))<>$E2,$F2<>$F1),"Change Account Name from "&$E2&" to "&INDEX($E$2:$E$34,MODE(IF($F$2:$F$34=$F2,IF($B$2:$B$34=$B2,MATCH($E$2:$E$34,$E$2:$E$34,0))))),
IF(COUNTIFS($B$2:$B$34,$B2,$C$2:$C$34,$C2,$E$2:$E$34,$E2,$F$2:$F$34,$F2)=COUNTIF($B$2:$B$34,$B2),"No action all Alligned","")))

Have you entered it as an array formula with Ctrl+Shift+Enter instead of regular Enter? As you can see in the attachment it works just fine.

You're welcome. And yes, explaining the logic behind it helps a lot. as well as uploading a sample whenever is needed.