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

T

TroyT

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
Before:
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!
 
J

Joel

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
 
T

TroyT

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?
 
J

Joel

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
 
T

TroyT

Perfect!
Thanks a million Joel!

Troy

Joel said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top