Results 1 to 4 of 4

Thread: match / algn to columns and add rows where no match

  1. #1
    New Member
    Join Date
    Jul 2013
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default match / algn to columns and add rows where no match

    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.

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,303
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: match / algn to columns and add rows where no match

    Assuming your data on sheet1 is like this:

    Sheet1
     ABCDEFGHIJKLMNOP
    1ABCDEFGHIJKLMNOP
    2A2A1C2D2E2F2G2H2I2J2K2L2M2N2O2P2
    3A4A4C3D3E3F3G3H3I3J3K3L3M3N3O3P3
    4A7A8C4D4E4F4G4H4I4J4K4L4M4N4O4P4
    5A8               


    The result will remain like this on sheet2
    Sheet2
     ABCDEFGHIJKLMNOP
    1                
    2                
    3 A1C2D2E2F2G2H2I2J2K2L2M2N2O2P2
    4A2               
    5A4A4C3D3E3F3G3H3I3J3K3L3M3N3O3P3
    6A7               
    7A8A8C4D4E4F4G4H4I4J4K4L4M4N4O4P4


    Run this macro:
    Change data in red for your information.
    Code:
    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("sheet2")
      sh2.Cells.ClearContents
      Sheets("Sheet1").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
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Jul 2013
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: match / algn to columns and add rows where no match

    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

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,303
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: match / algn to columns and add rows where no match

    Quote Originally Posted by TrishaL View Post
    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.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •