+ Reply to Thread
Results 1 to 3 of 3

Display a range of results with "*"&A1&"*" condition

  1. #1
    Registered User
    Join Date
    10-05-2007
    Posts
    74

    Display a range of results with "*"&A1&"*" condition

    please see attached - any help to achieve the yellow area results

    Both worksheets in reality are huge, and seperate files. I have explored an INDEX helper column and VLOOKUPs with no luck. I would prefer to avoid SUMPRODUCT as the spreadsheets really slow down.

    Any help really appreciated :-)
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Display a range of results with "*"&A1&"*" condition

    I went with this formula in cell F2, copied across and down. Not sure it will be faster than a SUMPRODUCT, though:

    =IF(VALUE(MID(F$1,8,1))>$D2,"-",INDEX(OFFSET(Data!$A:$A,0,ISODD(COLUMN())),LARGE(INDEX(ROW(Data!$A$1:$A$10000)*(Data!$F$1:$F$10000=$C2)*(ISNUMBER(FIND($B2,Data!$B$1:$B$10000))),0),COUNTIFS(Data!$F:$F,$C2, Data!$B:$B,"*" & $B2 & "*")-(VALUE(MID(F$1,8,1))-1))))

  3. #3
    Registered User
    Join Date
    10-05-2007
    Posts
    74

    Re: Display a range of results with "*"&A1&"*" condition

    The awesomeness of you help is so appreciated!
    That works really well. I will test the speed vs. SUMPRODUCT in the coming weeks!

+ 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