I have three columns of data - the first is a master list of
countries, the second an incomplete list that has a third column
referring to this. I want to somehow create a script to match up the
second and third columns with the first. For example:

Row 1: America - America - 3
Row 2: Belgium - Belgium - 5
Row 3: Belize - Canada - 10
Row 4: Canada - Cape Verde - 7
Row 5: Cape Verde, etc...


The numbers in the third column don't have any significance. Since
the first column is complete, I want to shift the second and third
columns down a row after "Belgium" so there is a blank after Belize,
and the next row lines up - (it says Canada in all rows). So it
should become:

Row 1: America - America - 3
Row 2: Belgium - Belgium - 5
Row 3: Belize - BLANK
Row 4: Canada - Canada - 10
Row 5: Cape Verde - Cape Verde - 7
etc....


I have LOTS of columns and rows of this data, and I need to match them
all up. Is there a good way to do this? Or do I just have to do it
by hand??

Thanks SO much for your help!

Will

The following worked for me. Give it a try. The locations are hard
coded in, so you may need to alter the cell references to match your
needs.

Sub test()

Dim val1 As String
Dim val2 As String
Dim lastcell As Long

lastcell = Range("A65536").End(xlUp).Row

For i = 1 To lastcell
val1 = Cells(i, 1).Value
val2 = Cells(i, 2).Value
If val1 < val2 Then
Range(Cells(i, 2), Cells(i, 3)).Insert (xlShiftDown)
End If
Next i

End Sub

HTH,
John


On Feb 18, 7:21 pm, wrote:
I have three columns of data - the first is a master list of
countries, the second an incomplete list that has a third column
referring to this. I want to somehow create a script to match up the
second and third columns with the first. For example:

Row 1: America - America - 3
Row 2: Belgium - Belgium - 5
Row 3: Belize - Canada - 10
Row 4: Canada - Cape Verde - 7
Row 5: Cape Verde, etc...

The numbers in the third column don't have any significance. Since
the first column is complete, I want to shift the second and third
columns down a row after "Belgium" so there is a blank after Belize,
and the next row lines up - (it says Canada in all rows). So it
should become:

Row 1: America - America - 3
Row 2: Belgium - Belgium - 5
Row 3: Belize - BLANK
Row 4: Canada - Canada - 10
Row 5: Cape Verde - Cape Verde - 7
etc....

I have LOTS of columns and rows of this data, and I need to match them
all up. Is there a good way to do this? Or do I just have to do it
by hand??

Thanks SO much for your help!

Will

You might also like to try the Vlookup function. If you are unfamiliar with
it then check out help but feel free to get back to me if you have problems.
Insert 2 columns after your first column and lookup the data in the other 2
columns. Set the last argument to false so that it places #N/A in cells it
could not match and I assume you can handle that. A hint when using Vlookup
which I did not find in the help is that the Table Array portion must be
absolute if you want to copy the formula to other cells. (Press F4
immediately after you select the range to lookup)

" wrote:

The following worked for me. Give it a try. The locations are hard
coded in, so you may need to alter the cell references to match your
needs.

Sub test()

Dim val1 As String
Dim val2 As String
Dim lastcell As Long

lastcell = Range("A65536").End(xlUp).Row

For i = 1 To lastcell
val1 = Cells(i, 1).Value
val2 = Cells(i, 2).Value
If val1 < val2 Then
Range(Cells(i, 2), Cells(i, 3)).Insert (xlShiftDown)
End If
Next i

End Sub

HTH,
John


On Feb 18, 7:21 pm, wrote:
I have three columns of data - the first is a master list of
countries, the second an incomplete list that has a third column
referring to this. I want to somehow create a script to match up the
second and third columns with the first. For example:

Row 1: America - America - 3
Row 2: Belgium - Belgium - 5
Row 3: Belize - Canada - 10
Row 4: Canada - Cape Verde - 7
Row 5: Cape Verde, etc...

The numbers in the third column don't have any significance. Since
the first column is complete, I want to shift the second and third
columns down a row after "Belgium" so there is a blank after Belize,
and the next row lines up - (it says Canada in all rows). So it
should become:

Row 1: America - America - 3
Row 2: Belgium - Belgium - 5
Row 3: Belize - BLANK
Row 4: Canada - Canada - 10
Row 5: Cape Verde - Cape Verde - 7
etc....

I have LOTS of columns and rows of this data, and I need to match them
all up. Is there a good way to do this? Or do I just have to do it
by hand??

Thanks SO much for your help!

Will

Hi Will :

Try this lttle macro:

Option Explicit

Sub insertblanks()
Dim lRow As Long
Dim lCol As Long
Dim lColPlus1 As Long
Dim lColPlus2 As Long

If (MsgBox("Is the cursor in on the left column " _
& "of the first row of data?", _
vbCritical + vbYesNo, "Starting...") = vbNo) Then
MsgBox "Try again....", vbOKOnly, "Stopping..."
Exit Sub
End If
With ActiveSheet
lRow = ActiveCell.Row
lCol = ActiveCell.Column
lColPlus1 = lCol + 1
lColPlus2 = lCol + 2
Do While .Cells(lRow, lCol) < ""
If .Cells(lRow, lCol) < _
.Cells(lRow, lColPlus1) Then
.Range(.Cells(lRow, lColPlus1), _
.Cells(lRow, lColPlus2)).Insert xlDown
End If
lRow = lRow + 1
Loop
End With
End Sub

Just put the cursor in the top left cell of the table.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


" wrote:

I have three columns of data - the first is a master list of
countries, the second an incomplete list that has a third column
referring to this. I want to somehow create a script to match up the
second and third columns with the first. For example:

Row 1: America - America - 3
Row 2: Belgium - Belgium - 5
Row 3: Belize - Canada - 10
Row 4: Canada - Cape Verde - 7
Row 5: Cape Verde, etc...


The numbers in the third column don't have any significance. Since
the first column is complete, I want to shift the second and third
columns down a row after "Belgium" so there is a blank after Belize,
and the next row lines up - (it says Canada in all rows). So it
should become:

Row 1: America - America - 3
Row 2: Belgium - Belgium - 5
Row 3: Belize - BLANK
Row 4: Canada - Canada - 10
Row 5: Cape Verde - Cape Verde - 7
etc....


I have LOTS of columns and rows of this data, and I need to match them
all up. Is there a good way to do this? Or do I just have to do it
by hand??

Thanks SO much for your help!

Will

"Martin Fishlock" wrote:

Hi Will :

Try this lttle macro:

Option Explicit

Sub insertblanks()
Dim lRow As Long
Dim lCol As Long
Dim lColPlus1 As Long
Dim lColPlus2 As Long

If (MsgBox("Is the cursor in on the left column " _
& "of the first row of data?", _
vbCritical + vbYesNo, "Starting...") = vbNo) Then
MsgBox "Try again....", vbOKOnly, "Stopping..."
Exit Sub
End If
With ActiveSheet
lRow = ActiveCell.Row
lCol = ActiveCell.Column
lColPlus1 = lCol + 1
lColPlus2 = lCol + 2
Do While .Cells(lRow, lCol) < ""
If .Cells(lRow, lCol) < _
.Cells(lRow, lColPlus1) Then
.Range(.Cells(lRow, lColPlus1), _
.Cells(lRow, lColPlus2)).Insert xlDown
End If
lRow = lRow + 1
Loop
End With
End Sub

Just put the cursor in the top left cell of the table.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


" wrote:

I have three columns of data - the first is a master list of
countries, the second an incomplete list that has a third column
referring to this. I want to somehow create a script to match up the
second and third columns with the first. For example:

Row 1: America - America - 3
Row 2: Belgium - Belgium - 5
Row 3: Belize - Canada - 10
Row 4: Canada - Cape Verde - 7
Row 5: Cape Verde, etc...


The numbers in the third column don't have any significance. Since
the first column is complete, I want to shift the second and third
columns down a row after "Belgium" so there is a blank after Belize,
and the next row lines up - (it says Canada in all rows). So it
should become:

Row 1: America - America - 3
Row 2: Belgium - Belgium - 5
Row 3: Belize - BLANK
Row 4: Canada - Canada - 10
Row 5: Cape Verde - Cape Verde - 7
etc....


I have LOTS of columns and rows of this data, and I need to match them
all up. Is there a good way to do this? Or do I just have to do it
by hand??

Thanks SO much for your help!

Will


Hi Will !
Insert one cell each in the 2nd and 3rd columns -
push cells down - in the short cut menu (Right click). Don't bother to write
code to do the job.

VIJAY

THANKS EVERYONE!!

That was incredibly helpful. I really appreciate it!

Will


On 19 Feb, 07:21, Vijay Chary
wrote:
"Martin Fishlock" wrote:
Hi Will :

Try this lttle macro:

Option Explicit

Sub insertblanks()
Dim lRow As Long
Dim lCol As Long
Dim lColPlus1 As Long
Dim lColPlus2 As Long

If (MsgBox("Is the cursor in on the left column " _
& "of the first row of data?", _
vbCritical + vbYesNo, "Starting...") = vbNo) Then
MsgBox "Try again....", vbOKOnly, "Stopping..."
Exit Sub
End If
With ActiveSheet
lRow = ActiveCell.Row
lCol = ActiveCell.Column
lColPlus1 = lCol + 1
lColPlus2 = lCol + 2
Do While .Cells(lRow, lCol) < ""
If .Cells(lRow, lCol) < _
.Cells(lRow, lColPlus1) Then
.Range(.Cells(lRow, lColPlus1), _
.Cells(lRow, lColPlus2)).Insert xlDown
End If
lRow = lRow + 1
Loop
End With
End Sub

Just put the cursor in the top left cell of the table.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

" wrote:

I have three columns of data - the first is a master list of
countries, the second an incomplete list that has a third column
referring to this. I want to somehow create a script to match up the
second and third columns with the first. For example:

Row 1: America - America - 3
Row 2: Belgium - Belgium - 5
Row 3: Belize - Canada - 10
Row 4: Canada - Cape Verde - 7
Row 5: Cape Verde, etc...

The numbers in the third column don't have any significance. Since
the first column is complete, I want to shift the second and third
columns down a row after "Belgium" so there is a blank after Belize,
and the next row lines up - (it says Canada in all rows). So it
should become:

Row 1: America - America - 3
Row 2: Belgium - Belgium - 5
Row 3: Belize - BLANK
Row 4: Canada - Canada - 10
Row 5: Cape Verde - Cape Verde - 7
etc....

I have LOTS of columns and rows of this data, and I need to match them
all up. Is there a good way to do this? Or do I just have to do it
by hand??

Thanks SO much for your help!

Will

Hi Will !
Insert one cell each in the 2nd and 3rd columns -
push cells down - in the short cut menu (Right click). Don't bother to write
code to do the job.

VIJAY