Delete entire column based on column heading (named range?)

[Aviles]

Hello,

I receive daily data that contains over 100 columns, most of which I don't need. But the issue is that the columns in this data are usually in different order and I only really need about 15 of the columns.

What I would like to do is have a macro that copies only the 15 columns that I need (based on specific column headings), and paste only these columns to another sheet.

I thought about using a named range to specify which column headings I want to use but I'm not sure if this would work or if there is an easier solution?

The column headings I receive will always be the same, just in different order.

Thanks in advance for your help.

 

[mrshl9898]

Could you just create a pivot table? And change the range daily?

 

[Aviles]

Could you just create a pivot table? And change the range daily?

Hi @mrshl9898
Sorry, I'm not really sure what you mean. Do you mean create a pivot table by manually selecting the 15 columns i need?
Because I have to do this function daily, I'm just looking for an automated way to exclude unnecessary columns, which gives me a much cleaner page to do the other analysis required.
Can the pivot table from a named range be put into a macro?

 

[alansidman]

Original Data

MOCK_DATA.xlsx
	A	B	C	D	E	F
1	id	first_name	last_name	email	gender	ip_address
2	1	Blondy	Anfonsi	banfonsi0@nifty.com	Female	78.193.170.245
3	2	Danit	Skittrell	dskittrell1@example.com	Female	197.197.74.69
4	3	Lela	Duffree	lduffree2@tripadvisor.com	Female	48.12.107.187
5	4	Loralie	Linzee	llinzee3@goodreads.com	Female	99.147.116.191
6	5	Myrtle	Lande	mlande4@desdev.cn	Female	254.193.153.190
7	6	Golda	Clegg	gclegg5@pen.io	Female	99.247.144.41
8	7	Winny	Schuh	wschuh6@goo.gl	Female	140.191.80.52
9	8	Darrick	Sones	dsones7@is.gd	Male	199.22.141.227
10	9	Ricki	Carvilla	rcarvilla8@psu.edu	Male	194.92.59.175
11	10	Felita	Janes	fjanes9@bbb.org	Female	51.100.64.123
12	11	Aloisia	Geddes	ageddesa@gnu.org	Female	84.160.26.241
13	12	Don	Maundrell	dmaundrellb@t.co	Male	120.225.55.217
14	13	Wang	Torrejon	wtorrejonc@phpbb.com	Male	227.139.35.216
15	14	Muffin	Haycock	mhaycockd@washington.edu	Female	131.41.210.179
16	15	Reade	Biffin	rbiffine@edublogs.org	Male	79.187.197.114
data

MOCK_DATA.xlsx
	A	B	C
1	first_name	last_name	email
2	Blondy	Anfonsi	banfonsi0@nifty.com
3	Danit	Skittrell	dskittrell1@example.com
4	Lela	Duffree	lduffree2@tripadvisor.com
5	Loralie	Linzee	llinzee3@goodreads.com
6	Myrtle	Lande	mlande4@desdev.cn
7	Golda	Clegg	gclegg5@pen.io
8	Winny	Schuh	wschuh6@goo.gl
9	Darrick	Sones	dsones7@is.gd
10	Ricki	Carvilla	rcarvilla8@psu.edu
11	Felita	Janes	fjanes9@bbb.org
12	Aloisia	Geddes	ageddesa@gnu.org
13	Don	Maundrell	dmaundrellb@t.co
14	Wang	Torrejon	wtorrejonc@phpbb.com
15	Muffin	Haycock	mhaycockd@washington.edu
16	Reade	Biffin	rbiffine@edublogs.org
Sheet1

With Power Query here is the Mcode in which you select the columns you wish to keep.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"first_name", type text}, {"last_name", type text}, {"email", type text}, {"gender", type text}, {"ip_address", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"first_name", "last_name", "email"})
in
    #"Removed Other Columns"

So long as the headers don't change names, this will work for you

 

[mrshl9898]

Hi @mrshl9898
Sorry, I'm not really sure what you mean. Do you mean create a pivot table by manually selecting the 15 columns i need?
Because I have to do this function daily, I'm just looking for an automated way to exclude unnecessary columns, which gives me a much cleaner page to do the other analysis required.
Can the pivot table from a named range be put into a macro?

Just meant select all columns needed in the Pivot, once the Pivot is set up you won't need to change anything but the source. Or the above post.

 

[Aviles]

Thank you. I'm not that familiar with Power Query, but I'll look into your suggestions.