Finding common values in 2 ranges using nested loops

S

saltire1963

Board Regular
Joined
Aug 11, 2014
Messages
69
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Re: Finding commong values in 2 ranges using nested loops

here is an example of what I'm trying to do, copy the numbers next to the first 7 matches of each city:

[TABLE="width: 403"]
<colgroup><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Adelaide[/TD]
[TD]Sydney[/TD]
[TD]0[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD][/TD]
[TD]Canberra[/TD]
[TD]Hobart[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Melbourne[/TD]
[TD]Canberra[/TD]
[TD]1[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Perth[/TD]
[TD]Hobart[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Hobart[/TD]
[TD]Darwin[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Sydney[/TD]
[TD]Adelaide[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Canberra[/TD]
[TD]Melbourne[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Melbourne[/TD]
[TD]Hobart[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Darwin[/TD]
[TD]Perth[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Adelaide[/TD]
[TD]Canberra[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Melbourne[/TD]
[TD]Sydney[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Perth[/TD]
[TD]Darwin[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Hobart[/TD]
[TD]Darwin[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Adelaide[/TD]
[TD]Sydney[/TD]
[TD]0[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Canberra[/TD]
[TD]Perth[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Darwin[/TD]
[TD]Melbourne[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Sydney[/TD]
[TD]Hobart[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Hobart[/TD]
[TD]Melbourne[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Darwin[/TD]
[TD]Canberra[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Perth[/TD]
[TD]Sydney[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Sydney[/TD]
[TD]Perth[/TD]
[TD]0[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]

and paste into the following grid:

[TABLE="width: 308"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Sydney[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Melbourne[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Canberra[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Adelaide[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Perth[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Darwin[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Hobart[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]



[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Finding commong values in 2 ranges using nested loops

Should your grid start with 0? If not, where is 0 located, after 9?

I don't understand how the numbers should be listed... perhaps a count of each?
 
Last edited:
Upvote 0
Re: Finding commong values in 2 ranges using nested loops

Yes, the snippet I cut and pasted does not show the row no's or column letters so can understand why its confusing. What I'm trying to do is, for example, go to first unique name in summary table bottom left, which in this case is Sydney, then go to upper table and start searching from bottom up, and when a cell with the value Sydney is found, then copy the value 2 cells to right, in this case 0, to the cell under no.1 next to Sydney in summary table. Next go to the second match of Sydney and copy the value 2 cells to right, in this case 1, to the cell under no.2 next to Sydney. And keep going like this until first 7 values for Sydney, then each of the other cities have populated the summary table. I know the basics of looping, but since this involves 3 loops, I'm confused which one should be the outer one and which of the other 2 should be next etc.
 
Upvote 0
Re: Finding commong values in 2 ranges using nested loops

Hi,

If you are fine with formulae to achieve this, here we go -

Assumption:
Data Set in A1:F21
A, B have City names
C, D have scores
Result set in I1:P8

Approach:
1. Column E to count the match number for team in Column A
E1=COUNTIF($A$1:$B1,A1)
2. Column F to count the match number for team in Column B
F1=COUNTIF($A$1:$B1,B1)
3. Copy E1, F1 to cells E2:F21
4. J2 = SUMIFS($D$1:$D$21,$B$1:$B$21,"="&$I2,$F$1:$F$21,"="&J$1)+SUMIFS($C$1:$C$21,$A$1:$A$21,"="&$I2,$E$1:$E$21,"="&J$1)
This gives the score of Sydney in its first match.
Copy this formula to the rest of the table J2:P8

Here is the output -
1 2 3 4 5 6 7
Sydney 1 7 2 4 1 1 0
Melbourne 1 0 1 2 3 0 0
Canberra 1 1 2 1 1 0 0
Adelaide 0 2 1 0 0 0 0
Perth 2 1 5 2 0 6 0
Darwin 0 2 0 1 2 0 0
Hobart 1 1 0 0 0 0 2

Hope this helps.

Regards.
 
Upvote 0
Re: Finding commong values in 2 ranges using nested loops

Thanks for reply, but I think we're at crossed purposes. I'm want to find last 7 most recent numbers for each city, not all of them, and populate the last 7 numbers for each city in the summary grid. Thats why I say, in vba, there needs to be 2 nested counter loops, (i) for list of cities 1 to 7 and (ii) for last 7 numbers for each city, 1 to 7
 
Upvote 0
Re: Finding commong values in 2 ranges using nested loops

saltire1963 said:
Thanks for reply, but I think we're at crossed purposes. I'm want to find last 7 most recent numbers for each city, not all of them, and populate the last 7 numbers for each city in the summary grid.
Click to expand...
Give this macro a try...
Code: 
[table="width: 500"]
[tr]
	[td]Sub FindLastSevenValuesPerCityInReverseOrder()
  Dim R As Long, C As Long, CDEF As Variant, Knames As Variant, Values As Variant
  CDEF = Range("C2:F92")
  Knames = Range("K2:K21")
  With CreateObject("Scripting.Dictionary")
    For R = 91 To 1 Step -1
      For C = 1 To 2
        .Item(CDEF(R, C)) = .Item(CDEF(R, C)) & Format(CDEF(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
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Re: Finding commong values in 2 ranges using nested loops

Thanks Rick, this looks very close to working. I get an error message 'No.9 Subscript out of range' when I run line.Item(CDEF(R, C)) = .Item(CDEF(R, C)) & Format(CDEF(R, C + 2)) & ","
 
Upvote 0
Re: Finding commong values in 2 ranges using nested loops

What is the value of R and C when this error occurs?
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Show duplicates from across multiple ranges in one cell with a single nested formula?
Replies
9
Views
188
VikingLink
V
P
  • Solved
Using For loop counter in Sheet name
Replies
3
Views
283
Joe4
Joe4
I
  • Question Question
Find last row of sheet based off cell value to reference the sheet name
Replies
0
Views
474
ItalianPlatinum
I
A
  • Solved
Finding a unique value in a range and returning the value in another column of the same row, without offset / slow formulae.
Replies
8
Views
2K
AaronPMH
A
V
  • Question Question
Looking to create VBA command to go through List of sheet names, look up sheet name in range, look up value in cell next to it, and run a macro
Replies
3
Views
385
Volrin
V

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top