dynamically generate formula in excel

Question

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.

BigBen · Accepted Answer · 2020-12-01 19:10:30Z

2

You could use SUMPRODUCT:

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

answered Dec 1, 2020 at 19:10

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hooded 0ne · Accepted Answer · 2020-12-01 19:08:38Z

0

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

This will return either TRUE or FALSE.

answered Dec 1, 2020 at 19:08

Hooded 0ne

9971 gold badge6 silver badges14 bronze badges

1 Comment

BigBen Over a year ago

Potential for false positives though. Plus TEXTJOIN has a length limit.

Collectives™ on Stack Overflow

dynamically generate formula in excel

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related