Combine list entries (with a twist)

[richmondparker]

I have a list of student information in columns A-D. Some names are repeated and some are not (the result of combining two different lists). Each name has either an X in column E or an X in column F, and the names that appear twice have both. I want to sort the list so that I have no names repeated while retaining the information from columns E and F.

What I have:

What I want:

Someone suggested using VLOOKUP(), but I'm afraid I've never used it and had a hard time figuring out how to proceed. Any suggestions?[/img]

 

[Peter_SSs]

richmondparker

1. Do the repeated names ever appear more than twice?
2. Are the repeated names always in adjacent rows like the example?
3. If the names (columns A & B) repeat, do we also need to check that the data in columns C & D are identical as well?

 

[richmondparker]

1. NO. The repeated names only appear twice, if repeated.
2. YES. The names are always in adjacent rows.
3. NO. The information in columns C & D is identical. In my example, John Adams is the name of a student who appears twice, and the last name of his homeroom teacher (Homeroom 100) is listed as SMITH in both rows.

 

[Peter_SSs]

A VBA solution might be efficient for this, but here is one non-VBA approach (if I have understood the circumstances correctly).

1. Select Column F by clicking its heading label.
2. Edit|Go to...|Special...|Blanks|OK
3. Note the row of the active cell. For your sample data the active cell would be F2, so the row is 2.
4. Type =IF(A2&B2=A3&B3,"X","") noting the 2s in this formula relate to the row mentioned in step 3 and the 3s in this formula would be the very next row. Confirm this formula to all the selected blank cells with Ctrl+Enter (not just Enter).
5. Select column F again by clicking its heading label.
6. Copy then Edit|Paste Special...|Values|OK
7. Select columns E & F then Data|Filter|AutoFilter
8. Filter column F to 'X' values and column E to (Blanks)
9. Select the remaining visible rows below the headings then Edit|Delete Row|OK
10. Data|Filter|AutoFilter to remove the filters and show all remaining rows.

 

[richmondparker]

WHEW! Worked like a charm. Thanks a million.