+ Reply to Thread
Results 1 to 2 of 2

Summary matrix

  1. #1
    Registered User
    Join Date
    01-13-2011
    Location
    scandinavia
    MS-Off Ver
    Excel 2007
    Posts
    27

    Summary matrix

    Hi,

    Attached you can find a sample workbook which describes fairly my challenge, -once you look at it you'll now what i'm looking for.

    Suppose the names in green are given, so you could in principle look these names up in "Data 1" (see sample) and paste categories in row 3 (the stuff in red).

    I'm almost looking for some way to vlookup...actually I could use a vlookup if each name in Data 1 occured only once, then in row 3 from column J is just a vlookup, but i have names occuring in different categories.

    NB: I need the solution to be in VBA! ..at least a hint is appreciated.
    If interrested:
    I have made a solution that works and give me the desired output, but the problem is that it's not an efficient way at all since it takes me more than 3 min or so to paste my "Matrix" and my data in my original problem is large (up to 550 rows (Data 1 in the sample attached)).

    My own solving of my problem was to run through all 550 rows (Data 1) and see if one row matches the given name that I already have in my matrix (green names in the sample attached), and that took time!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Summary matrix (must be easy - vlookup item which is linked to multiple categorie

    Hello,

    If both ranges are in same length & names are in same positions try to use SUMIFS.

    J4, copy down & across.

    =SUMIFS($E$24:$E$40,$D$24:$D$40,$I4,$E$4:$E$20,J$3)

    Use custom cell format as 0;0;;
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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