Shift Down if not match

[Legacy 185509]

Hi,
I have data from A2:G2 to A4000:G4000

so I am matching column A with column B

if cells in Column B don't match with cells in Column A than Shift down cells B:G and check if tht matches if tht matchs and check the next row and repeat

I don't want to spend hours doing it manually plz help me

Thanks

 

[texasalynn]

does this work

Sub AlignData()
Dim LR As Long, i As Long
Dim IFind As Variant, IFind2 As Variant
'this compares two columns (A, E) to align matching data to same row
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
i = 2
Do
        IFind = Cells(i, 1)
        IFind2 = Cells(i, 2)
    If IFind <> IFind2 Then
        Range(Cells(i, 2), Cells(i, 6)).Insert Shift:=xlDown
    End If
    i = i + 1
Loop Until IsEmpty(Cells(i, 1))
      
MsgBox "Done"
Application.ScreenUpdating = True
End Sub

 

[ChrisM]

how many times should you try to shift down if no match?

 

[Legacy 185509]

does this work

Sub AlignData()
Dim LR As Long, i As Long
Dim IFind As Variant, IFind2 As Variant
'this compares two columns (A, E) to align matching data to same row
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
i = 2
Do
        IFind = Cells(i, 1)
        IFind2 = Cells(i, 2)
    If IFind <> IFind2 Then
        Range(Cells(i, 2), Cells(i, 6)).Insert Shift:=xlDown
    End If
    i = i + 1
Loop Until IsEmpty(Cells(i, 1))
      
MsgBox "Done"
Application.ScreenUpdating = True
End Sub

Thanks alot, it worked saved me lots of time.

 

[texasalynn]

Your welcome - glad it worked. Thanks for the feedback

 

[Legacy 185509]

Hey, I need help again, your code is working fine, but I came around a condition where Data in column B don't exist in Column A, in tht case if it can shift down column.
do you know what I am trying to say?
Thanks

 

[texasalynn]

so you need it to shift column A down? Give an example

 

[Legacy 185509]

you know how in my original question I asked
shift down B:G if A don't match with column B
for example

in table below it will shift column B:E if A1 <> B1
and second loop it will try to match A2:B2 and it will match so won't ship
but when it try to match number 4 in Column B it has nothing in A to match with so for this need to shift down Column A.
does it make sense?

like for orginal question it was comparing A to B and shifting B:G if don't match but if there is nothing in A to match with it will shift B:G after column A end.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]COLUMN A[/TD]
[TD]COLUMN B[/TD]
[TD]COLUMN C[/TD]
[TD]COLUMN D[/TD]
[TD]COLUMN E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Legacy 185509]

what if I shift down column A first by changing code to

 [LEFT][COLOR=#333333][I]      Range(Cells(i, 2), Cells(i, 1)).Insert Shift:=xlDown

then run it again to shift down B:G
with your original code

[/I][/COLOR]      Range(Cells(i, 2), Cells(i, 6)).Insert Shift:=xlDown

[/LEFT]

 

[texasalynn]

Try this

Sub AlignData()
Dim LR As Long, i As Long
Dim IFind As Variant, IFind2 As Variant
'this compares two columns (A, E) to align matching data to same row
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
i = 2
Do
        IFind = Cells(i, 1)
        IFind2 = Cells(i, 2)
    If IFind < IFind2 Then
        Range(Cells(i, 2), Cells(i, 6)).Insert Shift:=xlDown
    End If
    If IFind2 > IFind Then
        Range(Cells(i, 1)).Insert Shift:=xlDown
    End If
       
    i = i + 1
Loop Until IsEmpty(Cells(i, 1))
     
MsgBox "Done"
Application.ScreenUpdating = True
End Sub