VB Help/Request? Compare and insert to different data in columns
 

I looked through the boards, came close to finding what I needed... but they
were off a little.

I have ColA and ColB filled to about 30000 Rows. I would like a script that
compares each column, and inserts a/some rows if they dont match up. The
blank column could be inserted on either column and if there is 20 rows of
missing data, it inserts 20 rows of blanks.

Ex:
The script would
Befo
Col1 Col2
1 1
2 2
3 4
4 5
5 6
7 10

After:
Col1 Col2
1 1
2 2
3
4 4
5 5
6
7
10

Is this possible?

Thanks for the help!

VB Help/Request? Compare and insert to different data in columns
 

The code will work if the data iun columns A and B are sorted and they and
data in columns A & B are numbers.

Sub comparecol()

RowCount = 1
Do While Cells(RowCount, "A").Value < ""

Diff = Cells(RowCount, "A").Value - _
Cells(RowCount, "B").Value

If Diff < 0 Then
Cells(RowCount, "B").Insert xlShiftDown
Else
If Diff 0 Then
Cells(RowCount, "A").Insert (xlShiftDown)
End If
End If
RowCount = RowCount + 1
Loop

End Sub


"TroyT" wrote:

I looked through the boards, came close to finding what I needed... but they
were off a little.

I have ColA and ColB filled to about 30000 Rows. I would like a script that
compares each column, and inserts a/some rows if they dont match up. The
blank column could be inserted on either column and if there is 20 rows of
missing data, it inserts 20 rows of blanks.

Ex:
The script would
Befo
Col1 Col2
1 1
2 2
3 4
4 5
5 6
7 10

After:
Col1 Col2
1 1
2 2
3
4 4
5 5
6
7
10

Is this possible?

Thanks for the help!

VB Help/Request? Compare and insert to different data in colum
 

Joel,
Thanks for the response.. but the information is not all numbers. It is a
directory pathway, and filenames. I am comparing 2 sets to see what is
missing. So it is numbers and letters.. Maybe another way to do this?

"Joel" wrote:

The code will work if the data iun columns A and B are sorted and they and
data in columns A & B are numbers.

Sub comparecol()

RowCount = 1
Do While Cells(RowCount, "A").Value < ""

Diff = Cells(RowCount, "A").Value - _
Cells(RowCount, "B").Value

If Diff < 0 Then
Cells(RowCount, "B").Insert xlShiftDown
Else
If Diff 0 Then
Cells(RowCount, "A").Insert (xlShiftDown)
End If
End If
RowCount = RowCount + 1
Loop

End Sub


"TroyT" wrote:

I looked through the boards, came close to finding what I needed... but they
were off a little.

I have ColA and ColB filled to about 30000 Rows. I would like a script that
compares each column, and inserts a/some rows if they dont match up. The
blank column could be inserted on either column and if there is 20 rows of
missing data, it inserts 20 rows of blanks.

Ex:
The script would
Befo
Col1 Col2
1 1
2 2
3 4
4 5
5 6
7 10

After:
Col1 Col2
1 1
2 2
3
4 4
5 5
6
7
10

Is this possible?

Thanks for the help!

VB Help/Request? Compare and insert to different data in colum
 

I thought I included in my response that for strings you needed to use strcomp.

Sub comparecol()

RowCount = 1
Do While Cells(RowCount, "A").Value < ""

Diff = strcomp(Cells(RowCount, "A").Value, _
Cells(RowCount, "B").Value)

If Diff < 0 Then
Cells(RowCount, "B").Insert xlShiftDown
Else
If Diff 0 Then
Cells(RowCount, "A").Insert (xlShiftDown)
End If
End If
RowCount = RowCount + 1
Loop

End Sub

"TroyT" wrote:

Joel,
Thanks for the response.. but the information is not all numbers. It is a
directory pathway, and filenames. I am comparing 2 sets to see what is
missing. So it is numbers and letters.. Maybe another way to do this?

"Joel" wrote:

The code will work if the data iun columns A and B are sorted and they and
data in columns A & B are numbers.

Sub comparecol()

RowCount = 1
Do While Cells(RowCount, "A").Value < ""

Diff = Cells(RowCount, "A").Value - _
Cells(RowCount, "B").Value

If Diff < 0 Then
Cells(RowCount, "B").Insert xlShiftDown
Else
If Diff 0 Then
Cells(RowCount, "A").Insert (xlShiftDown)
End If
End If
RowCount = RowCount + 1
Loop

End Sub


"TroyT" wrote:

I looked through the boards, came close to finding what I needed... but they
were off a little.

I have ColA and ColB filled to about 30000 Rows. I would like a script that
compares each column, and inserts a/some rows if they dont match up. The
blank column could be inserted on either column and if there is 20 rows of
missing data, it inserts 20 rows of blanks.

Ex:
The script would
Befo
Col1 Col2
1 1
2 2
3 4
4 5
5 6
7 10

After:
Col1 Col2
1 1
2 2
3
4 4
5 5
6
7
10

Is this possible?

Thanks for the help!

VB Help/Request? Compare and insert to different data in colum
 

Perfect!
Thanks a million Joel!

Troy

"Joel" wrote:

I thought I included in my response that for strings you needed to use strcomp.

Sub comparecol()

RowCount = 1
Do While Cells(RowCount, "A").Value < ""

Diff = strcomp(Cells(RowCount, "A").Value, _
Cells(RowCount, "B").Value)

If Diff < 0 Then
Cells(RowCount, "B").Insert xlShiftDown
Else
If Diff 0 Then
Cells(RowCount, "A").Insert (xlShiftDown)
End If
End If
RowCount = RowCount + 1
Loop

End Sub

"TroyT" wrote:

Joel,
Thanks for the response.. but the information is not all numbers. It is a
directory pathway, and filenames. I am comparing 2 sets to see what is
missing. So it is numbers and letters.. Maybe another way to do this?

"Joel" wrote:

The code will work if the data iun columns A and B are sorted and they and
data in columns A & B are numbers.

Sub comparecol()

RowCount = 1
Do While Cells(RowCount, "A").Value < ""

Diff = Cells(RowCount, "A").Value - _
Cells(RowCount, "B").Value

If Diff < 0 Then
Cells(RowCount, "B").Insert xlShiftDown
Else
If Diff 0 Then
Cells(RowCount, "A").Insert (xlShiftDown)
End If
End If
RowCount = RowCount + 1
Loop

End Sub


"TroyT" wrote:

I looked through the boards, came close to finding what I needed... but they
were off a little.

I have ColA and ColB filled to about 30000 Rows. I would like a script that
compares each column, and inserts a/some rows if they dont match up. The
blank column could be inserted on either column and if there is 20 rows of
missing data, it inserts 20 rows of blanks.

Ex:
The script would
Befo
Col1 Col2
1 1
2 2
3 4
4 5
5 6
7 10

After:
Col1 Col2
1 1
2 2
3
4 4
5 5
6
7
10

Is this possible?

Thanks for the help!