Two sheets, Street, City copy ID

[Beginner778]

Hi,

I have two sheets:

Sheet 1 (1K rows)

Street (A column)
City (B column)

Sheet 2 (2K rows)
Street (A column)
City (B column)
ID (C column)

Problem: If the street and city match in the sheets (1,2), copy the ID to the C column in Sheet 1

I tried modifying the code with bold text

Thank you very much for help

 

[Beginner778]

I try to change this code:

Sub Compare2Sheets()
' hiker95, 07/13/2011
' http://www.mrexcel.com/forum/showthread.php?t=564090
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, LR As Long, FR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
With w1.Range("C1:C" & LR)
  .FormulaR1C1 = "=RC[-2]&RC[-1]"
  .Value = .Value
End With
LR = w2.Cells(Rows.Count, 1).End(xlUp).Row
With w2.Range("C1:C" & LR)
  .FormulaR1C1 = "=RC[-2]&RC[-1]"
  .Value = .Value
End With
For Each c In w2.Range("C1", w2.Range("C" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w1.Columns(3), 0)
  On Error GoTo 0
  If FR = 0 Then
    c.Offset(, -2).Resize(, 2).Font.Bold = True
  End If
Next c
w1.Columns(3).ClearContents
w2.Columns(3).ClearContents
Application.ScreenUpdating = True
End Sub

 

[AlanY]

deleted

 

[Toadstool]

Please ignore. I see now you want a VBA solution.

Hi Beginner778,

Is this what you wanted?

Beginner778.xlsx
	A	B	C
1	Street	City	Id
2	Main	Miami	ABC
3	1st	Miami	MNB
4	Elm	Brice	XYZ
5	Oak	Belmont	PGT
6
Sheet2

Beginner778.xlsx
	A	B	C
1	Street	City	Id
2	Elm	Miami
3	Elm	Brice	XYZ
4	Main	Miami	ABC
5	1st	New York
6	Oak	Portland
7	Oak	Belmont	PGT
8
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(A2="","",IFERROR(INDEX(Sheet2!$C$2:$C$1200,AGGREGATE(15,6,ROW(Sheet2!$A$2:$A$1200)-ROW(Sheet2!$A$1)/((Sheet2!$A$2:$A$1200=A2)*(Sheet2!$B$2:$B$1200=B2)),1)),""))

 

[Takae]

Hope this helps.

Sub Sample()
    Dim Dic, i As Long, buf As String
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set ws1 = Sheets("sheet1")
    Set ws2 = Sheets("Sheet2")
    Set Dic = CreateObject("Scripting.Dictionary")
    
    On Error Resume Next
    With ws2
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            buf = .Cells(i, 1).Value & .Cells(i, 2).Value
            Dic.Add buf, .Cells(i, 3).Value
        Next
    End With
    
    With ws1
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            buf = .Cells(i, 1).Value & .Cells(i, 2).Value
            .Cells(i, 3).Value = Dic(buf)
        Next
        Set Dic = Nothing
    End With
    On Error GoTo 0
End Sub

 

[Beginner778]

Please ignore. I see now you want a VBA solution.

Hi Beginner778,

Is this what you wanted?

Beginner778.xlsx
	A	B	C
1	Street	City	Id
2	Main	Miami	ABC
3	1st	Miami	MNB
4	Elm	Brice	XYZ
5	Oak	Belmont	PGT
6
Sheet2

Beginner778.xlsx
	A	B	C
1	Street	City	Id
2	Elm	Miami
3	Elm	Brice	XYZ
4	Main	Miami	ABC
5	1st	New York
6	Oak	Portland
7	Oak	Belmont	PGT
8
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(A2="","",IFERROR(INDEX(Sheet2!$C$2:$C$1200,AGGREGATE(15,6,ROW(Sheet2!$A$2:$A$1200)-ROW(Sheet2!$A$1)/((Sheet2!$A$2:$A$1200=A2)*(Sheet2!$B$2:$B$1200=B2)),1)),""))

Could you add a file with this solution?

 

[Toadstool]

Could you add a file with this solution?

You can't upload files to this forum. You just need to copy that formula down column C to row 2000

=IF(A2="","",IFERROR(INDEX(Sheet2!$C$2:$C$1200,AGGREGATE(15,6,ROW(Sheet2!$A$2:$A$1200)-ROW(Sheet2!$A$1)/((Sheet2!$A$2:$A$1200=A2)*(Sheet2!$B$2:$B$1200=B2)),1)),""))

 

[Beginner778]

You can't upload files to this forum. You just need to copy that formula down column C to row 2000

=IF(A2="","",IFERROR(INDEX(Sheet2!$C$2:$C$1200,AGGREGATE(15,6,ROW(Sheet2!$A$2:$A$1200)-ROW(Sheet2!$A$1)/((Sheet2!$A$2:$A$1200=A2)*(Sheet2!$B$2:$B$1200=B2)),1)),""))

Thank you Very Much Works Great

Prerequisite: If the street and city match in the sheets (1,2), copy the ID to the C column in Sheet 1 - this works great

How to add condition if column D has zip code but city and street are different still copy ID

Sheet1:

Sheet2:

Thank You for Big Help

If it is difficult it may be a separate function

 

[Toadstool]

I don't see how this would work as column C wouldn't have an Id if A and B didn't match.

If you want to copy Zip using Id but regardless of other values then this does it.

Beginner778-2.xlsx
	A	B	C	D
1	Street	City	Id	Zip
2	Main	Miami	ABC	1
3	1st	Miami	MNB	2
4	Elm	Brice	XYZ	3
5	Oak	Belmont	PGT	4
6	AA	BB	AWS	123
Sheet2

Beginner778-2.xlsx
	A	B	C	D
1	Street	City	Id	Zip
2	Elm	Miami
3	Elm	Brice	XYZ	3
4	Main	Miami	ABC	1
5	1st	New York
6	Oak	Portland
7	Oak	Belmont	PGT	4
8	CC	DD
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(A2="","",IFERROR(INDEX(Sheet2!$C$2:$C$1200,AGGREGATE(15,6,ROW(Sheet2!$A$2:$A$1200)-ROW(Sheet2!$A$1)/((Sheet2!$A$2:$A$1200=A2)*(Sheet2!$B$2:$B$1200=B2)),1)),""))
D2:D8	D2	=IF(ISNA(MATCH(C2,Sheet2!$C$2:$C$1200,0)),"",INDEX(Sheet2!$D$2:$D$11200,MATCH(C2,Sheet2!$C$2:$C$1200,0)))

 

[Beginner778]

I don't see how this would work as column C wouldn't have an Id if A and B didn't match.

If you want to copy Zip using Id but regardless of other values then this does it.

Beginner778-2.xlsx
	A	B	C	D
1	Street	City	Id	Zip
2	Main	Miami	ABC	1
3	1st	Miami	MNB	2
4	Elm	Brice	XYZ	3
5	Oak	Belmont	PGT	4
6	AA	BB	AWS	123
Sheet2

Beginner778-2.xlsx
	A	B	C	D
1	Street	City	Id	Zip
2	Elm	Miami
3	Elm	Brice	XYZ	3
4	Main	Miami	ABC	1
5	1st	New York
6	Oak	Portland
7	Oak	Belmont	PGT	4
8	CC	DD
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(A2="","",IFERROR(INDEX(Sheet2!$C$2:$C$1200,AGGREGATE(15,6,ROW(Sheet2!$A$2:$A$1200)-ROW(Sheet2!$A$1)/((Sheet2!$A$2:$A$1200=A2)*(Sheet2!$B$2:$B$1200=B2)),1)),""))
D2:D8	D2	=IF(ISNA(MATCH(C2,Sheet2!$C$2:$C$1200,0)),"",INDEX(Sheet2!$D$2:$D$11200,MATCH(C2,Sheet2!$C$2:$C$1200,0)))

I mean, if the city and street are different, check if the zip codes are the same, if so, then copy the ID - (this will narrow down the search and data ordering)