Filtering Columns according to a list of variable names

**cgmstudnt** · 04-08-2020, 07:13 AM

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!

**Pepe Le Mokko** · 04-08-2020, 07:51 AM

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

**cgmstudnt** · 04-08-2020, 08:09 AM

Hi Pepe,
Thanks, I just fixed it.

**forester_xb** · 04-08-2020, 11:04 AM

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.

Filtering Columns according to a list of variable names

LinkBack

Thread Tools

Rate This Thread

Display

Filtering Columns according to a list of variable names

Re: Filtering Columns according to a list of variable names

Re: Filtering Columns according to a list of variable names

Re: Filtering Columns according to a list of variable names

Thread Information

Users Browsing this Thread

Similar Threads

How to populate names in a 16-box grid from a list of names and two variables columns

[SOLVED] Filtering the names from a list with multiple columns

Filtering a list in excel then saving into a PDF with different file names

Filtering a list of Names from Worksheet1 onto Worksheet 2 based on specific criteria

What is the best method for filtering a growing list of customer names?

[SOLVED] Filtering repeated names to get a list of unique names

[SOLVED] How- seperate a combined list by filtering out first list of names

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions