Formula to perform an "if" on a grouping of rows in another table-CodePudding

BACKGROUND

I have the following table (Table1) with some dummy data.

ColA, ColB, ColC, GroupId
1, 100, [Set to ColA or ColB value], GroupA
2, 200, [Set to ColA or ColB value], GroupA
3, 300, [Set to ColA or ColB value], GroupA
4, 400, [Set to ColA or ColB value], GroupB
5, 500, [Set to ColA or ColB value], GroupB

As you can see, the GroupId column is used to logically group rows. I have 2 groupings above: GroupA and GroupB.

ColC is set to either ColA or ColB depending on various factors. That part is done.

Below Table1, I need another table (Table2) that tells me if every row within a grouping has ColA = ColC.

For example:

GroupID, ColAEqColC
GroupA,
GroupB,

PROBLEM

So, logically I want the formula in ColAEqColC to be:

if(For all the rows in Table1 where Table2.GroupId = Table1.GroupId, is ColA = ColC?, "Yes", "No")

However, I don't know how to get all that into the first parameter of an if statement.

EXAMPLE:

ColA, ColB, ColC, GroupId
1, 100, 1, GroupA
2, 200, 2, GroupA
3, 300, 3, GroupA
4, 400, 4, GroupB
5, 500, 500, GroupB

GroupID, ColAEqColC
GroupA, Yes
GroupB, No

CodePudding user response：

Try this:

=IF(SUM((Table1[GroupId]=Table2[@GroupID])*(Table1[ColA]=Table1[ColC]))=COUNTIFS(Table1[GroupId],Table2[@GroupID]),"Yes","No")

CodePudding user response：

Utilising the Dynamic Array function FILTER:

=COUNTA(FILTER(Table1[[GroupId]],(Table1[ColA]=Table1[[ColC]])*(Table1[[GroupId]]=[@[GroupId]])))=COUNTIFS(Table1[[GroupId]],[@[GroupId]])

Returns TRUE/FALSE. Wrap in IF( ... , "Yes", "No") if you really want text output