Comparing two columns with VBA, inserting blank rows moving associated data

[Saria Ahmad]

Hi folks, I am quite new to VBA. I need help in comparing the data in two columns. I want to bring similar data in same row and if nothing common is found I want to insert the blank row. I tried many ways but all in vain.
sample data file is attached below
Below is my code.
Looking forward for your valuable suggestions.


Sub compare()
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("A1:A11")
Set rng2 = Range("B1:B8")

If rng1.Value1 = rng2.Value2 Then
'both cells dont match.'
Else
Worksheets(1).Rows(1).Insert
End If
End Sub

 

[Joe4]

Welcome to the Board!

I am sorry, but your "Expected Results" do not make any sense to me, starting on row 21.
Not sure why there is a blank row there, and not elsewhere, and why you are not living up the values for I, J, and K.

I think we require more explanation on exactly what you want to happen.

 

[Saria Ahmad]

Welcome to the Board!

I am sorry, but your "Expected Results" do not make any sense to me, starting on row 21.
Not sure why there is a blank row there, and not elsewhere, and why you are not living up the values for I, J, and K.

I think we require more explanation on exactly what you want to happen.

Hi, I just seen that I have uploaded the incorrect image.
This is the sample file, as the original files has the text data inside the two columns. I want to compare the both columns. If the text in column A is present in Column B then it should come to the same row of both column. and if the the text of Column A is not present in Column B then I want to insert the single row.
I hope I could able to explain it better.

 

[Joe4]

I still don't understand the value of "M" in column B.
Why did that move up a row (from row 10 to row 9)?
Why would that not be at the end of the data, after everything else?

 

[Saria Ahmad]

I still don't understand the value of "M" in column B.
Why did that move up a row (from row 10 to row 9)?
Why would that not be at the end of the data, after everything else?

The order doesnt matter, the idea here is to only show that If M is not present in A, then empty line should appear.

 

[Joe4]

I am sure that you made a simplified example for this purpose, but to make sure that it will work on your real data, please answer the follow questions.
1. Does the first list always start in cell A2 and go down column A? If not, what are the details of where this data resides?
2. Does the second list always start in cell B2 and go down column B? If not, what are the details of where this data resides?
3. Is there any other data on this sheet that we don't to move or mess with?

 

[Saria Ahmad]

I am sure that you made a simplified example for this purpose, but to make sure that it will work on your real data, please answer the follow questions.
1. Does the first list always start in cell A2 and go down column A? If not, what are the details of where this data resides?
2. Does the second list always start in cell B2 and go down column B? If not, what are the details of where this data resides?
3. Is there any other data on this sheet that we don't to move or mess with?

Hi, Yes this is the simplified example. I am attaching the glimpse of the data. Here the I only need to compare column H and O and the comparison should begin from H8 and O8. Cell alignment(similar text in both columns should be on the same row and if no text found I want to insert a blank row) is what I need.

 

[Joe4]

OK, so it does appear that you do have data in other columns (at least G and N, if not more).
So, if we are inserting rows, should it only be contained to columns H and O, or should inserting rows insert a WHOLE blank row (moving the values in other columns down too)?

 

[Saria Ahmad]

OK, so it does appear that you do have data in other columns (at least G and N, if not more).
So, if we are inserting rows, should it only be contained to columns H and O, or should inserting rows insert a WHOLE blank row (moving the values in other columns down too)?

Yes, the columns of concern is only H&O and inserting rows can only be contained to columns H & O.

 

[Joe4]

In looking at your sample data in column O, it looks like there are multiple records with the same value.
Not having a unique 1-1 match complicates things greatly.
I am not sure what you expect/want to do with that situation.

Quite frankly, I think this is going to be a bit messy in Excel.
If it were me, I would import (or link) both lists into Microsoft Access, and do some Matched and Unmatched queries between the two lists, i.e.
1. One query to return the matching records between the the two lists (though duplicate records could still be problematic here)
2. One query to return the unmatched records from list 1 not in list 2
3. One query to return the unmatched records from list 2 not in list 1