Finding common values in 2 ranges using nested loops

[saltire1963]

I’m going around in circles with this one. I have a list of unique names in cells K2:K21. In the same sheet, I also have multiple examples of these unique names in two columns, cells (C2:D92) that I want to summarize next to these unique names in cells L2:R21.
What I am trying to do is start searching at cell C92, then D92, then C91, then D91 etc until I have found the first 7 examples of these unique names. At each instance of one of the unique names appearing in Columns C or D, I will copy the value in cell ‘.Offset(0,2)’ and paste in cells L2:R21
I realise this involves 2 or 3 nested loops but I’m lost which order the loops should be in. Any help would be much appreciated. I can post example of sheet if that is permitted.

 

[Rick Rothstein]

Re: Finding commong values in 2 ranges using nested loops

R is 92, C is 1

R cannot be 92 since I start my loop at 91 and iterate backwards. Did you modify the code I posted in Message #7 ? If you did, you shouldn't have. No matter, delete whatever macro you are now using and recopy the code I posted in Message #7 (I did edit it very shortly after posting it, so maybe you picked up the earlier version by mistake) and try it again. Just so you know, the code I posted, as I posted it, worked fine in the test case I set up here before I posted it to the forum.

 

[saltire1963]

Re: Finding commong values in 2 ranges using nested loops

Hi Rick, still having a bit of a problem getting the code to work. I don't fully understand the -
CreateObject("Scripting.Dictionary") - part and also the line - Cells(R + 1, "L").Resize(, 7) = Split(.Item(Knames(R, 1)) & ",,,,,,,,", ",", 8) - but Is there a way
where I can employ something like - If x.Value = y.Value Then Range(x).Offset(0,2).Copy Destination:= Range("Knames").Offset(i,1).Paste etc?

 

[Rick Rothstein]

Re: Finding commong values in 2 ranges using nested loops

Hi Rick, still having a bit of a problem getting the code to work.

In what way is it not working? After telling us what is not working, can you post a copy of your workbook to DropBox so we can see exactly how your data is laid out and so I can test my code directly on your actual data (rather than my interpretation of your data) and watch it execute, line by line, in order to see how and where it fails to perform correctly?

but Is there a way
where I can employ something like - If x.Value = y.Value Then Range(x).Offset(0,2).Copy Destination:= Range("Knames").Offset(i,1).Paste etc?

Code written that way would be slower (probably not by a lot give how little data you say you have) and less efficient; however, if the code I gave you isn't working for you (I tested it before posting and it worked perfectly here on the test data I used), then I am skeptical any code I wrote in the way you suggest would work either. I really think I need to see your actual data and to be able to watch as my code process it live, line by line.

 

[saltire1963]

Re: Finding commong values in 2 ranges using nested loops

Rick, sorry for delay, not used dropbox before, pls try https://www.dropbox.com/s/jtfucstzh8d02k8/last7games.xlsm?dl=0 to access file. Thanks

 

[saltire1963]

Re: Finding commong values in 2 ranges using nested loops

here is my code to go with file:
Sub FindLast7ValuesInReverseOrder()

Dim FinalRow As Integer
Dim R As Long, C As Long
Dim CDEF As Variant, Knames As Variant, Values As Variant

FinalRow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row

CDEFGH = Range("C" & FinalRow & ":H3")
Knames = Range("L3:L22")
With CreateObject("Scripting.Dictionary")
For R = FinalRow To 3 Step -1
For C = 1 To 2
.Item(CDEFGH(R, C)) = .Item(CDEFGH(R, C)) & Format(CDEFGH(R, C + 2)) & ","
Next
Next
For R = 1 To 20
Cells(R + 1, "L").Resize(, 7) = Split(.Item(Knames(R, 1)) & ",,,,,,,,", ",", 8)
Next
End With

Cells(FinalRow, 1).Select

End Sub

 

[Rick Rothstein]

Re: Finding commong values in 2 ranges using nested loops

I see your data is in different locations than you said they were in in Message #1 ... I didn't look carefully, but I did notice you did not make one adjustment (in the For R=1 To 20 loope) correctly. Anyway, here is working code that will adjust automatically if there should be more or less teams (Column L) in the future and adjusts correctly no matter how many games have been played at the time the macro is run (so you can run it after each weeks' games starting with week #1 if you want).

[table="width: 500"]
[tr]
	[td]Sub FindLastSevenValuesPerCityInReverseOrder()
  Dim R As Long, C As Long, CDEF As Variant, Lnames As Variant, Values As Variant
  CDEF = Range("C3", Cells(Rows.Count, "F").End(xlUp))
  Lnames = Range("L3", Range("L3").End(xlDown))
  With CreateObject("Scripting.Dictionary")
    For R = UBound(CDEF) To 1 Step -1
      For C = 1 To 2
        .Item(CDEF(R, C)) = .Item(CDEF(R, C)) & CDEF(R, C + 2) & ","
      Next
    Next
    For R = 1 To UBound(Lnames)
      Cells(R + 2, "M").Resize(, 7) = Split(.Item(Lnames(R, 1)) & ",,,,,,,,", ",", 8)
    Next
  End With
  Range("M3:S" & 3 + UBound(Lnames)).Value = Range("M3:S" & 3 + UBound(Lnames)).Value
End Sub[/td]
[/tr]
[/table]

 

[saltire1963]

Re: Finding commong values in 2 ranges using nested loops

Cannot thank you enough Rick, it works a treat and much more efficient than the way I was thinking how it could be done. All the best