Combine 2 Rows then remove duplicates

[Freeman2022]

Hello,

I have 2 columns with customer numbers, some of the columns have the same account number, I would like to combine the columns then remove all duplicates.

 

[gargamalebarbosa]

Do you have office 365 Insider account ?

 

[Joe4]

Couldn't you just copy one list to the bottom of the other, and then use Excel's built-in "Remove Duplicates" functionality?

 

[Freeman2022]

Do you have office 365 Insider account ?

Sadly no.

Couldn't you just copy one list to the bottom of the other, and then use Excel's built-in "Remove Duplicates" functionality?

LOL yes, that's what I've been doing in the past, I wasn't sure if there was another way or not.

 

[Freeman2022]

Do you have office 365 Insider account ?

Sadly no, just your regular 365 business, I don't get any of the cool features that they roll out first

 

[Z51]

This code should work

Sub freeman()
Dim lastrowa As Long, lastrowb As Long
lastrowa = Range("A" & Rows.Count).End(xlUp).Row
lastrowb = Range("B" & Rows.Count).End(xlUp).Row
Range("A" & lastrowa + 1).Resize(lastrowb - 1, 1).Value = Range("B2:B" & lastrowb).Value
Range("A1:A" & lastrowa + lastrowb - 1).RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

 

[Fluff]

How about

=LET(a,A2:A100,b,B2:B20,ra,ROWS(a),s,SEQUENCE(ra+ROWS(b)),UNIQUE(IF(s<=ra,a,INDEX(b,s-ra))))

 

[Freeman2022]

This code should work

Sub freeman()
Dim lastrowa As Long, lastrowb As Long
lastrowa = Range("A" & Rows.Count).End(xlUp).Row
lastrowb = Range("B" & Rows.Count).End(xlUp).Row
Range("A" & lastrowa + 1).Resize(lastrowb - 1, 1).Value = Range("B2:B" & lastrowb).Value
Range("A1:A" & lastrowa + lastrowb - 1).RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Thanks for your suggestion, however for this I do want to say away from VBA code, just in case I need to pass it on to someone who doesn't know how to use VBA.

 

[Joe4]

Sadly no.

LOL yes, that's what I've been doing in the past, I wasn't sure if there was another way or not.

OK, wasn't sure if this was just a "one-time" thing or not.
You just got some good VBA and non-VBA solutions (see Fluff's solution).

 

[Freeman2022]

How about

=LET(a,A2:A100,b,B2:B20,ra,ROWS(a),s,SEQUENCE(ra+ROWS(b)),UNIQUE(IF(s<=ra,a,INDEX(b,s-ra))))

Awesome!!!! It works, I love the new spill function....Thank you so MUCH!!!!!