Find columns with the same name and copy to new worksheet

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
356
Office Version
  1. 365
Platform
  1. Windows
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

SiteNameStageResultTypeOrderSaleBonusBonusBonusBonusBonusFrontFrontFrontFrontFrontFrontBackBackBackBackBackBack
CardiffSteve1YNewYn10243852668094108122136150164178192206220234
CardiffJim1NUsedyn11253953678195109123137151165179193207221235
CardiffDave1NUsedyn15294357718599113127141155169183197211225239
CardiffSteve1NUsedyn253953678195109123137151165179193207221235249
CardiffJim4NUsedyn4256708498112126140154168182196210224238252266
CardiffDave3YUsedyy52668094108122136150164178192206220234248262276
CardiffMatt5YUsedny5193347617589103117131145159173187201215229
CardiffDave6YUsedny54688296110124138152166180194208222236250264278
CardiffMatt8NUsedny12264054688296110124138152166180194208222236
BirminghamSteve1NUsedny12264054688296110124138152166180194208222236
BirminghamJim1NNewny3173145597387101115129143157171185199213227
BirminghamDave1YNewyn657993107121135149163177191205219233247261275289
BirminghamMatt2YNewyy8599113127141155169183197211225239253267281295309
BirminghamDave2NNewyy7213549637791105119133147161175189203217231
BirminghamSteve2YNewyy48627690104118132146160174188202216230244258272
BirminghamJim2YNewyy9233751657993107121135149163177191205219233
BirminghamDave2NNewyy8223650647892106120134148162176190204218232
BirminghamMatt2NNewnn5193347617589103117131145159173187201215229
BristolSteve3YNewnn12264054688296110124138152166180194208222236
BristolSteve4YNewnn13274155698397111125139153167181195209223237
BristolJim5NNewnn115294357718599113127141155169183197211225
BristolDave1NNewnn253953678195109123137151165179193207221235249
BristolMatt2NUsednn678195109123137151165179193207221235249263277291
BristolMatt3NUsedyy98112126140154168182196210224238252266280294308322
BristolMatt3YUsedyy8223650647892106120134148162176190204218232
BristolMatt5YUsedyy9233751657993107121135149163177191205219233
BristolMatt4YUsedyy7213549637791105119133147161175189203217231
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

Active Member
Joined
Aug 16, 2011
Messages
356
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows
Thanks for updating your profile. (y)

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
ABCDEFGHIJKLMNOP
1SiteNameTypeSaleBackBackBackBackBackBack
2CardiffSteveNewn164178192206220234
3CardiffJimUsedn165179193207221235
4CardiffDaveUsedn169183197211225239
5CardiffSteveUsedn179193207221235249
6CardiffJimUsedn196210224238252266
7CardiffDaveUsedy206220234248262276
8CardiffMattUsedy159173187201215229
9CardiffDaveUsedy208222236250264278
10CardiffMattUsedy166180194208222236
11BirminghamSteveUsedy166180194208222236
12BirminghamJimNewy157171185199213227
13BirminghamDaveNewn219233247261275289
14BirminghamMattNewy239253267281295309
15BirminghamDaveNewy161175189203217231
16BirminghamSteveNewy202216230244258272
17BirminghamJimNewy163177191205219233
18BirminghamDaveNewy162176190204218232
19BirminghamMattNewn159173187201215229
20BristolSteveNewn166180194208222236
21BristolSteveNewn167181195209223237
22BristolJimNewn155169183197211225
23BristolDaveNewn179193207221235249
24BristolMattUsedn221235249263277291
25BristolMattUsedy252266280294308322
26BristolMattUsedy162176190204218232
27BristolMattUsedy163177191205219233
28BristolMattUsedy161175189203217231
29
Back
Cell Formulas
RangeFormula
A1:D28A1=FILTER(FILTER(Data!A1:G1000,Data!A1:A1000<>"",""),ISNUMBER(SEARCH(Data!A1:G1,"SiteNameTypeSale")))
J1:O28J1=FILTER(FILTER(Data!H1:AZ1000,Data!A1:A1000<>"",""),Data!H1:AZ1="Back","")
Dynamic array formulas.
 
Solution

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
356
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Aug 16, 2011
Messages
356
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
356
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Aug 16, 2011
Messages
356
Office Version
  1. 365
Platform
  1. Windows
This is perfect, thank you very much for all your help. Its saved me a load of time
 

Forum statistics

Threads
1,141,072
Messages
5,704,129
Members
421,328
Latest member
CBL

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top