• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Saria Ahmad

New Member
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 <<<
Code:
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

Last edited by a moderator:

Marc L

Excel Ninja
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

Well-Known Member
With Power Query

Code:
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​
 
Top