+ Reply to Thread
Results 1 to 2 of 2

Excel (2003) array formula taking too long

  1. #1
    Registered User
    Join Date
    04-27-2007
    Location
    Chicago, IL
    Posts
    8

    Excel (2003) array formula taking too long

    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,
    Krazy (Bill) Kasper

  2. #2
    Forum Contributor
    Join Date
    10-17-2007
    Location
    Washington, DC
    Posts
    103
    can you upload an excel file example of the data you have and the formulas you have so far? I believe this will become a lot easier with a concatenate, but I can not get my hands around what you are trying to do.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1