Comparing Data in two columns

[Guest]

I have an excel spreadsheet that I would like to be able to automate the
process of comparing two columns of data and then inserting blank cells into
the 2nd column which would continue to move the data in the 2nd column down
to the next row. I do not want to insert a row.

here is sample data before
col1 col2 col3 col4
abbott joe baker sally
atkins ted conti greg
baker sally
conti greg fudd elmer
doe john
fudd elmer

After comparing the names should match up or the cell should be blank.So the
data would look like this.

col1 col2 col3 col4
abbott joe
atkins ted
baker sally baker sally
conti greg conti greg
doe john
fudd elmer fudd elmer

I would appreciate any suggestions to make this process go quicker. Right
now I am doing this manually. Maybe something using a macro or VBA in Excel?
I know how to use VBA but I am not sure how to write the code.

Marianne

 

[Jay]

I have an excel spreadsheet that I would like to be able to automate

the process of comparing two columns of data and then inserting blank
cells into the 2nd column which would continue to move the data in the
2nd column down to the next row. I do not want to insert a row.

here is sample data before
col1 col2 col3 col4
abbott joe baker sally
atkins ted conti greg
baker sally
conti greg fudd elmer
doe john
fudd elmer

After comparing the names should match up or the cell should be
blank. So the data would look like this.

col1 col2 col3 col4
abbott joe
atkins ted
baker sally baker sally
conti greg conti greg
doe john
fudd elmer fudd elmer

I would appreciate any suggestions to make this process go quicker.

....

The following works with the example. However, there may be unstated
assumptions in the problem statement, so proceed with care.

At the top of col5, put the formula:
=IF(ISERROR(VLOOKUP(A1,C,2,FALSE)), "",
IF(VLOOKUP(A1,C,2,FALSE)=B1,A1,""))

At the top of col6, put the formula:
=IF(ISERROR(VLOOKUP(A1,C,2,FALSE)), "",
IF(VLOOKUP(A1,C,2,FALSE)=B1,B1,""))

Extend these two down for as many rows as you need. Then hide col3 and
col4.

 

[Guest]

This solution worked perfectly. Thank you very much for your help. You have
saved me many hours of manual labor.

Marianne

 

[Jay]

I have an excel spreadsheet that I would like to be able to

....

The following works with the example. However, there may be unstated
assumptions in the problem statement, so proceed with care. ...

But wait, there's more.

After some thought, consider the new and improved version below. For one
thing, it allows two people to have the same last name but different first
names. Also, it corrects for somebody mistakenly typing a space at the end
of a name. More fixes are possible, so, as before, proceed with caution.

At the top of col5, put
=TRIM(C1)&"<>"&TRIM(D1)

At the top of col6, put
=IF(COUNTIF(E:E,TRIM(A1)&"<>"&TRIM(B1))>0, TRIM(A1), "")

At the top of col7, put
=IF(F1="","",TRIM(B1))

Extend these three down for as many rows as you need. Then hide col3, col4
and col5.