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

[ronlau123]

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

[martindwilson]

make sure that the employee name is in every row of table
havent checked pivot table but this will do the same thing

[Richard Buttrey]

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

=SUMIF(Table!C:C,Summary!A17,Table!L:L)+SUMIF(Table!C:C,Summary!A17,Table!N:N)

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

[martindwilson]

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

[ronlau123]

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

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

[Richard Buttrey]

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

[ronlau123]

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

[Richard Buttrey]

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

[gobishg]

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