Formula question

[lomerick]

Hi,

Is it possible to combine this two formula into 1?

=VLOOKUP(C2,$J$1:$L$3095,3,0)

=IF(OR(F2="F6P 430",F2="F5P 420"),IF(ISERROR(FIND(("B"),D2,1)+FIND(("G"),D2,1)),"Pending","Done"),IF(OR(F2="A6P 430",F2="ANP 430"),IF(ISERROR(FIND(("E"),D2,1)+FIND(("L"),D2,1)+FIND(("V"),D2,1)),"No basic view",IF(ISERROR(FIND(("B"),D2,1)+FIND(("G"),D2,1)),"Pending","Done"))))


As you can see the first formula looks up the value, the second formula tags a specific status. I'm running this two formula with 600K rows and it takes long time to finish.

If ever what can you suggest me to do to make it faster?

Please Help me. Thanks

 

[MandeepBaluja]

Less than 2 second for 6 lac rows !! Add star if it helps you

Sub T()

'Author: Mandeep baluja
'Date   : 19 feb 2016
'Note   : Excel Can't do Transpose more than 65536 Rows as tried in this But still we have solution


Application.Calculation = xlManual
Const lr = 600000
T1 = Timer: Application.ScreenUpdating = False


Dim varout(1 To lr, 1 To 1) As Variant


Var = Range("D2:F" & lr)
For i = LBound(Var) To UBound(Var)
cell = Var(i, 3): Txt = Var(i, 1)
    Select Case cell
    Case Is = "F6P 430", "F5P 420"
             If Not InStr(Txt, "B") Or InStr(Txt, "G") > 0 Then
                Getvalue = "Done"
             Else
              Getvalue = "Pending"
             End If
    
    Case Is = "ANP 430", "A6P 430"
         If Not (InStr(Txt, "E") Or InStr(Txt, "L") Or InStr(Txt, "V")) > 0 Then
                Getvalue = "No basic view"
        ElseIf Not (InStr(Txt, "B") Or InStr(Txt, "G")) > 0 Then
              Getvalue = "Pending"
             Else
                Getvalue = "Done"
                End If
    End Select
    varout(i, 1) = Getvalue
    
Next


Range("g2").Resize(UBound(varout) - 1, 1).Value = varout
Application.Calculation = xlAutomatic
MsgBox Round(Timer - T1, 2)


End Sub

 

[MandeepBaluja]

Run this code

[COLOR=#333333]Sub T()[/COLOR][COLOR=#333333][INDENT]
'Author: Mandeep baluja
'Date   : 19 feb 2016
'Note   : Excel Can't do Transpose more than 65536 Rows as tried in this But still we have solution


Application.Calculation = xlManual
Const lr = 600000
T1 = Timer: Application.ScreenUpdating = False


Dim varout(1 To lr, 1 To 1) As Variant


Var = Range("D2:F" & lr)
For i = LBound(Var) To UBound(Var)
cell = Var(i, 3): Txt = Var(i, 1)
    Select Case cell
    Case Is = "F6P 430", "F5P 420"
             If Not InStr(Txt, "B") Or InStr(Txt, "G") > 0 Then
                Getvalue = "Done"
             Else
              Getvalue = "Pending"
             End If
    
    Case Is = "ANP 430", "A6P 430"
         If Not (InStr(Txt, "E") Or InStr(Txt, "L") Or InStr(Txt, "V")) > 0 Then
                Getvalue = "No basic view"
        ElseIf Not (InStr(Txt, "B") Or InStr(Txt, "G")) > 0 Then
              Getvalue = "Pending"
             Else
                Getvalue = "Done"
                End If
    End Select
    varout(i, 1) = Getvalue
    
Next


Range("g2").Resize(UBound(varout) - 1, 1).Value = varout
Application.Calculation = xlAutomatic
MsgBox Round(Timer - T1, 2)


End Sub
[/INDENT]

[/COLOR]
[COLOR=#574123]
[LIST]
[/LIST]

[/COLOR]

 

[lomerick]

Run this code

[COLOR=#333333]Sub T()[/COLOR][COLOR=#333333][INDENT]
'Author: Mandeep baluja
'Date   : 19 feb 2016
'Note   : Excel Can't do Transpose more than 65536 Rows as tried in this But still we have solution


Application.Calculation = xlManual
Const lr = 600000
T1 = Timer: Application.ScreenUpdating = False


Dim varout(1 To lr, 1 To 1) As Variant


Var = Range("D2:F" & lr)
For i = LBound(Var) To UBound(Var)
cell = Var(i, 3): Txt = Var(i, 1)
    Select Case cell
    Case Is = "F6P 430", "F5P 420"
             If Not InStr(Txt, "B") Or InStr(Txt, "G") > 0 Then
                Getvalue = "Done"
             Else
              Getvalue = "Pending"
             End If
    
    Case Is = "ANP 430", "A6P 430"
         If Not (InStr(Txt, "E") Or InStr(Txt, "L") Or InStr(Txt, "V")) > 0 Then
                Getvalue = "No basic view"
        ElseIf Not (InStr(Txt, "B") Or InStr(Txt, "G")) > 0 Then
              Getvalue = "Pending"
             Else
                Getvalue = "Done"
                End If
    End Select
    varout(i, 1) = Getvalue
    
Next


Range("g2").Resize(UBound(varout) - 1, 1).Value = varout
Application.Calculation = xlAutomatic
MsgBox Round(Timer - T1, 2)


End Sub
[/INDENT]

[/COLOR]
[COLOR=#574123]
[/COLOR]

Late reply (weekends)

But I don't see the lookup part here? Thanks for the other formula. Will try this...