VBA alternative to the countif formula

Hey guys,
Is there an alternative to the countif formula I have used in the attached spreadsheet that won’t cause excel to crash whenever I import new decks? (I often import 1000+ decks at a time)

The trick to dealing with large data sets is to put all the data into, and process it as, an array. Constantly writing to/from the worksheet takes orders of magnitude longer than manipulating the data in array form. The other trick is to minimize the number of times you have to loop the array.

See if this works for you…

Please Login or Register  to view this content.

This will take data from Sheet1 (assumes always cols A>L, starting at row 3) and then output the count for each name on Sheet2 next to the name list you had there. It will also highlight any entries that didn't match (in case of typos etc).

I have uploaded a modified file with the code already included. I removed all the COUNTIF formulas you had on Sheet1 and fixed the formula you had in col C to calc the percentages (you needed absolute references for COUNTA)

Thank you for all the fantastic solutions!

Thatandyward, I’m really grateful for the additional explanation, comments and functionality

Glad it was of use.

Not sure if you also need to sum by row on Sheet1? I assumed these were just helper cells to calculate the totals on Sheet2 so I didn't factor them into my original solution; however if you do need them then the updated code below will also output them.

Please Login or Register  to view this content.

one last question - is there a way to remove the helper columns but still use the data to create a table similar to the one below on sheet 2? I gave it a go but I could only get the sum for the entire array called “ByRowCounts” – I can’t figure out how to deal with multi-dimensional arrays

Card Name	Total used	%	number of decks that use the card	%
Marrow Weaver	6	0.24	4	0.16
Vile Darter	29	1.15	10	0.4
Zoid Battered	11	0.44	8	0.32
Irvos Mistwalker	7	0.28	5	0.2
Alpha Replicant	9	0.36	9	0.36
Therion Aetherstorm	4	0.16	4	0.16
Typhon's Pupil	2	0.08	2	0.08
Airspace Regulators	5	0.2	3	0.12
Sif's Evangel	3	0.12	3	0.12
Alpha Slayer	3	0.12	3	0.12
Typhon the Insane	8	0.32	8	0.32
Atomic Wardriver	11	0.44	7	0.28
Exicon Vanisher	4	0.16	4	0.16
Selciscorpion	5	0.2	5	0.2
Aerial Ocelot	0	0	0	0
Atmos Flyer	0	0	0	0
Windstorm Successor	1	0.04	1	0.04
Pherous Astute	0	0	0	0
Dune Runner	2	0.08	2	0.08
Daedalus Enraged	5	0.2	5	0.2

Just wanted to say thank you again for all the help I’ve received so far – I think I’ll be spending a lot time getting to grips with vba from now on after seeing how useful it is

Please Login or Register  to view this content.

Thank you Jindon!

You are welcome and thanks for the rep.