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