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: 9655
Experience:  Bachelor of Science - Engineering Consultant
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).

Resolved Question:

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?
Submitted: 2 years ago.
Category: Microsoft Office
Expert:  John D replied 2 years ago.

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 2 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.
Expert:  John D replied 2 years ago.

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

 

 

Expert:  John D replied 2 years ago.

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

 

 

Customer: replied 2 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.

Expert:  John D replied 2 years ago.

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 2 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?

Expert:  John D replied 2 years ago.

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 2 years ago.
Yes, that is what I need. Can you please send the formula?
Expert:  John D replied 2 years ago.

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

 

 

Expert:  John D replied 2 years ago.

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

 

 

Customer: replied 2 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.
Expert:  John D replied 2 years ago.

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, Bachelor's Degree
Category: Microsoft Office
Satisfied Customers: 9655
Experience: Bachelor of Science - Engineering Consultant
John D and 2 other Microsoft Office Specialists are ready to help you
Expert:  John D replied 2 years ago.

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 2 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!!!
Expert:  John D replied 2 years ago.

Excellent! Thanks again

 

 

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional