Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compare data in two columns | Excel Worksheet Functions | |||
Compare data in two columns | Excel Worksheet Functions | |||
compare data from 2 columns | Excel Worksheet Functions | |||
Need to compare data in 2 columns and not sure how. | Charts and Charting in Excel | |||
Trying to compare data in two columns... | Excel Worksheet Functions |