Hello Everyone,
I've run into a bit of a snag here and would like to know if anyone has a simpler solution. I have a data set with 3 columns that I want to analyze. Here is a visual:
IDENTIFIER CATEGORY MODE
SPARK PLUG KEEP KEEP
BRAKE PAD TAKE TAKE
SPARK PLUG KEEP KEEP
SPARK PLUG REMAIN KEEP
I have a little over 220k rows and am trying to use a simpler formula to return the most frequent instance of the CATEGORY column based on the value in the IDENTIFIER column in the MODE column. So in this case, for SPARK PLUG the most common CATEGORY is "KEEP" which what I want to return in the MODE column every time SPARK PLUG appears.
currently I have this array formula:
=INDEX($Q$2:$Q$246076,MODE(IF($P$2:$P$246076=P2,MATCH($Q$2:$Q$246076,$Q$2:$Q$246076,0))))
It takes hours for this to run due to the sheer size of the dataset and I can break it up because there could be multiple instances of the IDENTIFIER in my data. Any simpler solutions?
thanks!
I've run into a bit of a snag here and would like to know if anyone has a simpler solution. I have a data set with 3 columns that I want to analyze. Here is a visual:
IDENTIFIER CATEGORY MODE
SPARK PLUG KEEP KEEP
BRAKE PAD TAKE TAKE
SPARK PLUG KEEP KEEP
SPARK PLUG REMAIN KEEP
I have a little over 220k rows and am trying to use a simpler formula to return the most frequent instance of the CATEGORY column based on the value in the IDENTIFIER column in the MODE column. So in this case, for SPARK PLUG the most common CATEGORY is "KEEP" which what I want to return in the MODE column every time SPARK PLUG appears.
currently I have this array formula:
=INDEX($Q$2:$Q$246076,MODE(IF($P$2:$P$246076=P2,MATCH($Q$2:$Q$246076,$Q$2:$Q$246076,0))))
It takes hours for this to run due to the sheer size of the dataset and I can break it up because there could be multiple instances of the IDENTIFIER in my data. Any simpler solutions?
thanks!
