+ Reply to Thread
Results 1 to 9 of 9

Find and Replace

  1. #1
    Registered User
    Join Date
    11-14-2012
    Location
    Boston
    MS-Off Ver
    Excel 2010
    Posts
    11

    Find and Replace

    This might be better suited for the Macro forum, but I believe this should be possible with a formula. I am very new to excel, but have used the IF function before...which is what I assume I will need for this.

    I have a two column spreadsheet with this data:

    Column A/ Column B
    George/ teacher
    Margret/ admin
    Sandy/admin
    Emily/teacher
    Mark/admin
    Michael/admin

    I need the values in Column A to be deleted if the value in Column B=Teacher

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Find and Replace

    A non-VB way would be in a separate column..

    =IF(B1="teacher","",A1)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Find and Replace

    Or you can filter on column B for admin and select and delete those rows
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    11-14-2012
    Location
    Boston
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Find and Replace

    Thanks for the quick response. I was hoping there was a way to run it on the entire column at once (B:B, right?). I'm guessing the only way to do that is with a macro? Have you seen a post on how to do this yet? I need to run this on various spreadsheets that sometimes do not share the same number of rows. my understanding is if I use the macro help, it is based on what row location or number, but I need to define the relationship between the two columns.

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Find and Replace

    So are you looking for a button that will delete all "teacher" rows? Filtering and deleting does it on all spreadsheet at once. See example attached.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-14-2012
    Location
    Boston
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Find and Replace

    Thanks again. But I don't want to delete the entire row. I want only the data in the column a cells to go away.

    So a successful formula would make it look like this:



    Column A/ Column B
    / teacher
    Margret/ admin
    Sandy/admin
    /teacher
    Mark/admin
    Michael/admin

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Find and Replace

    Okay, change step 3 to
    Step 3a: Select Column A (excluding row 1) and right click to "Clear Contents"
    Step 3b: Remove filter

    Still want that macro? If so, do you want it specific to a workbook or able to use in multiple workbooks?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-14-2012
    Location
    Boston
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Find and Replace

    I would love it!

    I would like to be able to use it across mulctiple workbooks, even more, these two rows may not always be next to each other or in any particular order. Is is possible to write it so that the relationship is known based on the header, not the column?

  9. #9
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Find and Replace

    Okay, this macro needs to be put into your Personal Macro Workbook
    Assuming you do not have a Personal Macro Workbook, follow these instructions
    http://office.microsoft.com/en-us/ex...#_Toc275246697

    Copy this macro to your personal macro workbook. This assumes the headers are "Position" (where teacher and admin would appear) and "Name" (where the name is that you want to erase). I am attaching a workbook with the macro embedded to show you it works. Test it on the 3 sheets.
    Please Login or Register  to view this content.
    Questions?
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1