+ Reply to Thread
Results 1 to 4 of 4

Filtering Columns according to a list of variable names

  1. #1
    Registered User
    Join Date
    01-10-2020
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    3

    Question Filtering Columns according to a list of variable names

    Hi!

    I am facing a very silly problem. However, I am unable to solve so far.
    I have a table with approximately 800 columns and 200 rows.
    The ROW 1 is the variable names (800 variables).

    I actually only need around 150 variables (I have a list of the variable, column names that should remain).
    Thus, 150 columns out of the 800 columns.
    However, the names are complicated and to delete each column by looking at it is tricky and time-consuming.
    I tried to use the advance filter. However, it was returning only ROW 1 for the 150 columns and deleting the information for these 150 variables, which means, not returning the other 199 rows.

    I uploaded a data example of part of my table.

    Thank you already for your help!
    Attached Files Attached Files
    Last edited by cgmstudnt; 04-08-2020 at 08:08 AM.

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    10,965

    Re: Filtering Columns according to a list of variable names

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Registered User
    Join Date
    01-10-2020
    Location
    Leeds
    MS-Off Ver
    365
    Posts
    3

    Re: Filtering Columns according to a list of variable names

    Hi Pepe,
    Thanks, I just fixed it.

  4. #4
    Registered User
    Join Date
    04-05-2020
    Location
    Hannover, Germany
    MS-Off Ver
    MS Office 365
    Posts
    15

    Re: Filtering Columns according to a list of variable names

    Hi,you can do as follows:

    (1)Copy and paste your table as Transpose in a new sheet from column B(leave Column A empty), then you get a table with 800 rows and 200 columns with 800 variables in column B
    (2)Copy and paste your final list in column A
    (3)Simultaneously choose your final list(only list) and column B(whole column), you can achieve this by:
    - first select your list
    - then press Ctrl + select whole column B
    (4)In Home tab, choose Conditional Formatting-Highlight Cells Rules-Duplicate Values..-OK
    Capture.JPG
    (5)Then filter Column B by color(Light Red), so that all variables duplicated with variables in your final list will be filtered out, together with their following information in the same row
    (6)Copy and paste the filtered result as Transpose again in a new sheet, thus you will get the 150 variables spread in the first row with their corresponding information in the following 199 rows

    Please see attached the file with solution operations.
    Hope it solves your problem.
    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)

Similar Threads

  1. Replies: 7
    Last Post: 06-07-2019, 03:50 PM
  2. [SOLVED] Filtering the names from a list with multiple columns
    By mehmetics in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-05-2019, 05:29 PM
  3. Filtering a list in excel then saving into a PDF with different file names
    By aexcelv in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-24-2018, 11:07 PM
  4. Filtering a list of Names from Worksheet1 onto Worksheet 2 based on specific criteria
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2014, 05:19 AM
  5. What is the best method for filtering a growing list of customer names?
    By mcgriffin in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-04-2014, 03:54 AM
  6. [SOLVED] Filtering repeated names to get a list of unique names
    By grumpyguppy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-06-2014, 11:48 PM
  7. [SOLVED] How- seperate a combined list by filtering out first list of names
    By Briana in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-21-2006, 11:16 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