Move cells down
 

If the left 11 characters in column E do not match the left 11
characters in column A, how can I move the cells in column E down
until they do match?

a b c
d e
001531594-8 2001 33 001531594-8 CF 5501 PM
001531594-8 2002 33 001531596-0 CF 4345 NN
001531594-8 2003 33 001531598-2 CF 5500 PM
001531594-8 2004 33 001531602-2 CF 4455 PK
001531596-0 2011 33 001531609-9 CF 6898 SB
001531598-2 2004 33 001531611-0 CF 6888 PG
001531602-2 2009 33 001531622-0 CF 6879 PG
001531602-2 2010 33 001531631-8 CF 4909 NR
001531602-2 2011 33 001531645-1 CF 5451 PL
001531609-9 2006 33 001531650-5 CF 7174 PJ
001531611-0 2005 33 001531653-8 CF 5438 PL


Here are the results I'm looking for:

a b c
d e
001531594-8 2001 33 001531594-8 CF 5501 PM
001531594-8 2002 33
001531594-8 2003 33
001531594-8 2004 33
001531596-0 2011 33 001531596-0 CF 4345 NN
001531598-2 2004 33 001531598-2 CF 5500 PM
001531602-2 2009 33 001531602-2 CF 4455 PK
001531602-2 2010 33
001531602-2 2011 33
001531609-9 2006 33 001531609-9 CF 6898 SB
001531611-0 2005 33 001531611-0 CF 6888 PG

Move cells down
 

On Nov 22, 3:29*pm, gary wrote:
If the left 11 characters in column E do not match the left 11
characters in column A, how can I move the cells in column E down
until they do match?

* * * * * *a * * * * * * * * * * * * *b * * * * * c
d * * * * * * * * * * * * * * e
001531594-8 2001 * * * * * * * *33 * * * * * * *001531594-8 CF 5501 PM
001531594-8 2002 * * * * * * * *33 * * * * * * *001531596-0 CF 4345 NN
001531594-8 2003 * * * * * * * *33 * * * * * * *001531598-2 CF 5500 PM
001531594-8 2004 * * * * * * * *33 * * * * * * *001531602-2 CF 4455 PK
001531596-0 2011 * * * * * * * *33 * * * * * * *001531609-9 CF 6898 SB
001531598-2 2004 * * * * * * * *33 * * * * * * *001531611-0 CF 6888 PG
001531602-2 2009 * * * * * * * *33 * * * * * * *001531622-0 CF 6879 PG
001531602-2 2010 * * * * * * * *33 * * * * * * *001531631-8 CF 4909 NR
001531602-2 2011 * * * * * * * *33 * * * * * * *001531645-1 CF 5451 PL
001531609-9 2006 * * * * * * * *33 * * * * * * *001531650-5 CF 7174 PJ
001531611-0 2005 * * * * * * * *33 * * * * * * *001531653-8 CF 5438 PL

Here are the results I'm looking for:

* * * * * *a * * * * * * * * * * * * *b * * * * * c
d * * * * * * * * * * * * * * e
001531594-8 2001 * * * * * * * *33 * * * * * * *001531594-8 CF 5501 PM
001531594-8 2002 * * * * * * * *33
001531594-8 2003 * * * * * * * *33
001531594-8 2004 * * * * * * * *33
001531596-0 2011 * * * * * * * *33 * * * * * * *001531596-0 CF 4345 NN
001531598-2 2004 * * * * * * * *33 * * * * * * *001531598-2 CF 5500 PM
001531602-2 2009 * * * * * * * *33 * * * * * * *001531602-2 CF 4455 PK
001531602-2 2010 * * * * * * * *33
001531602-2 2011 * * * * * * * *33
001531609-9 2006 * * * * * * * *33 * * * * * * *001531609-9 CF 6898 SB
001531611-0 2005 * * * * * * * *33 * * * * * * *001531611-0 CF 6888 PG


Sub moveemdowntomatchSAS()Dim i As LongFor i = 2 To Cells(Rows.Count,
"e").End(xlUp).RowIf Left(Cells(i, "e"), 11) < Left(Cells(i, "a"),
11) ThenCells(i, "e").InsertEnd IfNext iEnd Sub

Move cells down
 

On Nov 22, 6:24*pm, Don Guillett wrote:
On Nov 22, 3:29*pm, gary wrote:









If the left 11 characters in column E do not match the left 11
characters in column A, how can I move the cells in column E down
until they do match?

* * * * * *a * * * * * * * * * * * * *b * * * * * c
d * * * * * * * * * * * * * * e
001531594-8 2001 * * * * * * * *33 * * * * * * *001531594-8 CF 5501 PM
001531594-8 2002 * * * * * * * *33 * * * * * * *001531596-0 CF 4345 NN
001531594-8 2003 * * * * * * * *33 * * * * * * *001531598-2 CF 5500 PM
001531594-8 2004 * * * * * * * *33 * * * * * * *001531602-2 CF 4455 PK
001531596-0 2011 * * * * * * * *33 * * * * * * *001531609-9 CF 6898 SB
001531598-2 2004 * * * * * * * *33 * * * * * * *001531611-0 CF 6888 PG
001531602-2 2009 * * * * * * * *33 * * * * * * *001531622-0 CF 6879 PG
001531602-2 2010 * * * * * * * *33 * * * * * * *001531631-8 CF 4909 NR
001531602-2 2011 * * * * * * * *33 * * * * * * *001531645-1 CF 5451 PL
001531609-9 2006 * * * * * * * *33 * * * * * * *001531650-5 CF 7174 PJ
001531611-0 2005 * * * * * * * *33 * * * * * * *001531653-8 CF 5438 PL

Here are the results I'm looking for:

* * * * * *a * * * * * * * * * * * * *b * * * * * c
d * * * * * * * * * * * * * * e
001531594-8 2001 * * * * * * * *33 * * * * * * *001531594-8 CF 5501 PM
001531594-8 2002 * * * * * * * *33
001531594-8 2003 * * * * * * * *33
001531594-8 2004 * * * * * * * *33
001531596-0 2011 * * * * * * * *33 * * * * * * *001531596-0 CF 4345 NN
001531598-2 2004 * * * * * * * *33 * * * * * * *001531598-2 CF 5500 PM
001531602-2 2009 * * * * * * * *33 * * * * * * *001531602-2 CF 4455 PK
001531602-2 2010 * * * * * * * *33
001531602-2 2011 * * * * * * * *33
001531609-9 2006 * * * * * * * *33 * * * * * * *001531609-9 CF 6898 SB
001531611-0 2005 * * * * * * * *33 * * * * * * *001531611-0 CF 6888 PG

Sub moveemdowntomatchSAS()
Dim i As LongFor i = 2 To Cells(Rows.Count, "e").End(xlUp).Row
If Left(Cells(i, "e"), 11) < Left(Cells(i, "a"), 11) Then
Cells(i, "e").Insert
End If
Next i
End Sub