Alternative formula to a multiple Index used in a 50,000 row table

[edmundmckay]

I am contacting the forum today in search of some advice/help or solution to a problem which I'm pretty sure is being caused by the size of the data I am using and what formulas I am trying to utilise within the sheet of data.

In Column H of my report (this report is 50,000 rows long) is column H. This is a list of codes which references the role of the Name in column J. The raw report only has columns A-J in it. In the table below is an extract but also the additional columns in which I want to create, columns K-O.

Columns K- N headers hold a role type which exists in column H. I am using the formula below to look up in column H the name from column J and populate in column K depending on the property reference in column D and fill down the formula to the bottom of the 50,000 report.

The formula works and does what I need it too but due to the amount of rows I am presuming, excel crashes or gets stuck. I created a Macro to perform this (see below) to try and speed up the process.

Please could someone tell me either another route to try to achieve the above without excel crashing.

I have Defined Named Ranges for the arrays of the formula arrays needed to perform the formula

ManagerTypeNames = Column J
ManagerType = Column H
PropertyREfColumn = Column D

{=INDEX(ManagerTypeNames,MATCH(1,($K$1=ManagerType)*(O2=PropertyRefColumn),0))}

The below is the macro I have built to try and speed up the process and prevent crashing.

Sub TrampsManagerTableColumnAdd()
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False '
    Application.Calculation = xlManual
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "J").End(xlUp).Row
    
'Create defined names for ranges for the index formulas'
    
    Sheets("Tramps Manager Table Raw Data").Select
    
    ActiveWorkbook.Names.Add Name:="ManagerType", RefersToR1C1:= _
        "=OFFSET('Tramps Manager Table Raw Data'!R2C8,0,0,COUNTA('Tramps Manager Table Raw Data'!C8),1)"
    ActiveWorkbook.Names("ManagerType").Comment = ""
    ActiveWorkbook.Names.Add Name:="ManagerTypeNames", RefersToR1C1:= _
        "=OFFSET('Tramps Manager Table Raw Data'!R2C10,0,0,COUNTA('Tramps Manager Table Raw Data'!C10),1)"
    ActiveWorkbook.Names("ManagerTypeNames").Comment = ""
    ActiveWorkbook.Names.Add Name:="PropertyRefColumn", RefersToR1C1:= _
        "=OFFSET('Tramps Manager Table Raw Data'!R2C4,0,0,COUNTA('Tramps Manager Table Raw Data'!C4),1)"
    ActiveWorkbook.Names("PropertyRefColumn").Comment = ""
    
'Create Columns for names per category to sit in and then a Property Ref Column purely for the index formula to utilise'
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "WPIC"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "MAIN"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "PMA"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "APA"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "INDEX PROPERTY REF"
    Range("K2").Select
'  Format Paste Headers to the new Column Headers'
    Range("J1").Select
    Selection.Copy
    Range("K1:O1").Select
    Selection.pastespecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
 ' pull through Property Ref for each row (this is for the index formulas below to work)'
 
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "=RC[-11]"
    Range("O2:O" & Lastrow).Select
    Selection.FillDown
    
    ActiveSheet.Calculate
    
    Range("O2:O" & Lastrow).Select
    Selection.Copy
    Range("O2").Select
    Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    
 ' Pull through the name sitting next to WPIC in the ManagerType Column'
    
    Range("K2").Select
    Selection.FormulaArray = _
        "=INDEX(ManagerTypeNames,MATCH(1,(R1C11=ManagerType)*(RC[4]=PropertyRefColumn),0))"
    Range("K2:K" & Lastrow).Select
    Selection.FillDown
    
    ActiveSheet.Calculate
    
    Range("K2:K" & Lastrow).Select
    Selection.Copy
    Range("K2").Select
    Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    
  ' Pull through the name sitting next to MAIN in the ManagerType Column'
    
        
    Range("L2").Select
    Selection.FormulaArray = _
        "=INDEX(ManagerTypeNames,MATCH(1,(R1C12=ManagerType)*(RC[3]=PropertyRefColumn),0))"
    Range("L2:L" & Lastrow).Select
    Selection.FillDown
    
    ActiveSheet.Calculate
    
    Range("L2:L" & Lastrow).Select
    Selection.Copy
    Range("L2").Select
    Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
        
' Pull through the name sitting next to PMA in the ManagerType Column'
        
    Range("M2").Select
    Selection.FormulaArray = _
        "=INDEX(ManagerTypeNames,MATCH(1,(R1C13=ManagerType)*(RC[2]=PropertyRefColumn),0))"
    Range("M2:M" & Lastrow).Select
    Selection.FillDown
    
    ActiveSheet.Calculate
    
    Range("M2:M" & Lastrow).Select
    Selection.Copy
    Range("M2").Select
    Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
        
    
' Pull through the name sitting next to APA in the ManagerType Column'
    Range("N2").Select
    Selection.FormulaArray = _
        "=INDEX(ManagerTypeNames,MATCH(1,(R1C14=ManagerType)*(RC[1]=PropertyRefColumn),0))"
    Range("N2:N" & Lastrow).Select
    Selection.FillDown
    
    ActiveSheet.Calculate
    
    Range("N2:N" & Lastrow).Select
    Selection.Copy
    Range("N2").Select
    Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
        
    
    
    Range("A1").Select
    
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.Calculation = xlAutomatic
    
    MsgBox ("Tramp Manager Table Column Update Macro Complete")
    
End Sub

 

[mumps]

I think that your best approach would be to eliminate the formulas altogether and let the macro do the work. Please explain in words exactly what your formula is trying to so referring to specific cells, rows and columns and using a few examples from your data. Include what your expected results should be in columns K:N for those examples. Also, it's difficult to work with pictures so perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Fluff]

Unless I've totally misunderstood, why not just use
=IF(H2=$M$1,J2,"")
in K2 & copy down