CODEWITHSUNDEEP
excelformula
Koosh
Koosh

Reputation: 896

dynamically generate formula in excel

Lets say I have two sets of data and want to compare each row and column to make sure that they are identical.

Both sets of data have the same number of Columns and rows, say first set is Columns A-G, 2nd set of data is on the same tab an goes from H-N (in reality I actually have 50+ columns in each set).

Typically what I do when I don't have a lot of columns, i do something like:

=if(AND(A2 = h2, B2=i2, c2=j2),"Good","Bad")

Once I have a formula, then I press the little square and drag it down across all rows. This is able to quickly show me whether there is data difference in any of the columns or not.

However in this case I have a lot of columns to compare. Is there a quicker way to do this, or generate dynamically somehow?

Thanks.

Upvotes: 0

Views: 40

Answers (2)

BigBen
BigBen

Reputation: 49998

You could use SUMPRODUCT:

=IF(SUMPRODUCT(--(A2:G2=H2:N2))=0,"Good","Bad")

enter image description here

Upvotes: 2

Hooded 0ne
Hooded 0ne

Reputation: 997

=TEXTJOIN(,,A1:C1)=TEXTJOIN(,,h1:j1)

This will return either TRUE or FALSE.

Upvotes: 0

Related Questions