• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching and counter matching problem

I have names in two columns ColA and ColB, and ColC is there orientation to one another

The 1st row says christine knows tim and there orientation is cw(Clockwise) the 2nd row says tim knows christine and there orientation is ccw(counter clock wise)

The 5th row says bill knows frank and there orientation is cw(Clockwise) the 8th row says frank knows bill and there orientation is ccw(counter clock wise)

I need a macro to start on row 1 write cw in ColC then look for the reverse paring (tim christine) then wite ccw in ColC ajacent to the pairing, then repeat this process for all name parings

I have 100,00 rows so it needs speed

I am going nowhere trying to code this I am at a loss!

Thank you

Code:
ColA            ColB             ColC
christine       tim              cw
tim             christine        ccw
christine       chanda           cw
joe             betty            cw
bill            frank            cw
bill            viktor           cw
hank            mona  
frank           bill             ccw
mona            hillery          cw
betty           joe              ccw
victor          bill             ccw
chanda          christine        ccw
hillery         mona             ccw
 

Attachments

  • Match.xlsm
    8.4 KB · Views: 5
Hi Tim ,

Can you see whether the logic in this file is correct ?

If yes , then how does it fare as far as speed is concerned when there are 100000 rows of data ?

Narayan
 

Attachments

  • Match.xlsm
    9.4 KB · Views: 8
Last edited:
Hello Narayan,

Thank you for your help, the logic is correct!

I am afraid I placed my question in the wrong form I am in need of a vba solution is this method a good fit for turning it in to a macro?
 
Hi Tim ,

Why do you need a macro if there is no time impact of the formulae ?

If the formulae are making things sluggish , then certainly we should look for an improved alternative.

Of course , the logic can easily be implemented using VBA. I am sure someone or the other will post the code.

Narayan
 
Thank you again Narayan, here is what I did with your formulas to automate your help.

Code:
Sub Winding()
Dim ws As Worksheet
Dim Rng As Range, Rng2 As Range
Dim LR As Long

Set ws = ThisWorkbook.Sheets("Unpivot_RegistrationData")
      With ws
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set Rng = .Range("A1:A" & LR)
        Set Rng2 = .Range("G1:G" & LR)
      End With

      With Rng.Offset(, 3)
            .Formula = "=A1&B1"
          ' .Value = .Value
      End With
   
      With Rng.Offset(, 4)
            .Formula = "=B1&A1"
            '.Value = .Value
      End With
   
      With Rng.Offset(, 5)
            .Formula = "=COUNTIF($D$1:D1,E1)=0"
            '.Value = .Value
      End With
   
      Rng2.Cells(1, 1).FormulaArray = _
            "=ISNUMBER(MATCH(B1&A1,$A$1:$A$" & LR & " & $B$1:$B$" & LR & ",0))"
            Rng2.Cells(1, 1).AutoFill Destination:=Rng2, Type:=xlFillDefault
   
      With Rng.Offset(, 7)
            .Formula = "=IF(G1,IF(F1,""CW"",""CCW""),"""")"
            .Value = .Value
      End With

With ws
  .Columns("D:G").Delete
  .Cells(1, 4).Value = "Winding"
End With

End Sub
 
Hi Tim ,

But does it work ?

And how does its performance on your actual data file compare with the formula version ?

Narayan
 
Hello Narayan

Thank for bring me back to my original question as I was only testing my Sud on a 1000 rows and had not tryed it on my Live dataset of 120,000 or so rows

It took about 45 miniutes for the Sub to run up from 35 minitues if I enter the formulas manualy (Why would there be a difference?)

I think these are the time cosumers
Code:
 With Rng.Offset(, 5)
            .Formula = "=COUNTIF($D$1:D1,E1)=0"
            '.Value = .Value
    End With
And
Code:
      Rng2.Cells(1, 1).FormulaArray = _
            "=ISNUMBER(MATCH(B1&A1,$A$1:$A$" & LR & " & $B$1:$B$" & LR & ",0))"
            Rng2.Cells(1, 1).AutoFill Destination:=Rng2, Type:=xlFillDefault
 
Hi Tim ,

Can you upload a file which has 1000 rows of data ?

Whatever time it takes for a formula solution , we can benchmark all other approaches against the formula approach.

With just a few rows of data , the time taken will be too short to do any kind of meaningful evaluation.

Narayan
 
Hello Narayan,

Here is the WB it has 2000 rows it takes 5s at 10,00 rows it takes 1.27 min and so forth

Thank you
 

Attachments

  • Winding.xlsm
    167.8 KB · Views: 3
Hi Tim ,

I am not sure about the timings you have mentioned ; with the formulae in place over 2000 rows , if I change any particular value anywhere in the data range , the recalculation takes less than 2 seconds.

If this were extended to 120000 rows , it will take more than 2 minutes , but I cannot imagine how it can take 35 minutes.

Secondly , in your first post , you had shown text values ; in your next upload you are showing numeric values ; which is it in reality ?

If it is numeric , you can try the formula :

=A2 * 10000 + B2 , instead of =A2 & B2

and

=B2 * 10000 + A2 , instead of =B2 & A2

and see whether the numeric computation is faster than the concatenation.

Narayan
 
Back
Top