How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask John D Your Own Question

John D
John D, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9666
Experience:  Bachelor of Science - Engineering Consultant
53785
Type Your Microsoft Office Question Here...
John D is online now
A new question is answered every 9 seconds

I have two excel spreadsheets. One with a list of names (SPS-A).

This answer was rated:

I have two excel spreadsheets. One with a list of names (SPS-A). The second with a list of accounts with multiple names SPS-B). Some of the records in SPS-B have multiple accounts that matches the names in SPS-A. Some of the accounting info in SPS-B do not match the names in SPS-A. Those I can delete all of the records. I am trying to add these spreadsheets into Access to make reports. Can you help?

Hi,

 

Could you put the two spreadsheets in one workbook (if they aren't already) and attach the file here so I can work on it

 

Here is how to attach it:

 

graphic

Customer: replied 4 years ago.

Attachment: 2012-03-04_175128_test_workbook.xls

Please do not publish the names on the web. They are real names. If you can give me the formula I can added to spreadsheet. If we can place this in an access database it would work better.

So you want to delete from column B all names that do not have a match in column A. Correct?

 

 

So you want to delete from column B all names that do not have a match in column A. Correct?

 

 

Customer: replied 4 years ago.

Correct, I need to keep the duplicate names in column B that match Column A. Those matches up with multiple accounts for the individual. I need the names in Column B that do not match column A to be removed. Example: Abney,Thomas is in column B, but not in column A. I need all records for Abney,Thomas to be removed.

Ok here are the formulas (in the yellow cells) which identify with an "X" the names in column B that do not have a match in column A. You can copy the formulas to as many rows down as you need, or to any other spreadsheet

 

Attachment: 2012-03-04_181916_2012-03-04_175128_test_workbook1.xls

 

 

Hope this helps. Let me know if you have any questions, and thank you for clicking Accept if you are satisfied with the solution

 

 

 

Customer: replied 4 years ago.

I ran the formula and it did add the X to the correct records. I was expecting to sort on column C to move all the X's together to delete the records, but I am unable to do it. I went to Custom sort and selected column c to sort upon. It sorts, but leave the X's in the same location. I also tried to add column D and copy/paste special the X's, but the same thing happened. Any ideas?

No I was just showing you the formulas. I could have sent you the end result but you wouldn't be able to see the formulas after the data has been processed and deleted

 

Here is the file after deleting the

 

Attachment: 2012-03-04_184205_2012-03-04_175128_test_workbook1a.xls

 

 

Hope this helps

 

Customer: replied 4 years ago.
Yes, that is what I need. Can you please send the formula?

The formulas are in the yellow cells in the first file that I sent you

 

Thank you for clicking Accept if you are satisfied with the solution. I will be happy to assist you further after that

 

 

I can send you the step by step instructions if you like

 

 

Customer: replied 4 years ago.
I guess I didn't give all the information needed. The names in column B have other data on the same row. There are data in columns D - X which also must be removed along with the names in Column B. The formula did place an X in column C, but did not remove the rows of data. So what I am seeking is: a way to remove all the data B - X for each row of data that do not match the names in Column A. Sorry.

No problem I will prepare now the instructions to achieve the final result. In the meantime you can click accept if the results are correct as shown in the last file

 

 

John D and other Microsoft Office Specialists are ready to help you

Thanks

 

Ok assuming you are using Excel 2007 or 2010

 

1- Open the file that I sent you first (with the formulas)

 

2- Select the entire column C (that has the formulas)

 

3- Copy it, then while it is still selected right click on the selection and choose Paste Special, then select Values, and click OK

 

4- Now select the range B2 through C52 (i.e. through the last cell)

 

5- Click Data > Sort, and uncheck the box 'My Data has Headers'

 

6- In the sort fields select column C , and Z to A, then click OK

 

7- This will bring up all the cells that have X's to the top

 

8- Select the cells that have X's together with their corresponding cells in column, and press Delete

 

Hope this is clear. Let me know if you have any questions

 

 

 

 

Customer: replied 4 years ago.
I did something similar. I copied the entire worksheet and pasted it special to a new worksheet. Then sorted it. It also achieved the results I was seeking. Thanks you very much for your assistance and time. GREAT JOB!!!

Excellent! Thanks again