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
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

Column1​

[/td][td]

Table2.Column1​

[/td][/tr]

[tr][td]

2​

[/td][td]

A​

[/td][td]

A​

[/td][/tr]

[tr][td]

3​

[/td][td]

C​

[/td][td]

C​

[/td][/tr]

[tr][td]

4​

[/td][td]

B​

[/td][td]

B​

[/td][/tr]

[tr][td]

5​

[/td][td]

I​

[/td][td]

I​

[/td][/tr]

[tr][td]

6​

[/td][td]

D​

[/td][td]

D​

[/td][/tr]

[tr][td]

7​

[/td][td]

E​

[/td][td]

​

[/td][/tr]

[tr][td]

8​

[/td][td]

F​

[/td][td]

​

[/td][/tr]

[tr][td]

9​

[/td][td]

G​

[/td][td]

​

[/td][/tr]

[tr][td]

10​

[/td][td]

H​

[/td][td]

​

[/td][/tr]

[tr][td]

11​

[/td][td]

J​

[/td][td]

J​

[/td][/tr]

[tr][td]

12​

[/td][td]

K​

[/td][td]

K​

[/td][/tr]

[tr][td]

13​

[/td][td]

​

[/td][td]

M​

[/td][/tr]
[/table]