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

Saria Ahmad

New Member
Joined
Feb 23, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Annotation 2021-02-23 122257.png
    Annotation 2021-02-23 122257.png
    27.4 KB · Views: 8

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 23, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • Image1.png
    Image1.png
    15.2 KB · Views: 6

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 23, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 23, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

Attachments

  • Image 2.png
    Image 2.png
    47 KB · Views: 7

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 23, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,000
Messages
5,622,120
Members
415,878
Latest member
jjj12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top