I have an Excel spreadsheet with about 25,000 rows of data in Column A (Sales Ord) and Column B (P-Code). I have about 100 Promo Codes in Column F.
I’m trying to count the unique number of Sales Orders (Sales Ord) that contain the P-Code as identified in Column F (Promo Code) and put the results in Column E (Qty).
My spreadsheet should look something like:
COL A COL B COL E COL F
SALES ORD P-CODE QTY PROMO CODE
123456 Q1000 2 Z4200
123456 Q1000 3 Q1000
123456 Q1000 2 R5000
789123 Z4200 1 A3000
999999 Z4200
888888 Q1000
888888 Q1000
888888 Q1000
888888 Q1000
777777 R5000
777777 R5000
777777 R5000
777777 R5000
333333 R5000
333333 R5000
333333 R5000
333333 R5000
333333 R5000
555555 Q1000
444444 A3000
444444 A3000
444444 A3000
444444 A3000
444444 A3000
444444 A3000
444444 A3000
444444 A3000
444444 A3000
444444 A3000
444444 A3000
444444 A3000
I’m using the following array formula, which works OK on a small number of rows but takes forever when running against 25,000 rows.
{=SUM(IF(FREQUENCY(IF($A$2:$A$25199<>"",
IF($B$2:$B$25199=F2,MATCH("~"&$A$2:$A$25199,$A$2:$A$25199&"",0))),
ROW($A$2:$A$25199)-ROW($A$2)+1),1))}
Can anyone help me tweak the formula so it will run faster?
Thanks,
Bookmarks