Using =if(countif), slow data, and much more fun!

[jasbentle]

I have a very large database that I am trying to certain information and am having a problem doing so because of the size.

What I want to accomplish:
I would like two things
1) if Column C, F, and G are the same put "Original" in Column H
2) if Column C, F, and G are the same put but already have an "Original" instance, put "Duplicate" in column H

What I'm trying to accomplish is finding out if any of the text in F is different and if the credits in column G has changed. Here is the formula I put together.
=IF(COUNTIFS($C$2:C2,C2,$F$2:F2,F2,$G$2:G2,G2)>1, "Duplicate","Original")

Great right? It "works" but is so sluggish because I have almost 50K rows of data so any time I make any sort of change, it takes 30 min. or so to update. I have tried turning off auto calculation options

Also, for bonus points if anyone has ideas (that wont bog down the spreadsheet) on how I can have a range if all the information and have it put the YEAR LOW and YEAR HIGH of when all the said data is EXACTLY the same, that would be great!

TIA

Below is the spreadsheet below for reference.

A B C D E F G

[TABLE="width: 792"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]AB 205[/TD]
[TD]AB[/TD]
[TD]205[/TD]
[TD]TECHNICAL SKILLS AND COLLISION REPAIR[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]AB 205[/TD]
[TD]AB[/TD]
[TD]205[/TD]
[TD]TECHNICAL SKILLS AND COLLISION REPAIR[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD]2003[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]CE: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD]2005[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]2006[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD]2007[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD]2008[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]2009[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]CE: AUTO BODY[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2009[/TD]
[TD]2010[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD]2012[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]AB 280A[/TD]
[TD]AB[/TD]
[TD]280A[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD]2003[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]CE: AUTO BODY REPAIR- SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD]2005[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]2006[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD]2007[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD]2008[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]2009[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]CE: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2009[/TD]
[TD]2010[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2011[/TD]
[TD]2012[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]2013[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]2014[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]2016[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]2018[/TD]
[TD]AB 280B[/TD]
[TD]AB[/TD]
[TD]280B[/TD]
[TD]COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR[/TD]
[TD]VARIABLE[/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD]2003[/TD]
[TD]AB 9120[/TD]
[TD]AB[/TD]
[TD]9120[/TD]
[TD]AUTO BODY RESTORATION[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]AB 9120[/TD]
[TD]AB[/TD]
[TD]9120[/TD]
[TD]AUTO BODY RESTORATION[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2004[/TD]
[TD]2005[/TD]
[TD]AB 9120[/TD]
[TD]AB[/TD]
[TD]9120[/TD]
[TD]AUTO BODY RESTORATION[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2005[/TD]
[TD]2006[/TD]
[TD]AB 9120[/TD]
[TD]AB[/TD]
[TD]9120[/TD]
[TD]AUTO BODY RESTORATION[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2006[/TD]
[TD]2007[/TD]
[TD]AB 9120[/TD]
[TD]AB[/TD]
[TD]9120[/TD]
[TD]AUTO BODY RESTORATION[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD]2008[/TD]
[TD]AB 9120[/TD]
[TD]AB[/TD]
[TD]9120[/TD]
[TD]AUTO BODY RESTORATION[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]2009[/TD]
[TD]AB 9120[/TD]
[TD]AB[/TD]
[TD]9120[/TD]
[TD]AUTO BODY RESTORATION[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2009[/TD]
[TD]2010[/TD]
[TD]AB 9120[/TD]
[TD]AB[/TD]
[TD]9120[/TD]
[TD]AUTO BODY RESTORATION[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]AB 9120[/TD]
[TD]AB[/TD]
[TD]9120[/TD]
[TD]AUTO BODY RESTORATION[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2002[/TD]
[TD]2003[/TD]
[TD]AB 9121[/TD]
[TD]AB[/TD]
[TD]9121[/TD]
[TD]VINTAGE AUTO RESTORATION PROCESS[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[thisoldman]

These are untested beyond the 44 lines of the example spreadsheet.

Rewriting the formula to remove the IF might speed things up. Conditional branching can be a cause of slow calculation performance.

[TABLE="class: grid"]
<tbody>[TR]
[TD="class: xl65"]H2[/TD]
[TD="class: xl65"]=REPT("Original",COUNTIFS($C$2:C2,C2,$F$2:F2,F2,$G$2:G2,G2)=1)&REPT("Duplicate",COUNTIFS($C$2:C2,C2,$F$2:F2,F2,$G$2:G2,G2)<> 1)[/TD]
[/TR]
</tbody>[/TABLE]


That's OK but the COUNTIFS is repeated. That part of the formula could be placed into a separate column so that the COUNTIFS is only calculated once per row.

[TABLE="class: grid"]
<tbody>[TR]
[TD="class: xl65"]H2[/TD]
[TD="class: xl65"]=COUNTIFS($C$2:C2,C2,$F$2:F2,F2,$G$2:G2,G2)[/TD]
[/TR]
[TR]
[TD="class: xl65"]I2[/TD]
[TD="class: xl65"]=REPT("Original",H2=1) & REPT("Duplicate", H2>1)[/TD]
[/TR]
</tbody>[/TABLE]


In the original formula in cell H2, there are three comparison operations being performed.
In cell H3, there are six comparison operations (does C3 equal C2, does C3 equal C2, does F3 equal F2, does F3 equal F3, does H3 ...).
...
In cell H49999 there are 149,997 comparison operations being performed.

You add them all up and by the time Excel gets to H50000, it's completed 3,749,925,000 comparison operations and has another 150,000 to do.

The following might be a speedup; it could make things worse, or it might not make any perceptible difference in speed. Text operations in Excel are slower than numeric operations, but we reduce the number of comparison operations to one-third of the original number by concatenating the column C, F, and G cells in another helper column.

[TABLE="class: grid"]
<tbody>[TR]
[TD]H2[/TD]
[TD]=C2&" "&F2&" "&G2[/TD]
[/TR]
[TR]
[TD]I2[/TD]
[TD]=COUNTIF($H$2:H2, H2)[/TD]
[/TR]
[TR]
[TD]J2[/TD]
[TD]=REPT("Original", I2=1) & REPT("Duplicate", I2<> 1)[/TD]
[/TR]
</tbody>[/TABLE]

Note we're now using COUNTIF, without the "S" in cell I2. The ampersand, the "&", in cell H2 is the concatenation operator.


You could convert some of the formulas to values and calculation time would be greatly reduced.

 

[Aladin Akyurek]

In H2 enter and copy down:

=$C2&$F2&$G2

In I2 enter and copy down:

=IF(ISNA(MATCH($H2,$H$1:H1,0)),"original","duplicate")

This set up should be faster.

 

[thisoldman]

Aladin Akyurek,

I am very jealous of your ability to cut through to concise formulas. Perhaps I should try to learn Lisp again?

 

[Aladin Akyurek]

Aladin Akyurek,

I am very jealous of your ability to cut through to concise formulas. Perhaps I should try to learn Lisp again?

Thanks for the kind words. And I'd say yes for it is the language I love the most.