match / algn to columns and add rows where no match

[TrishaL]

Hi
I have the following that I need help on:
column A
ID (it has many of the same ID's as column B)

Column B
ID

(it has many of the same ID's as column A)

Column C to P is the rest of my data that aligns with column B. I want column A and B to align so that column A to P all align.

In excel, I have brought in column A from my database and I want to match it to column B. But since some match and sometimes there are more (not the same) ID's in column A as column B and vice versa I want to add rows where they don't match. Just leave blanks in A or B so I can filter. I could have 5 IDs that match and then 3 column A's not matching and 2 column B's not matching. There are about 3300 rows that i need this to run to. My objective would just to filter out the blanks from column A so that I can paste the matching data back to my access db from column C to P. Hope I explained this correctly. Is there someone that could please help me do this so i don't a have to manually do the adding of rows? Thanks in advance.
​

 

[DanteAmor]

Assuming your data on sheet1 is like this:

Sheet1
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:21.86px;" /><col style="width:21.86px;" /><col style="width:20.91px;" /><col style="width:21.86px;" /><col style="width:19.96px;" /><col style="width:19.96px;" /><col style="width:21.86px;" /><col style="width:21.86px;" /><col style="width:17.11px;" /><col style="width:18.06px;" /><col style="width:20.91px;" /><col style="width:19.01px;" /><col style="width:24.71px;" /><col style="width:22.81px;" /><col style="width:22.81px;" /><col style="width:20.91px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A2</td><td >A1</td><td >C2</td><td >D2</td><td >E2</td><td >F2</td><td >G2</td><td >H2</td><td >I2</td><td >J2</td><td >K2</td><td >L2</td><td >M2</td><td >N2</td><td >O2</td><td >P2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A4</td><td >A4</td><td >C3</td><td >D3</td><td >E3</td><td >F3</td><td >G3</td><td >H3</td><td >I3</td><td >J3</td><td >K3</td><td >L3</td><td >M3</td><td >N3</td><td >O3</td><td >P3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A7</td><td >A8</td><td >C4</td><td >D4</td><td >E4</td><td >F4</td><td >G4</td><td >H4</td><td >I4</td><td >J4</td><td >K4</td><td >L4</td><td >M4</td><td >N4</td><td >O4</td><td >P4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >A8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>

The result will remain like this on sheet2
Sheet2
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:21.86px;" /><col style="width:20.91px;" /><col style="width:21.86px;" /><col style="width:19.96px;" /><col style="width:19.96px;" /><col style="width:21.86px;" /><col style="width:21.86px;" /><col style="width:17.11px;" /><col style="width:18.06px;" /><col style="width:20.91px;" /><col style="width:19.01px;" /><col style="width:24.71px;" /><col style="width:22.81px;" /><col style="width:22.81px;" /><col style="width:20.91px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >A1</td><td >C2</td><td >D2</td><td >E2</td><td >F2</td><td >G2</td><td >H2</td><td >I2</td><td >J2</td><td >K2</td><td >L2</td><td >M2</td><td >N2</td><td >O2</td><td >P2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >A4</td><td >A4</td><td >C3</td><td >D3</td><td >E3</td><td >F3</td><td >G3</td><td >H3</td><td >I3</td><td >J3</td><td >K3</td><td >L3</td><td >M3</td><td >N3</td><td >O3</td><td >P3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >A7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >A8</td><td >A8</td><td >C4</td><td >D4</td><td >E4</td><td >F4</td><td >G4</td><td >H4</td><td >I4</td><td >J4</td><td >K4</td><td >L4</td><td >M4</td><td >N4</td><td >O4</td><td >P4</td></tr></table>

Run this macro:
Change data in red for your information.

Sub match_align()
  Dim lr As Long, i As Long, sh2 As Worksheet, j As Long, f As Range
  Application.ScreenUpdating = False
  Set sh2 = Sheets("[COLOR=#ff0000]sheet2[/COLOR]")
  sh2.Cells.ClearContents
  Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Select
  Range("W:AL").ClearContents
  Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Copy Range("X2")
  Range("W2:W" & Range("X" & Rows.Count).End(xlUp).Row).Value = "A"
  lr = Range("X" & Rows.Count).End(xlUp).Row + 1
  Range("B2:P" & Range("B" & Rows.Count).End(xlUp).Row).Copy Range("X" & lr)
  Range("W" & lr & ":W" & Range("X" & Rows.Count).End(xlUp).Row).Value = "B"
  lr = Range("X" & Rows.Count).End(xlUp).Row
  Range("W2:AL" & lr).Sort key1:=Range("X2"), order1:=xlAscending, Header:=xlNo
  j = 2
  For i = 2 To lr
    If Cells(i, "W") = "A" Then
      sh2.Range("A" & j).Value = Range("X" & i).Value
      j = j + 1
    Else
      Set f = sh2.Range("A:A").Find(Cells(i, "X"), , xlValues, xlWhole)
      If Not f Is Nothing Then
        sh2.Range("B" & f.Row).Resize(1, 15).Value = Range("X" & i).Resize(1, 15).Value
      Else
        j = j + 1
        sh2.Range("B" & j).Resize(1, 15).Value = Range("X" & i).Resize(1, 15).Value
        j = j + 1
      End If
    End If
  Next
  Range("W:AL").ClearContents
End Sub

 

[TrishaL]

Hi Dante
You saved me so many hours. Thank you for being kind and selflessly sharing your knowledge. It took me about 1 second to copy and waste and magic! I tried so much today searching on the internet and trying to figure it out but this was beyond my skillset. This site is amazing and people like you have no idea how much you help peeps like me. Take care and thank you so very much!
Trish

 

[DanteAmor]

Hi Dante
You saved me so many hours. Thank you for being kind and selflessly sharing your knowledge. It took me about 1 second to copy and waste and magic! I tried so much today searching on the internet and trying to figure it out but this was beyond my skillset. This site is amazing and people like you have no idea how much you help peeps like me. Take care and thank you so very much!
Trish

I'm glad to help you. I appreciate your kind comments.