Find columns with the same name and copy to new worksheet

[thedeadzeds]

Hi Guys.

I have a table which is exported daily. As you can see lots of columns contain the same name. I would like to create 3 seperate worksheets. 1 containing all of the Bonus Columns, 1 containing all of the Back Columns, and one containing all of the Front Columns. Also, each worksheet should also include Site, Name, Type and Sale

Many thanks

Site	Name	Stage	Result	Type	Order	Sale	Bonus	Bonus	Bonus	Bonus	Bonus	Front	Front	Front	Front	Front	Front	Back	Back	Back	Back	Back	Back
Cardiff	Steve	1	Y	New	Y	n	10	24	38	52	66	80	94	108	122	136	150	164	178	192	206	220	234
Cardiff	Jim	1	N	Used	y	n	11	25	39	53	67	81	95	109	123	137	151	165	179	193	207	221	235
Cardiff	Dave	1	N	Used	y	n	15	29	43	57	71	85	99	113	127	141	155	169	183	197	211	225	239
Cardiff	Steve	1	N	Used	y	n	25	39	53	67	81	95	109	123	137	151	165	179	193	207	221	235	249
Cardiff	Jim	4	N	Used	y	n	42	56	70	84	98	112	126	140	154	168	182	196	210	224	238	252	266
Cardiff	Dave	3	Y	Used	y	y	52	66	80	94	108	122	136	150	164	178	192	206	220	234	248	262	276
Cardiff	Matt	5	Y	Used	n	y	5	19	33	47	61	75	89	103	117	131	145	159	173	187	201	215	229
Cardiff	Dave	6	Y	Used	n	y	54	68	82	96	110	124	138	152	166	180	194	208	222	236	250	264	278
Cardiff	Matt	8	N	Used	n	y	12	26	40	54	68	82	96	110	124	138	152	166	180	194	208	222	236
Birmingham	Steve	1	N	Used	n	y	12	26	40	54	68	82	96	110	124	138	152	166	180	194	208	222	236
Birmingham	Jim	1	N	New	n	y	3	17	31	45	59	73	87	101	115	129	143	157	171	185	199	213	227
Birmingham	Dave	1	Y	New	y	n	65	79	93	107	121	135	149	163	177	191	205	219	233	247	261	275	289
Birmingham	Matt	2	Y	New	y	y	85	99	113	127	141	155	169	183	197	211	225	239	253	267	281	295	309
Birmingham	Dave	2	N	New	y	y	7	21	35	49	63	77	91	105	119	133	147	161	175	189	203	217	231
Birmingham	Steve	2	Y	New	y	y	48	62	76	90	104	118	132	146	160	174	188	202	216	230	244	258	272
Birmingham	Jim	2	Y	New	y	y	9	23	37	51	65	79	93	107	121	135	149	163	177	191	205	219	233
Birmingham	Dave	2	N	New	y	y	8	22	36	50	64	78	92	106	120	134	148	162	176	190	204	218	232
Birmingham	Matt	2	N	New	n	n	5	19	33	47	61	75	89	103	117	131	145	159	173	187	201	215	229
Bristol	Steve	3	Y	New	n	n	12	26	40	54	68	82	96	110	124	138	152	166	180	194	208	222	236
Bristol	Steve	4	Y	New	n	n	13	27	41	55	69	83	97	111	125	139	153	167	181	195	209	223	237
Bristol	Jim	5	N	New	n	n	1	15	29	43	57	71	85	99	113	127	141	155	169	183	197	211	225
Bristol	Dave	1	N	New	n	n	25	39	53	67	81	95	109	123	137	151	165	179	193	207	221	235	249
Bristol	Matt	2	N	Used	n	n	67	81	95	109	123	137	151	165	179	193	207	221	235	249	263	277	291
Bristol	Matt	3	N	Used	y	y	98	112	126	140	154	168	182	196	210	224	238	252	266	280	294	308	322
Bristol	Matt	3	Y	Used	y	y	8	22	36	50	64	78	92	106	120	134	148	162	176	190	204	218	232
Bristol	Matt	5	Y	Used	y	y	9	23	37	51	65	79	93	107	121	135	149	163	177	191	205	219	233
Bristol	Matt	4	Y	Used	y	y	7	21	35	49	63	77	91	105	119	133	147	161	175	189	203	217	231

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have a table which is exported daily.

Assuming that table is on the left hand (or only) worksheet in the workbook, try this.

Sub Make_Sheets()
  Dim aCols As Variant, itm As Variant
  Dim c As Long
  
  aCols = Split("Bonus Front Back")
  Application.ScreenUpdating = False
  For Each itm In aCols
    Sheets(1).Copy After:=Sheets(Sheets.Count)
    With Sheets(Sheets.Count)
      For c = .Cells(1, Columns.Count).End(xlToLeft).Column To 8 Step -1
        If .Cells(1, c).Value <> itm Then Columns(c).Delete
      Next c
      .Columns("F").Delete
      .Columns("C:D").Delete
      .Name = itm
    End With
  Next itm
  Application.ScreenUpdating = True
End Sub

 

[thedeadzeds]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


Assuming that table is on the left hand (or only) worksheet in the workbook, try this.

Sub Make_Sheets()
  Dim aCols As Variant, itm As Variant
  Dim c As Long
 
  aCols = Split("Bonus Front Back")
  Application.ScreenUpdating = False
  For Each itm In aCols
    Sheets(1).Copy After:=Sheets(Sheets.Count)
    With Sheets(Sheets.Count)
      For c = .Cells(1, Columns.Count).End(xlToLeft).Column To 8 Step -1
        If .Cells(1, c).Value <> itm Then Columns(c).Delete
      Next c
      .Columns("F").Delete
      .Columns("C:D").Delete
      .Name = itm
    End With
  Next itm
  Application.ScreenUpdating = True
End Sub

Thank you for this. This works great however i apoligse as my explantion was poor. The 3 tabs would already exisit as this data would need to be overwritten each day and pasted as Values. Also, Site would need to be in column A, Name in column B, Type in column C, Sale in Column D. I would also then have formulas in columns E, F, G H, I so the data would need to start at column J.

 

[Peter_SSs]

Thanks for updating your profile.

Since you have 365, would you consider a formula approach. If the data on that main export sheet is over-written each time, all you would need, as I understand it, is two formulas in two individual cells in each of the 'Bonus', 'Front' and 'Back' sheets. Here is the one for 'Back' using the sample data from post 1 on a sheet called 'Data'. You can have the formulas you mentioned already in columns E:I.
You may need to adjust the ranges in my formulas as I have allowed for possible data in columns up to AZ and rows to 1,000.
All you would need to do is make the obvious adjustment in the J1 formula on the other two sheets.

thedeadzeds.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Site	Name	Type	Sale						Back	Back	Back	Back	Back	Back
2	Cardiff	Steve	New	n						164	178	192	206	220	234
3	Cardiff	Jim	Used	n						165	179	193	207	221	235
4	Cardiff	Dave	Used	n						169	183	197	211	225	239
5	Cardiff	Steve	Used	n						179	193	207	221	235	249
6	Cardiff	Jim	Used	n						196	210	224	238	252	266
7	Cardiff	Dave	Used	y						206	220	234	248	262	276
8	Cardiff	Matt	Used	y						159	173	187	201	215	229
9	Cardiff	Dave	Used	y						208	222	236	250	264	278
10	Cardiff	Matt	Used	y						166	180	194	208	222	236
11	Birmingham	Steve	Used	y						166	180	194	208	222	236
12	Birmingham	Jim	New	y						157	171	185	199	213	227
13	Birmingham	Dave	New	n						219	233	247	261	275	289
14	Birmingham	Matt	New	y						239	253	267	281	295	309
15	Birmingham	Dave	New	y						161	175	189	203	217	231
16	Birmingham	Steve	New	y						202	216	230	244	258	272
17	Birmingham	Jim	New	y						163	177	191	205	219	233
18	Birmingham	Dave	New	y						162	176	190	204	218	232
19	Birmingham	Matt	New	n						159	173	187	201	215	229
20	Bristol	Steve	New	n						166	180	194	208	222	236
21	Bristol	Steve	New	n						167	181	195	209	223	237
22	Bristol	Jim	New	n						155	169	183	197	211	225
23	Bristol	Dave	New	n						179	193	207	221	235	249
24	Bristol	Matt	Used	n						221	235	249	263	277	291
25	Bristol	Matt	Used	y						252	266	280	294	308	322
26	Bristol	Matt	Used	y						162	176	190	204	218	232
27	Bristol	Matt	Used	y						163	177	191	205	219	233
28	Bristol	Matt	Used	y						161	175	189	203	217	231
29
Back

Cell Formulas
Range	Formula
A1:D28	A1	=FILTER(FILTER(Data!A1:G1000,Data!A1:A1000<>"",""),ISNUMBER(SEARCH(Data!A1:G1,"SiteNameTypeSale")))
J1:O28	J1	=FILTER(FILTER(Data!H1:AZ1000,Data!A1:A1000<>"",""),Data!H1:AZ1="Back","")
Dynamic array formulas.

 

[thedeadzeds]

Thank you again, the only issue i may have with that is that in the orignal export, the same named columns are not always next to each other, for example, 'Back' could be in columns E, U, I, T, U one day and then G, I, O, X the other. Sorry about this

 

[Peter_SSs]

the only issue i may have with that is that in the orignal export, the same named columns are not always next to each other, for example, 'Back' could be in columns E, U, I, T, U one day and then G, I, O, X the other.

The order/position of the Back/Front/Bonus columns should make no difference, the FILTER function should find them. The only requirements with the current formulas are that:
- those Back/Front/Bonus columns do not come before column H, but it looks to me like the first 7 columns are fixed anyway?
- the columns do not extend beyond AZ

BTW, if testing the formulas, make sure you are using what is now showing in post #4 as I did edit them to make them simpler and you may have seen the original ones and not the latest ones.

 

[thedeadzeds]

ah I see, this is amazing. Thank you so much

 

[thedeadzeds]

Quick question, how would i make this an array? So rather than so filter for Back, filter for column header Back and column header FrontBonus? Ive tried using the insumber but unforatnely one of the column names is 'Bonus' so its also pulling that through

=FILTER(FILTER(Data!H1:AZ1000,Data!A1:A1000<>"",""),(Data!H1:AZ1="Back")*(Data!H1:AZ1="Front Bonus"),"")

=FILTER(FILTER(Data!H1:AZ1000,Data!A1:A1000<>"",""),Data!H1:AZ1="Back","")

 

[Peter_SSs]

Unclear what you are actually asking. Also you have used "FrontBonus" and "Front Bonus" so I don't know which it is or whether both exist.
Are you wanting 'Back' and 'Front Bonus' on the same sheet? If so, it would be something like

=FILTER(FILTER(Data!H1:AZ1000,Data!A1:A1000<>"",""),(Data!H1:AZ1="Back")+(Data!H1:AZ1="Front Bonus"),"")

 

[thedeadzeds]

This is perfect, thank you very much for all your help. Its saved me a load of time