Combine list entries (with a twist)

richmondparker

New Member
Joined
Oct 10, 2005
Messages
13
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:

excel.gif


What I want:

excel2.gif


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]
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,645
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 10, 2005
Messages
13
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,645
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,626
Messages
5,707,483
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top