Compare and combine two columns

sailingnut

New Member
Joined
Oct 28, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
How do I compare and combine two columns? Column A has a list of business email addresses. Column B has a list of personal email addresses. I want to compare column A and B and put in column C the value from column A if they match. If they do not match, put into column C what is contained in column A. Also, put into column C the contents from column B if a cell in column A is blank.
 

Attachments

  • Screenshot 2020-10-28 225023.jpg
    Screenshot 2020-10-28 225023.jpg
    194 KB · Views: 5

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,075
maybe easier will be to append these columns into one and then remove duplicates
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,825
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. Hence my simplified samples. ;)

Is this what you mean?

20 10 29.xlsm
ABC
1BusinessResidenceList
2aaa
3bzb
4cc
5dyd
6eee
7ss
8qq
9hhh
10ii
11jxj
Combine lists
Cell Formulas
RangeFormula
C2:C11C2=IF(A2="",B2,A2)
 

sailingnut

New Member
Joined
Oct 28, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows

Peter. I should have give a better picture of what I'm trying to do.
List example is above.
In the case of Jana, it compared and since they were the same, it brought in Business email to the Results
In the case of Max, it compared and because they were different, it brought in Business email to the Results
In the case of Gabe, Business email was blank, so it brought in Personal email to the Results
Int the case of Sam, Personal email was blank, so it brought in Business email to the Results.
So the Results field would contain some formula to accommodate those scenarios.
 

McGuilliam

New Member
Joined
Oct 23, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
You could try a formula like this:

Excel Formula:
=if(E2=F2;E1;if(E2<>"";E2;F2))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,825
Office Version
  1. 365
Platform
  1. Windows
I should have give a better picture of what I'm trying to do.
List example is above.
In the case of Jana, it compared and since they were the same, it brought in Business email to the Results
In the case of Max, it compared and because they were different, it brought in Business email to the Results
In the case of Gabe, Business email was blank, so it brought in Personal email to the Results
Int the case of Sam, Personal email was blank, so it brought in Business email to the Results.
So the Results field would contain some formula to accommodate those scenarios.
That is exactly what my formula already did (except the emails are now in columns B & C not A & B as you first stated). Here it is again with your sample data & layout.

20 10 29.xlsm
ABCDEF
1NameBusiness emailPersonal emailFormula ResultsYour results from post 4Compare
2Jana Willisjwillis@tac.edujwillis@tac.edujwillis@tac.edujwillis@tac.eduTRUE
3Max Willismaxwillis@wcu.edumaxwillis@gmail.commaxwillis@wcu.edumaxwillis@wcu.eduTRUE
4Gabe Willisgwillis@gmail.comgwillis@gmail.comgwillis@gmail.comTRUE
5Sam Willissamwillis@grove.edusamwillis@grove.edusamwillis@grove.eduTRUE
Combine lists (2)
Cell Formulas
RangeFormula
D2:D5D2=IF(B2="",C2,B2)
F2:F5F2=D2=E2
 

Watch MrExcel Video

Forum statistics

Threads
1,113,895
Messages
5,544,901
Members
410,643
Latest member
sng
Top