+ Reply to Thread
Results 1 to 6 of 6

Delete columns in sheet1 that are not equal to columns in sheet2

  1. #1
    Registered User
    Join Date
    01-12-2016
    Location
    Europe
    MS-Off Ver
    MS 2013
    Posts
    48

    Delete columns in sheet1 that are not equal to columns in sheet2

    Hi Forum,

    I am trying to create a macro that is able to delete entire columns in sheet1, which don't have the same title in first row in sheet2. I find a macro a lot easier, since I got 7000+ columns.

    For example, column E in sheet 2 with title "Return (Day to Day) 2015-05-28 to 2015-05-28 USD" has identical title in sheet1, so this shouldn't be deleted. However, column F in sheet 2 with title "Return (Day to Day) 2015-05-26 to 2015-05-26 USD" is an identical titel, and should therefore be deleted.

    Any help is deeply appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Delete columns in sheet1 that are not equal to columns in sheet2

    Your requirement is a little unclear - but this will delete columns in Sheet1 which do not have a matching column in Sheet2:
    Please Login or Register  to view this content.
    Does that do what you wanted?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    01-12-2016
    Location
    Europe
    MS-Off Ver
    MS 2013
    Posts
    48

    Re: Delete columns in sheet1 that are not equal to columns in sheet2

    Yes, very close. However, the columns in sheet1 only have to be deleted if they are not equal to the first row in sheet2, i.e. the text.

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Delete columns in sheet1 that are not equal to columns in sheet2

    Quote Originally Posted by ugalskov View Post
    Yes, very close. However, the columns in sheet1 only have to be deleted if they are not equal to the first row in sheet2, i.e. the text.
    Yes, that's exactly what the code does...

  5. #5
    Valued Forum Contributor
    Join Date
    05-14-2012
    Location
    Unknown
    MS-Off Ver
    Unseen
    Posts
    429

    Re: Delete columns in sheet1 that are not equal to columns in sheet2

    Just for fun, a MUCH longer alternative that does pretty much the same thing but in a slightly different way.

    Bear in mind that the presence or absence of a space in a header (something as simple as that) could result in headers being seen as different and therefore deleted.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-12-2016
    Location
    Europe
    MS-Off Ver
    MS 2013
    Posts
    48

    Re: Delete columns in sheet1 that are not equal to columns in sheet2

    Thank you very much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Macro Needed to copy data from columns of sheet1 to columns of sheet2
    By 823 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-15-2015, 11:48 AM
  2. Replies: 0
    Last Post: 05-22-2014, 08:10 AM
  3. [SOLVED] Faster way to copy and paste multiple columns in Sheet1 to corresponding columns in Sheet2
    By babbeleme1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-19-2013, 12:55 AM
  4. Sheet1 is possible with 10 columns and filter columns 3 and paste in Sheet2?
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 03-17-2012, 05:08 PM
  5. Copy sheet1 columns to sheet2 automatically
    By charlie.cale in forum Excel General
    Replies: 0
    Last Post: 11-11-2011, 04:20 PM
  6. Hide columns on sheet2 based on cell on sheet1?
    By proepert in forum Excel General
    Replies: 4
    Last Post: 01-25-2010, 09:00 AM
  7. How to copy some columns from sheet1 to sheet2 or sheet3
    By wlarson in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2007, 08:58 AM

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