Automate Excel to shift data so the values in two rows match?

[EmilyExcel]

Hello! Please help me with this quandary for my internship. I would greatly appreciate it Here is my problem:
</SPAN>
I want to match ID numbers in two columns by shifting the ID from Source 2 (and shifting its unique attributes along with it) so that it lines up in the proper row and matches the ID from Source 1. Both sources include ID numbers that the other one doesn’t have. I don’t want to delete those rows, but just leave the Source 2 cells that are lacking the information blank. Ideally the blank cells would be automatically highlighted. Here is what I am trying to do:</SPAN>

Before:</SPAN>

Source 1 unique attributes</SPAN>	ID from Source 1</SPAN>	ID from Source 2</SPAN>	Source 2 unique attributes</SPAN>
AA</SPAN>	1</SPAN>	2</SPAN>	BA</SPAN>
AB</SPAN>	3</SPAN>	3</SPAN>	BB</SPAN>
AC</SPAN>	5</SPAN>	4</SPAN>	BC</SPAN>
AD</SPAN>	6</SPAN>	5</SPAN>	BD</SPAN>
AE</SPAN>	8</SPAN>	6</SPAN>	BE</SPAN>
AF</SPAN>	10</SPAN>	11</SPAN>	BF</SPAN>
AG</SPAN>	12</SPAN>	12</SPAN>	BG</SPAN>

<TBODY>
</TBODY>

After:</SPAN>

Source 1 unique attributes</SPAN>	ID from Source 1</SPAN>	ID from Source 2</SPAN>	Source 2 unique attributes</SPAN>
AA</SPAN>	1</SPAN>
AB</SPAN>	3</SPAN>	3</SPAN>	BB</SPAN>
AC</SPAN>	5</SPAN>	5</SPAN>	BD</SPAN>
AD</SPAN>	6</SPAN>	6</SPAN>	BE</SPAN>
AE</SPAN>	8</SPAN>
AF</SPAN>	10</SPAN>
AG</SPAN>	12</SPAN>	12</SPAN>	BG</SPAN>

<TBODY>
</TBODY>

Is there a command, formula, or macro you can suggest to automate this work? Otherwise I have to manually go through it. I look at “ID from Source 2” and if that doesn’t match the “ID from Source 1,” I delete “ID from Source 2” along with its “Source 2 unique attributes.” I then shift all of the Source 2 cells up the rows and check again if the new ID matches with the ID from Source 1. If not, delete and shift the cells up again. If it is obvious I simply do not have the necessary ID from Source 2, I leave it blank and continue down the row. I need to be careful to not just keep deleting Source 2 IDs that match IDs from Source 1 in rows farther down.</SPAN>

Thank you for your help -Emily</SPAN>

 

[texasalynn]

try this

Sub AlignData()

Dim LR As Long, i As Long
Dim IFind As Variant, IFind2 As Variant
'this compares two columns (B, C) to align matching data to same row
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
i = 2

Do
        IFind = Cells(i, 2)
        IFind2 = Cells(i, 3)
        IFind3 = Cells(i + 1, 2)
    If IFind < IFind2 And Not IFind2 >= IFind3 Then
        Range(Cells(i, 3), Cells(i, 4)).Clear
    End If
    If IFind > IFind2 Then
        Range(Cells(i, 3), Cells(i, 4)).Delete Shift:=xlUp
    End If
    If IFind3 < IFind2 And IFind3 <> Empty Then
        Range(Cells(i, 3), Cells(i, 4)).Insert Shift:=xlDown
    End If
    i = i + 1
Loop Until IsEmpty(Cells(i, 1))
      
MsgBox "Done"
Application.ScreenUpdating = True
End Sub

note: be sure to save your file and or make a copy of the data

 

[EmilyExcel]

Dear Mr. Texas,

Thanks a lot!! This is looking really good I ran your code and it did nearly everything we need it to do. The only prob is that it does not delete entries for "ID from Source 2" and "Source 2 unique attributes" when the corresponding ID from 1 doesn't exist in the workbook, and it doesn't insert blank cells in "ID from Source 2" and "Source 2 unique attributes" and move the data in those columns down to try and match the IDs up again in the next row. You can see below..."11" and "BF" (from source 2, in the 3rd and 4th columns) should simply be deleted and "12" and "BG" (again, from source 2, in the 3rd and 4th columns) should be moved down to the last row to line up with the ID from Source 1 entitled "12".
After running code:


[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Source 1 unique attributes -
[/TD]
[TD]- ID from Source 1 - -
[/TD]
[TD]ID from Source 2-
[/TD]
[TD] Source 2 unique attributes -
[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD] 1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD] 3
[/TD]
[TD]3
[/TD]
[TD] BB
[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD] 5
[/TD]
[TD]5[/TD]
[TD] BD
[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD] 6
[/TD]
[TD]6[/TD]
[TD] BE
[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD] 8
[/TD]
[TD]11
[/TD]
[TD] BF
[/TD]
[/TR]
[TR]
[TD]AF[/TD]
[TD] 10
[/TD]
[TD]12
[/TD]
[TD] BG
[/TD]
[/TR]
[TR]
[TD]AG

Are you able to figure out how
to fix those probs?? I tried
myself but I am not much
of a programmer, hahah

Any advice you can give
would be greatly
appreciated. I feel
like we are so close
[/TD]
[TD] 12
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
to cracking this bugger!!
[TABLE="class: cms_table"]
<tbody>[TR="class: px"cms_table_tr""]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
Thanks again -Em