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

2016	2017	AB 205	AB	205	TECHNICAL SKILLS AND COLLISION REPAIR	12
2017	2018	AB 205	AB	205	TECHNICAL SKILLS AND COLLISION REPAIR	12
2002	2003	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2003	2004	AB 280A	AB	280A	CE: AUTO BODY REPAIR	VARIABLE
2004	2005	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2005	2006	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2006	2007	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2007	2008	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2008	2009	AB 280A	AB	280A	CE: AUTO BODY	VARIABLE
2009	2010	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2010	2011	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2011	2012	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2012	2013	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2013	2014	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2014	2015	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2015	2016	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2016	2017	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2017	2018	AB 280A	AB	280A	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2002	2003	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR SEMINAR	VARIABLE
2003	2004	AB 280B	AB	280B	CE: AUTO BODY REPAIR- SEMINAR	VARIABLE
2004	2005	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR	VARIABLE
2005	2006	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR	VARIABLE
2006	2007	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR	VARIABLE
2007	2008	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR	VARIABLE
2008	2009	AB 280B	AB	280B	CE: AUTO BODY REPAIR - SEMINAR	VARIABLE
2009	2010	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR	VARIABLE
2010	2011	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR	VARIABLE
2011	2012	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR	VARIABLE
2012	2013	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR	VARIABLE
2013	2014	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR	VARIABLE
2014	2015	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR	VARIABLE
2015	2016	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR	VARIABLE
2016	2017	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR	VARIABLE
2017	2018	AB 280B	AB	280B	COOPERATIVE EDUCATION: AUTO BODY REPAIR - SEMINAR	VARIABLE
2002	2003	AB 9120	AB	9120	AUTO BODY RESTORATION	3
2003	2004	AB 9120	AB	9120	AUTO BODY RESTORATION	3
2004	2005	AB 9120	AB	9120	AUTO BODY RESTORATION	3
2005	2006	AB 9120	AB	9120	AUTO BODY RESTORATION	3
2006	2007	AB 9120	AB	9120	AUTO BODY RESTORATION	3
2007	2008	AB 9120	AB	9120	AUTO BODY RESTORATION	3
2008	2009	AB 9120	AB	9120	AUTO BODY RESTORATION	3
2009	2010	AB 9120	AB	9120	AUTO BODY RESTORATION	3
2010	2011	AB 9120	AB	9120	AUTO BODY RESTORATION	3
2002	2003	AB 9121	AB	9121	VINTAGE AUTO RESTORATION PROCESS	2

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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.

H2	=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)

<tbody>
</tbody>

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.

H2	=COUNTIFS($C$2:C2,C2,$F$2:F2,F2,$G$2:G2,G2)
I2	=REPT("Original",H2=1) & REPT("Duplicate", H2>1)

<tbody>
</tbody>

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.

H2	=C2&" "&F2&" "&G2
I2	=COUNTIF($H$2:H2, H2)
J2	=REPT("Original", I2=1) & REPT("Duplicate", I2<> 1)

<tbody>
</tbody>

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.