+ Reply to Thread
Results 1 to 10 of 10

Index and match, matching with one more item (Item A and B)

  1. #1
    Forum Contributor
    Join Date
    04-29-2011
    Location
    South Korea
    MS-Off Ver
    Excel 2019
    Posts
    176

    Index and match, matching with one more item (Item A and B)

    Dear brothers and sister,

    I have a weekly annoying task in the office. Every week, I printed the system report (use pivot table) and type data into corresponding cell manually. I believe it is quite time consuming.

    I attached the workbook and appreciate if you can give the suggestion.
    I have a table which contain branch, district, employee name column.
    I only need to refer the value of specific account manager's products-(long term mutual fund others) sales value and (Long term mutual fund MIP) volumne.

    I tried to use index and match function, however, it has limit on one matching criteria. How can I fix it? I searched in the forum, may be vlookup can help. However, I don't want to use Vlookup because it has limit that matching column should be ascending in alphatic.

    Task:

    As there is Long term mutual fund with MIP, Column C of "summary" is equal to Sheet"table" column K plus M
    As there is Long term mutual fund with other, Column B of "summar" is equal to Sheet"table" column L plus N

    Thanks everyone.
    John
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Index and match, matching with one more item (Item A and B)

    make sure that the employee name is in every row of table
    havent checked pivot table but this will do the same thing
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Index and match, matching with one more item (Item A and B)

    Hi,

    I think you misunderstand the use of VLOOKUP().
    First of all it is not restricted to an alphabetic sort provided you use the fourth parameter and set it to FALSE

    Secondly it can only return one value not a sum of values. To do that you should use SUMIF()

    However your real problem is that the Employee name column does not have names on every row. So for instance whilst

    Please Login or Register  to view this content.
    will return the correct answer of 9888 if the name is Chris Smith, it won't for David Cheung since the system has no way of knowing that C4 on the Table sheet is associated with David Cheung.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Index and match, matching with one more item (Item A and B)

    hmm over use of sumproduct on my part! go with Richard!

  5. #5
    Forum Contributor
    Join Date
    04-29-2011
    Location
    South Korea
    MS-Off Ver
    Excel 2019
    Posts
    176

    Re: Index and match, matching with one more item (Item A and B)

    Quote Originally Posted by martindwilson View Post
    hmm over use of sumproduct on my part! go with Richard!
    Quote Originally Posted by martindwilson View Post
    hmm over use of sumproduct on my part! go with Richard!
    Dear Richard and martindwilson ,

    Thanks a lot.
    However, one account manager had the wrong result:
    Richard weatherhead
    =SUMPRODUCT(--(Table!$C$2:$C$20=A18),--(Table!$F$2:$F$20="others"),Table!$L$2:$L$20)+SUMPRODUCT(--(Table!$C$2:$C$20=A18),--(Table!$F$2:$F$20="others"),Table!$N$2:$N$20)

    This formula not only include long term mutual fund other (8800+3000), it also include unwanted money market other (7000+8000).

    Is it possible using macro to make column C of sheet" table" fill in the blank column, I mean , C3 to C5 is David, C7 to 8 is emily. (if refer to attached file, C4 is blank, C8 is blank), Column E also. Can pivot table do this function

    As I copy from pivot table to template, column c and E like this. I don't change anything.

    After that, combine the text between Column E and F, therefore, it only matched long term mutual fund other?

    I just search the code online: It only work column A, may I know why?

    Sub FillColBlanks()
    'by Dave Peterson 2004-01-06
    'fill blank cells in column with value above
    'http://www.contextures.com/xlDataEntry02.html
    Dim wks As Worksheet
    Dim rng As Range
    Dim LastRow As Long
    Dim col As Long

    Set wks = ActiveSheet
    With wks
    col = activecell.column
    'or
    'col = .range("b1").column

    Set rng = .UsedRange 'try to reset the lastcell
    LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
    Set rng = Nothing
    On Error Resume Next
    Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
    .Cells.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

    If rng Is Nothing Then
    MsgBox "No blanks found"
    Exit Sub
    Else
    rng.FormulaR1C1 = "=R[-1]C"
    End If

    'replace formulas with values
    With .Cells(1, col).EntireColumn
    .Value = .Value
    End With

    End With

    End Sub
    Attached Files Attached Files
    Last edited by ronlau123; 05-19-2011 at 08:18 PM.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Index and match, matching with one more item (Item A and B)

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found h you alreadere

    And since Martin mentioned it, if you already have a pivot table which is supplying this information, why are you trying to jump through hoops with a macro when the pivot table (or its data) should be your source for any summarisation?

    Regards

  7. #7
    Forum Contributor
    Join Date
    04-29-2011
    Location
    South Korea
    MS-Off Ver
    Excel 2019
    Posts
    176

    Re: Index and match, matching with one more item (Item A and B)

    Quote Originally Posted by Richard Buttrey View Post
    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found h you alreadere

    And since Martin mentioned it, if you already have a pivot table which is supplying this information, why are you trying to jump through hoops with a macro when the pivot table (or its data) should be your source for any summarisation?

    Regards
    Thanks for you reply.

    I am not good at macro, I tried to modified it as more easy to read.
    I don't use macro because it contain more than 1000 account managers. I just only need to show/present 100 account managers. Therefore, I just use the summary.

    For column C and E, in pivot table, it may be pivot table feature, as column C , row 3 and row 4 is same person, row 4 in column C of pivot table is blank. This mean that row 3 and row 4 is for same person.

    By the way, is it possible to use macro to fill in the column of C and E and then combine text of column E and F? Is there any better ways to do that?

    I have already attached the pivot table from system. In pivot table, some rows are blank. Therefore, as I copy from pivot table to template (summary), some rows are blank

    Thanks
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-22-2011
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Index and match, matching with one more item (Item A and B)

    i Thing using concatenate the items and use with in Index and Match will be the right coice for you.
    http://tipsindeed.com/excel-function...-in-excel.html

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Index and match, matching with one more item (Item A and B)

    Hi,

    You're missing the point. That pivot table is attached to a workbook containing the source data that we can't see. If you're wanting to summarise your data and present it in a different format to the pivot table, then summarise it directly from the original data. Don't extract a pivot table, copy from the PT with blank rows and then expect to be able to summarise again when blank rows are present.

    Regards

  10. #10
    Forum Contributor
    Join Date
    04-29-2011
    Location
    South Korea
    MS-Off Ver
    Excel 2019
    Posts
    176

    Re: Index and match, matching with one more item (Item A and B)

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    You're missing the point. That pivot table is attached to a workbook containing the source data that we can't see. If you're wanting to summarise your data and present it in a different format to the pivot table, then summarise it directly from the original data. Don't extract a pivot table, copy from the PT with blank rows and then expect to be able to summarise again when blank rows are present.

    Regards
    Dear RIchard,

    Yes, your suggestion is excellent. I have a file for presentation to boss. (Account manager mutual fund sales 2.xls, sheet"summary". For pivot table, I get from the system. I don't have the source.

    I tried to linked pivotable directly. However, every week, after I receive the new file, i need to build the formula and link again.

    By the way, even if I use the formula you attached, some account manager's sales is also wrong. I only need to two set of data:
    for [ long term mutual fund] and [other], I need the sales value of column L and N ,
    for [ long term mutual fund] and [mip]. I need number of mip sales (K and M).

    Would you mind give the suggestion, how to modify the followig formula?
    =SUMPRODUCT(--(Table!$C$2:$C$20=A17),--(Table!$F$2:$F$20="others"),Table!$L$2:$L$20)+SUMPRODUCT(--(Table!$C$2:$C$20=A17),--(Table!$F$2:$F$20="others"),Table!$N$2:$N$20)

    Thanks

+ 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