Trying to find missing pairs with macro or a manual process

[sncb]

I have a situation where I have a list of 17 cities and every item code needs to exist in exactly those 17 cities. The issue I have is my items do not exist in all the cities but in only some of them and I need a macro to identify only those cities (with the item code beside them) where the correction would need to be done. I dont know if a macro would be the best solution or another way to extract these but since I have to do these checks very regularly, I think a macro would be the way to go but any suggestions are welcome.

The number of records (rows) change each time based on the data Im trying to correct. Thanks in advance if anyone could advise or help with a macro code.

No headers
Col A are all the cities
Cols C&D is what my existing data looks like
Cols F&G is what I am trying to extract.

 

[Legacy 143009]

So basically, you want the cities those doesn't exist in the first list for each item. Right?

 

[sncb]

So basically, you want the cities those doesn't exist in the first list for each item. Right?

Yes thats right. Thanks.

 

[Legacy 143009]

It is poosible both with formula and macro. Can you send the sample workbook please? I can't rewrite all the values.

 

[Joe4]

Can you send the sample workbook please? I can't rewrite all the values.

Note that if they use the "XL2BB" tool to post their data, you can easily copy and paste it from here to your workbook.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[sncb]

It is poosible both with formula and macro. Can you send the sample workbook please? I can't rewrite all the values.

Thanks Flashbond. Here it is. Let me know if its usable.

Florida		Florida	Item1		Michigan	Item1
Illinois		Illinois	Item1		Nevada	Item1
Indiana		Indiana	Item1		New York	Item1
Kentucky		Kentucky	Item1		North Carolina	Item1
Maryland		Maryland	Item1		Ohio	Item1
Massachusetts		Massachusetts	Item1		Oklahoma	Item1
Michigan		Tennessee	Item2		Oregon	Item1
Nevada		Texas	Item2		Pennsylvania	Item1
New York		Washington	Item2		Tennessee	Item1
North Carolina		Kentucky	Item3		Texas	Item1
Ohio		Maryland	Item3		Washington	Item1
Oklahoma		Massachusetts	Item3		Florida	Item2
Oregon		Michigan	Item3		Illinois	Item2
Pennsylvania		Nevada	Item3		Indiana	Item2
Tennessee		New York	Item3		Kentucky	Item2
Texas		North Carolina	Item3		Maryland	Item2
Washington		Ohio	Item3		Massachusetts	Item2
		Oklahoma	Item3		Michigan	Item2
		Oregon	Item3		Nevada	Item2
		Pennsylvania	Item3		New York	Item2
		Tennessee	Item3		North Carolina	Item2
		Texas	Item3		Ohio	Item2
		Washington	Item3		Oklahoma	Item2
		Illinois	Item 4		Oregon	Item2
		Florida	Item 5		Pennsylvania	Item2
		Illinois	Item 5		Florida	Item3
		Indiana	Item 5		Illinois	Item3
		Kentucky	Item 5		Indiana	Item3
		Maryland	Item 5		and so on....
		Massachusetts	Item 5		and so on....
		Michigan	Item 5		and so on....
					and so on....
					and so on....

 

[sncb]

Note that if they use the "XL2BB" tool to post their data, you can easily copy and paste it from here to your workbook.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Just saw this Joe...Will try this as well.

 

[Mario_R]

Greetings to all. You can try with:

Sub Macro8()
Dim a, b, i&, j&, d, R&
a = [a1].CurrentRegion
Range("D1").Insert Shift:=xlDown: Range("D1") = "F1"
Range("D1", [d1].End(xlDown)).AdvancedFilter 2, , [f1], True
b = [f1].CurrentRegion: [f1].CurrentRegion.Delete xlShiftUp
ReDim d(1 To UBound(a) * (UBound(b) - 1), 1 To 2)
For i = 1 To UBound(a)
  For j = 2 To UBound(b)
    R = 1 + R: d(R, 1) = a(i, 1): d(R, 2) = b(j, 1)
  Next
Next
[c1].CurrentRegion.Delete xlShiftUp: [c1:d1].Resize(UBound(d)) = d
End Sub

 

[Legacy 143009]

I am sorry that I said it is achievable with formula. But that was a dead end for me. However I still believe it is possible and a master can do it. Here is a working macro:

Sub test()
  Dim states As Variant, myRange As Variant, items As Variant, c As Long, exist As Boolean
 
  With ActiveSheet
  states = Intersect(.Range("A:A").CurrentRegion, .Range("A:A"))
  myRange = Intersect(.Range("C:D").CurrentRegion, .Range("C:D"))
  items = Intersect(.Range("D:D").CurrentRegion, .Range("D:D"))
 
  For i = LBound(items) To UBound(items) - 1
    For j = i + 1 To UBound(items)
      If items(i, 1) = items(j, 1) Then items(j, 1) = ""
    Next
  Next
  c = 1
  For Each itm In items
    If itm <> "" Then
      For Each ste In states
        For i = 1 To UBound(myRange)
          If ste = myRange(i, 1) And myRange(i, 2) = itm Then
            exist = True
          End If
        Next
        If Not exist Then
          .Cells(c, 6).Value = ste
          .Cells(c, 7).Value = itm
          c = c + 1
        End If
        exist = False
      Next
    End If
  Next
  End With
End Sub

 

[sncb]

Sub test()
  Dim states As Variant, myRange As Variant, items As Variant, c As Long, exist As Boolean
 
  With ActiveSheet
  states = Intersect(.Range("A:A").CurrentRegion, .Range("A:A"))
  myRange = Intersect(.Range("C:D").CurrentRegion, .Range("C:D"))
  items = Intersect(.Range("D:D").CurrentRegion, .Range("D:D"))
 
  For i = LBound(items) To UBound(items) - 1
    For j = i + 1 To UBound(items)
      If items(i, 1) = items(j, 1) Then items(j, 1) = ""
    Next
  Next
  c = 1
  For Each itm In items
    If itm <> "" Then
      For Each ste In states
        For i = 1 To UBound(myRange)
          If ste = myRange(i, 1) And myRange(i, 2) = itm Then
            exist = True
          End If
        Next
        If Not exist Then
          .Cells(c, 6).Value = ste
          .Cells(c, 7).Value = itm
          c = c + 1
        End If
        exist = False
      Next
    End If
  Next
  End With
End Sub

Yes this one worked perfectly well. The first one wasnt and I was just writing when I got your second code...Thanks a ton Flashbond.