Faster For Next Loop 3

[PWD]

Good afternoon. I have inherited a task that gets data from our customer, sorted in their preferred order. Basically we have to run down Column "T" and look for occurrences on any of the 6,000 lines in Columns "K" to "S". If it is found in any of these columns (it will only appear once in each row) then add the values from that Row, Column "U" and the put that value in Column "V".

So I have to go through each of the 6,000 rows for the value of "T" & look for it in any of the 6,000 rows.

Basically it is a horrible nested loop of 6,000 x 6,000.

Any suggestions of how I can stop this taking 10+ minutes to run?
Bear in mind I can't re-order the data.

Many thanks,
D€$

 

[strongm]

Why would you not use N1GHTEYES' solution, with a final pass to delete cells in coumn U where you don't need an aggregate. Seems to me that would be somewhat quicker.

 

[PWD]

I'll have a look in the morning. Thanks.

Many thanks,
D€$

 

[PWD]

Yikes N1GHTEYES! I wasn't sure how to turn off the autocalc - so I just put the array formula in V2, Ran to Row 6014, held the Shift & clicked in V6014 then pressed Ctrl + D and waited - probably all of 20 seconds!!!!!

Now all I need to do is figure out the rest of the rules for whether or not things get counted. I've put this in, so far:

{=IF(I2<>1,SUM(IF(T2=data,U)),"")}

I just hope I don't end up having to run through each row anyway to determine whether or not to list anything in Column "U".

Many thanks,
D€$

 

[N1GHTEYES]

PWD - I'm glad it is running a bit quicker for you. Are all your additional conditions related to columns outside "data", T, or U? If so, you can continue to add them the way you suggest - they should not interfere with the basic functioning of the central array formula.

For future reference, to turn off calculation (and sometimes it is a very good idea):
click menu Tools, sub-item Options
on the dialogue box this gives you, select the Calculation tab
click the manual option.
Then, whenever you want the sheet to calculate, hit F9.

However, if you do also end up doing some VBA userfunctions as well, see thread

http://www.tek-tips.com/viewthread.cfm?qid=1628705

"Speeding up worksheets lots of userfunctions".

I don't really understand what you mean by your last sentence.

Tony

 

[PWD]

Hi Tony, there appear to be odd requirements when we should not count the value of U. That what I meant. BTW I have run the first 500 rows of "T" using my 'old' code vs the new formula and there's one value where there's a difference; this must be one of those instances. I'll check it next week & post where it appears to be deciding not to add any particular value(s).

Many thanks,
D€$

 

[N1GHTEYES]

One issue might arise if there are instances where more than one column of data on any given row matches a T value. You stated explicitly early on in the thread that this would not be the case.

However, if it does happen, then the corresponding value of U will be added more than once to the V value. If your coded method actively excludes these cases (e.g. by exiting the For loop when a match is found on a given row) then there will be a difference between the two methods. But that should only happen if the data does comply with your description. If that is an issue, I'm sure the array formula method could be modded to fix it, but I'll have to think about it over the weekend as I'm up to my eyeballs right now (and sinking fast...).

Tony

 

[PWD]

Good morning. I can confirm that the 'Found' value can only occur once per row as each column corresponds to a different level within the organisation, for example:-

L0	L1	L2	L3	L4	L5	L6	L7	L8
GP00	HO	FF00	FF3	FF3C	FF3CM	FF3CS	FF3CSP

The exlusion that I've found is helping me understand the process a bit better. It seems that Column AG will be "1" if a particular line (Report) is part of "Business Finance Summary". If that is true, we have to look for certain 'Business Finace' codes to set a 'Level 2' value & 'Level 3' value. Finally there's another check to see whether or not to add the values of "U" to the running total.

'If business finance
        If Cells(rFound.Row, "AG") = 1 Then ' "M" = Level 2
            lev1 = "FF00"

            If Cells(rFound.Row, "M") = "FF00" _
            Or Cells(rFound.Row, "M") = "ET00" _
            Or Cells(rFound.Row, "M") = "SCBX" _
            Or Cells(rFound.Row, "M") = "FC00" _
            Or Cells(rFound.Row, "M") = "PH00" Then
              lev2 = ""
            Else
              lev2 = Cells(rFound.Row, "M")
            End If 'All of the above!!

            If Cells(rFound.Row, "N") = "FF3" _
            Or Cells(rFound.Row, "N") = "FC3" _
            Or Cells(rFound.Row, "N") = "PH2" Then
              lev3 = ""
            Else
              lev3 = Cells(rFound.Row, "N")
            End If 'All of the aboove!!

        Else 'if not business finance
            lev1 = Cells(rFound.Row, "L")
            lev2 = Cells(rFound.Row, "M")
            lev3 = Cells(rFound.Row, "N")

        End If 'If Cells(rFound.Row, "AG") = 1 Then ' "M" = Level 2
        
        'If not aggregate add up head count for any level into current rows team
        '"I" = "Aggregate Code"
        '"T" = "Survey Print Code" - last column level
        '"O" = "Level 4"
        '"P" = "Level 5"
        '"Q" = "Level 6"
        '"R" = "Level 7"
        '"S" = "Level 8"
    If (Cells(rFound.Row, "I").Value <> 1 And (Cells(rFound.Row, "K") = Range("T" & i)) _
    Or lev1 = Range("T" & i) _
    Or lev2 = Range("T" & i) _
    Or lev3 = Range("T" & i) _
    Or Cells(rFound.Row, "O") = Range("T" & i) _
    Or Cells(rFound.Row, "P") = Range("T" & i) _
    Or Cells(rFound.Row, "Q") = Range("T" & i) _
    Or Cells(rFound.Row, "R") = Range("T" & i) _
    Or Cells(rFound.Row, "S") = Range("T" & i)) Then
        
''        If Cells(rFound.Row, "U") <> "" Then
        If Cells(rFound.Row, "I").Value <> "1" Then 'Don't count summaries
        
        k = k + Cells(rFound.Row, "U").Value
        End If 'If Cells(rFound.Row, "U") <> "" Then

Unfortunately this is only the 1st of 2 complex 'Ifs'.

' If not aggregate: headcount = column v
If Range("I" & i).Value <> 1 Then 'Not an Aggregate
        Range("V" & i).Value = Range("U" & i).Value 'Put Headcount in "V"
        'then go down to next line
      
''        ' If aggregate & not GPS:
    ElseIf Range("I" & i).Value = 1 And Range("Z" & i).Value = 0 Then
    'Exclude F(ood) & PO from counting

The 2nd having other criteris:-

        ' If aggregate & F & PO:
      ElseIf Range("I" & i).Value = 1 And Range("AA" & i).Value = 1 Then

    If Cells(rFound.Row, "I").Value <> 1 And Cells(rFound.Row, "Z") <> "0" _
    And Cells(rFound.Row, "K") & Cells(rFound.Row, "Z") = Range("T" & i) _
    Or (Cells(rFound.Row, "L") & Cells(rFound.Row, "Z") = Range("T" & i)) _
    Or (Cells(rFound.Row, "M") & Cells(rFound.Row, "Z") = Range("T" & i)) _
    Or (Cells(rFound.Row, "N") & Cells(rFound.Row, "Z") = Range("T" & i)) _
    Or (Cells(rFound.Row, "O") & Cells(rFound.Row, "Z") = Range("T" & i)) _
    Or (Cells(rFound.Row, "P") & Cells(rFound.Row, "Z") = Range("T" & i)) _
    Or (Cells(rFound.Row, "Q") & Cells(rFound.Row, "Z") = Range("T" & i)) _
    Or (Cells(rFound.Row, "R") & Cells(rFound.Row, "Z") = Range("T" & i)) _
    Or (Cells(rFound.Row, "S") & Cells(rFound.Row, "Z") = Range("T" & i)) Then
        If Cells(rFound.Row, "I").Value <> "1" Then 'Don't count summaries
        k = k + Cells(rFound.Row, "U").Value
        End If
    End If

So I'm not sure if it would be too convoluted to figure out a suitable formula to cover these requirements

Many thanks,
D€$

 

[N1GHTEYES]

Can you post a sample of data and a concise description of the real processing requirements please. I can't guarantee to provide an array formula solution, but if I get a few spare nanoseconds I'll try to have a quick think about it.

Tony

 

[PWD]

I'll give it a go. Might not be today but ASAP. Thanx.

Many thanks,
D€$