INDEX and MATCH , "Excel ran out of resources while attempting to calculate one or more formulars.."

[DIARYTODAY]

Hi All,

I am new to VBA and need some help with few formulas. The first question is the Index and Match formula, it does not work for me. I have 2 sets of data as follows:
Table 1: Employee Name(A1), Client Name that EE works this month (B1), Total Days EE works (C1)/month <-- this is my data, it changes every month
Table 2: Employee Name (D1), CLient Name (E1), Client Rate (F1)<-- this data is set (1 EE can have different rates for diff client)

I need to calculate Total Cost per EE per month. So my formula is :

G1=(INDEX(F:F,MATCH(A1&B1,D:D&E:E,0),1))*C1) CSE

It seems like it works but when I double click the corner of the cell G1 to copy it down, it copies all the way down to the bottle of the page pass the data table and it starts giving me the msg "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated".

Please help! I need this to work for my report. Is my formula wrong?
Thank you in advance.

 

[gaz_chops]

So if client is AEM change it to AEP, if B1 contains Advanced return AEP - ADV, if client begins with anything else then return the 1st 3 characters, with - ADV if Advanced is found?

=IF(ISERROR(FIND("Advanced",B1)),IF(LEFT(B1,3)="AEM","AEP",LEFT(B1,3)),IF(LEFT(B1,3)="AEM","AEP - ADV",LEFT(B1,3)&" - ADV"))

 

[DIARYTODAY]

it only works half way , ie for prospect AEM-ADVANCED, It does not give me AEP - ADV, it only gives me AEP.

but I found this one and it works =IF(LEFT(B1,3)="AEM","AEP",LEFT(B1,3))&IF(ISNUMBER(FIND("ADVANCED",B1))," - ADV","")

Thank you for your help gaz_chops

 

[gaz_chops]

This is what I get with my formula & the one you just posted!

[TABLE="width: 397"]
<tbody>[TR]
[TD][/TD]
[TD]AZM-Advanced[/TD]
[TD]AEM-Advanced[/TD]
[TD]AEM-sdfgv[/TD]
[TD]ADG[/TD]
[TD]AFS-Advanced[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mine[/TD]
[TD]AZM - ADV[/TD]
[TD]AEP - ADV[/TD]
[TD]AEP[/TD]
[TD]ADG[/TD]
[TD]AFS - ADV[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yours[/TD]
[TD]AZM[/TD]
[TD]AEP[/TD]
[TD]AEP[/TD]
[TD]ADG[/TD]
[TD]AFS[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Realised your client is in uppercase!

=IF(ISERROR(SEARCH("Advanced",B1)),IF(LEFT(B1,3)="AEM","AEP",LEFT(B1,3)),IF(LEFT(B1,3)="AEM","AEP - ADV",LEFT(B1,3)&" - ADV"))

Glad you got sorted.

 

[Snakehips]

DIARYTODAY, Gaz,

Just taken a quick look at the original query as requested by Gaz.

Not sure in what way vba relates to this? Can you clarify?

Does the below reflect the data setup? Would appear odd to have a lookup for rates amidst the list of data?

With the existing concatenated match array formula it will be somewhat sluggish and formulas / applied ranges must be kept to a minimum.

I have my Excel on a Mac using Parallels so it may not be the fastest.
4500 rows of data similar to below calcs in a slow blink with 2007 but takes 17 seconds with 2013 !!!!!!

Excel 2007

	A	B	C	D	E	F	G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Employee[/TD]
[TD="align: center"]Client[/TD]
[TD="align: center"]Days Work[/TD]
[TD="bgcolor: #DBE5F1, align: center"]Employee[/TD]
[TD="bgcolor: #DBE5F1, align: center"]Client[/TD]
[TD="bgcolor: #DBE5F1, align: center"]Rate[/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: #DBE5F1, align: center"]1[/TD]
[TD="bgcolor: #DBE5F1, align: center"]A[/TD]
[TD="bgcolor: #DBE5F1, align: center"]99.54[/TD]
[TD="align: center"]995.4[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: #DBE5F1, align: center"]1[/TD]
[TD="bgcolor: #DBE5F1, align: center"]B[/TD]
[TD="bgcolor: #DBE5F1, align: center"]6[/TD]
[TD="align: center"]23450[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: #DBE5F1, align: center"]1[/TD]
[TD="bgcolor: #DBE5F1, align: center"]C[/TD]
[TD="bgcolor: #DBE5F1, align: center"]7[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: #DBE5F1, align: center"]2[/TD]
[TD="bgcolor: #DBE5F1, align: center"]A[/TD]
[TD="bgcolor: #DBE5F1, align: center"]8[/TD]
[TD="align: center"]100[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: #DBE5F1, align: center"]2[/TD]
[TD="bgcolor: #DBE5F1, align: center"]B[/TD]
[TD="bgcolor: #DBE5F1, align: center"]2345[/TD]
[TD="align: center"]60[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: #DBE5F1, align: center"]2[/TD]
[TD="bgcolor: #DBE5F1, align: center"]C[/TD]
[TD="bgcolor: #DBE5F1, align: center"]10[/TD]
[TD="align: center"]560[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: #DBE5F1, align: center"]3[/TD]
[TD="bgcolor: #DBE5F1, align: center"]A[/TD]
[TD="bgcolor: #DBE5F1, align: center"]11[/TD]
[TD="align: center"]120[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: #DBE5F1, align: center"]3[/TD]
[TD="bgcolor: #DBE5F1, align: center"]B[/TD]
[TD="bgcolor: #DBE5F1, align: center"]12[/TD]
[TD="align: center"]550[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: #DBE5F1, align: center"]3[/TD]
[TD="bgcolor: #DBE5F1, align: center"]C[/TD]
[TD="bgcolor: #DBE5F1, align: center"]13[/TD]
[TD="align: center"]995.4[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: #DBE5F1, align: center"]1[/TD]
[TD="bgcolor: #DBE5F1, align: center"]F[/TD]
[TD="bgcolor: #DBE5F1, align: center"]55[/TD]
[TD="align: center"]60[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: #DBE5F1, align: center"]1[/TD]
[TD="bgcolor: #DBE5F1, align: center"]D[/TD]
[TD="bgcolor: #DBE5F1, align: center"]9[/TD]
[TD="align: center"]995.4[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]10[/TD]
[TD="bgcolor: #DBE5F1, align: center"]1[/TD]
[TD="bgcolor: #DBE5F1, align: center"]E[/TD]
[TD="bgcolor: #DBE5F1, align: center"]12[/TD]
[TD="align: center"]60[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]995.4[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]70[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]60[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]90[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]120[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]G2[/TH]
[TD="align: left"]{=INDEX(F$2:F$500,MATCH(A2&B2,D$2:D$5000&E$2:E$5000,0),1)*C2}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Will be out until late afternoon but will be happy to pick up on this when I return.

 

[gaz_chops]

Hi Tony, DIARYTODAY,

I wasn't sure if he was running the Index/Match via VBA (never really used VBA so don't know if that is possible!).
I'm using a Mac running Excel 2011, tried it with 10,000 rows re-calcs in < 1 sec!!

Gaz

 

[DIARYTODAY]

Yes, I am using recoding the formula using VBA to help me fasten up the report running process. I have Excel 2007, so to speak not the latest and fastest version nowadays so I think that's why the core processor is slow since I have minimum of 8500 rows to calculate and much more if I try to run annual reports or quarterly report to calculate the total pay/bill for each EE per office.

I have MAC but never try it on MAC, im sure it will be faster..

 

[Snakehips]

DT,
So you are wanting to enter the formula using vba?
Your original post / formula suggests to me that the data similar to my post # 15.
A:C is a record of employee hours worked for various clients.
D:F is a table of each employee's rate for a given client.
You want cost of hours in G
You have not confirmed as much but that is the assumption I am making with the code below.

Sub DT()
Dim Rng As Range, Rng2 As Range
Dim lr1 As Long, lr2 As Long


Set Rng = Range("G2")


'stop screen updating and calculation to help speed up the process
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'find actual last rows 
lr1 = Cells(Rows.Count, "D").End(xlUp).Row  'last row of employee/client rate lookup table
lr2 = Cells(Rows.Count, "A").End(xlUp).Row  'last row of data to be costed


'put array formula to cell G2 with appropriate last row
Rng.FormulaArray = "=INDEX(F$2:F$" & lr1 & ",MATCH(A2&B2,D$2:D$" & _
lr1 & "&E$2:E$" & lr1 & ",0),1)*C2"


'copy array formula to full costing range
Set Rng2 = Rng.Resize(lr2 - 1, 1)
Rng.AutoFill Rng2


're-set calculation etc
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


'remove formulas  .... convert formula to resultant value
Rng2.Value = Rng2.Value
End Sub

Hope that helps.