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.

>>> use code - tags <<<

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

 

[Marc L]

Hi !​

​

According to this forum rules :​

• for any VBA question do not post in the formulas Excel section but here in the VBA Macros section​

• use code tags via the 3 dots icon …​

​

Why in your result M is not after K ?! (Easier to code & faster execution …)​

Where should be exactly the result ?​

​

In your code you must compare cell by cell rather than range by range.​

​

Even in VBA you can use Excel basics like for example the MATCH worksheet function,​

here needing only two formulas with less than ten codelines …​

​

 

[AlanSidman]

With Power Query

let
    Source = Table.NestedJoin(Table1, {"Column1"}, Table2, {"Column1"}, "Table2", JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"Column1"}, {"Table2.Column1"})
in
    #"Expanded Table2"

Data Range

	A​	B​
1​	Column1​	Table2.Column1​
2​	A​	A​
3​	C​	C​
4​	B​	B​
5​	I​	I​
6​	D​	D​
7​	E​	​
8​	F​	​
9​	G​	​
10​	H​	​
11​	J​	J​
12​	K​	K​
13​	​	M​