CODEWITHSUNDEEP
excelexcel-formulacomparison
Deepak
Deepak

Reputation: 1258

Excel How to compare 2 Column Ranges

I am trying to compare two set of column ranges in Excel.

i am aware of the standard comparison formula :

Eg. =(A1=E1)

What I am looking for is a replacement for the following formula =AND(A1=E1,B1=F1,C1=G1)

since the number of columns is large I was thinking if it is possible to use a cell range.

Upvotes: 2

Views: 167

Answers (3)

Chronocidal
Chronocidal

Reputation: 8081

=SUMPRODUCT(PRODUCT(--(A1:D1=E1:H1)))

The SUMPRODUCT is to force it to evaluate as an Array Formula

The PRODUCT multiplies all of the values

The -- converts TRUE into 1 and FALSE into 0

A1:D1=E1:H1, evalauated as an Array Formula, gives the array {A1=E1,B1=F1,C1=G1,D1=H1}

So, the array is calcuated as a list of TRUE and FALSE. The -- converts this into a list of 1s and 0s, and the PRODUCT multiplies all the values in the list together. (Multiplication of 1 and 0 is the same as AND with TRUE and FALSE) - if you use "Evaluate Formula", you can step through and watch it happen

{EDIT} As Tom Sharpe pointed out in his answer, I've overcomplicated this slightly - you can use AND instead of PRODUCT, like so: =SUMPRODUCT(--AND(A1:D1=E1:H1))

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34390

A bit slower than @Chronocidal, just to observe that

=AND(A1:C1=E1:G1)

also works if entered as an array formula using

CtrlShiftEnter

Upvotes: 1

user4039065
user4039065

Reputation:

Perhaps,

=a1&b1&c1=e1&f1&g1

Larger ranges could be handled with the newer .

=textjoin("", true, a1:c1)=textjoin("", true, e1:g1)

If you don't have the newer TextJoin function, here is a quick VBA UDF (user defined function) that you can use to substitute.

function tj(rng as range, optional delim as string = ";") as string
   dim val as range, str as string
   for each val in rng
       str = str & delim & val.value2
   next val
   tj = mid(str, len(delim)+1)
end function

Upvotes: 0

Related Questions