Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Comparing Data in two columns
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 |
#2
|
|||
|
|||
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:D,2,FALSE)), "", IF(VLOOKUP(A1,C:D,2,FALSE)=B1,A1,"")) At the top of col6, put the formula: =IF(ISERROR(VLOOKUP(A1,C:D,2,FALSE)), "", IF(VLOOKUP(A1,C:D,2,FALSE)=B1,B1,"")) Extend these two down for as many rows as you need. Then hide col3 and col4. |
#3
|
|||
|
|||
This solution worked perfectly. Thank you very much for your help. You have
saved me many hours of manual labor. :-) Marianne "Jay" wrote: 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:D,2,FALSE)), "", IF(VLOOKUP(A1,C:D,2,FALSE)=B1,A1,"")) At the top of col6, put the formula: =IF(ISERROR(VLOOKUP(A1,C:D,2,FALSE)), "", IF(VLOOKUP(A1,C:D,2,FALSE)=B1,B1,"")) Extend these two down for as many rows as you need. Then hide col3 and col4. |
#4
|
|||
|
|||
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. ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colors of columns after sorting data in the supporting table | Charts and Charting in Excel | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
How to get pivot table data columns instead of rows | Excel Discussion (Misc queries) | |||
How do I link columns so data flows from 1 column to another like. | Excel Discussion (Misc queries) | |||
Drop-down selection fills data across multiple columns | Excel Discussion (Misc queries) |