COUNTIF Solution was too much for Excel. Is this for PowerPivot?

[Monsignor]

I solved this with 1000 rows using COUNTIF, MATCH and INDEX. But when the user adds 100,000 rows of data (with the potential for 500,000) Excel crashes. Is this better handled in PowerPivot?

The image below is a simplification of what's happening.
The source data is just the 2 columns in A and B.
The desired result is complicated by the interest of choosing only the states of interest, as opposed to rearranging the entirety of the original data.

Again, I solved this with a solution that seemed pretty elegant. Regular Excel just can handle the magnitude. I've heard that Excel gets cranky around 500,000 rows no matter how simple the data set is. I rarely deal with data sets that large.

 

[theBardd]

How about just adding a helper columns, called ID, with a formula of ="P"&COUNTIF($A$1:$A2,$A2).

Then pivot the three columns, a standard pivot, state in row labels, ID in column headings, and Person in Values with a Count aggregator.You could even set the value numberformat as "X";;

 

[Monsignor]

Very clever and I see potential here. I used a CONCATENATE and COUNTIF to create IDs but not the way you did it.
the next question is how to get the names in the cells. I tried the "X";; and it's just putting Xs in the cells.

 

[Monsignor]

Bad news.
100,000 COUNTIFs consistently crashes Excel.

 

[theBardd]

I think it will be very difficult if not impossible to get the names in the cells, so my suggestion was to use the names as column headings and just show an X where there was an intersection of state and person.

 

[Fazza]

Please explain what is required.

Or if you've already got a thread that does this (in the normal Excel forum, I guess), please provide a link to it.

thanks

 

[theBardd]

Please explain what is required.

Or if you've already got a thread that does this (in the normal Excel forum, I guess), please provide a link to it.

thanks

He did explain, perfectly clearly, in his original post!

 

[Monsignor]

No link in the regular Excel forum.

 

[Fazza]

He did explain, perfectly clearly, in his original post!

Please can you provide a link to that. Thanks

 

[theBardd]

Please can you provide a link to that. Thanks

First post in this thread