Merge / consolidate around 17 to 18 sheets with different colum header names

[deepa.iyer]

I need help to merger 18 sheets of data ,all have the same kind of data only catch is that every sheet has a different header nomenclature

Eg i need data from 8 fields like
MMS id
Client group
SG
Client name
Opp Name
Market
Market unit
Submission data
Revenue
Lead SA
Support SA
NC parameter

The above would be my target headers . But in the 18 sheets i have headers are as below

ID - or Mm id
CG
sG
Master client name
MU
Markets
Proposal submission date or assignment date or deal date
Lead SAs
Support SAs or supporting sAs
Total revenue or ta+ sc revenue $m

I need the bna code to search the header n copy these columns and paste as per my target sheet header column and add the sheet name under NC parameter column


Any VBA code would be of great help in reducing manual work . Appreciate your help in this regards.

Thanks in advance

[JohnTopley]

Please see the yellow banner at top the page on how to attach a sample workbook. Ensure you add a sample of expected results (output).

[deepa.iyer]

Attached sample file .

[deepa.iyer]

Any help in this highly appreciated

[Marc L]

(removed after review)

[Marc L]

Rather than hardcoding anything you will have to amend yourself within the code each time necessary the better is to
add a 'Settings' worksheet between worksheets 'Master Template" & 'AA' in order to create an headers table conversion :
first copy the 'Master Template' headers to 'Settings' first row.
Then for each worksheet from 'AA' to the last, check each header name with the 'Settings' first row and
when a header does not exists in 'Settings' just add it in 'Settings' in its relative column (but avoid any duplicate)
so it should be easy in the VBA procedure to copy the data according to the 'Settings' conversion table.

Once you have done the necessary, attach the updated workbook
with data and expected result accordingly (far better than any guessing challenge !)
and post you have done the job in order some helper could give it a try …

But obviously with smart worksheets with exact same headers than the source
just using a kid level Excel basics advanced filter should need less than ten codelines !

[JohnTopley]

Following from Marc's suggestion attached has sheet "Settings" for only "AA" and I could not complete this!

You cannot expect contributors to sort out the various headings in multiple sheets: your own headings above are incomplete!

[Marc L]

In fact no need to fill 'Sheet name' column in 'Settings' worksheet as it does not exist in source worksheets …

[JohnTopley]

You need "sheet name" so you can "lookup" the parameters [for a given sheet] needed to do the matching/conversion.

[Marc L]

Very not as 'lookup' according to sheet name is useless with my way so less work to achieve in 'Settings' worksheet
as if a source header name does not exactly match the destination headers so this source header must be added
in the relative column of the destination headers within 'Settings' if it's not a duplicate within this column.

As a reminder it's just a headers conversion table and not a conversion table according to worksheet name …

[Marc L]

I also must know under which OS ? Without any information I won't waste time …

[Marc L]

The 'Settings' worksheet could also be located before the master worksheet and can have a different layout than my post #6 :
column A must contain only the headers not matching the master worksheet and
column B its relative column index according to the master worksheet …

[deepa.iyer]

I am not sure if i am very clear . but i have attached a file with settings tab having column headers and master sheet having one predetermined header . i need to consolidate all this under one sheet by appending the rows of data , basically a looping thru each sheet with n copying the data as per only the required column and pasting in Result sheet

[Marc L]

As your 'Settings' sheet with so many duplicates does very not follow any of my both layouts (posts #6-7 & 12),
as I can't catch the logic if any , as there is no data in the source worksheets neither the expected result accordingly as asked in my post #6
and according my post #11 is still waiting for an answer so hope someone else may see the light …

As a reminder : solution belongs to good enough readers.

[deepa.iyer]

Thanks for your time.i really apprciate it.

[deepa.iyer]

I came up with a Different method but could achieve it for only one sheet , i want this code to loop in all sheets and Change Header , then run the matching data and append in Result Sheet .

The file attached , i copy the data from Sheet 1(Say DAC) and paste in Source Sheet , I have worked on some coding so that it changes the headers in Source sheet by using the Settings as a template reference A:A column and then i run the Matching code for it to pull data only from the required headers.

Now i want this code to loop each sheet paste into source , change header , match columns and Append data in Result sheet.
The attached file code works on only one sheet and there is no copy paste into source sheet in this code .

Could some one pls help me with the looping thru each sheet for copy pasting data into Source sheet , then changing the headers and then pasting the matching data in result sheet , goto next sheet do the same until it find any more sheets .
really appreciate if someone could help on this after i managed to achieved the initial bit .

[JohnTopley]

Try

Please Login or Register  to view this content.

[deepa.iyer]

Thanks for this , I will run it on my Original file and get back to you.

[deepa.iyer]

This works Brilliantly . Thanks for your time and help.

I have one more quick thing on this , can there be a way before the code gets in it does a filter and then copies the data ?/?Could u help incorporate a filter function inside the code, each sheet would need 2 to 3 filters happening and then it pulls data from them

[JohnTopley]

Please post a file which shows your requirement for filters; and if filters vary per sheet then a list per sheet is required.

[deepa.iyer]

I am attaching a test file with filter criteria in same Header filters sheet , i have rename result sheet as Pending Open Items and added another sheet with total Open items .
Total open items is where your earlier code was fetching the matching data .
now i want it to fetch the match data in total , then filter based on header sheet and paste filtered rows in Pending Sheet , by looping thru all sheets
Filter criteria varies in all sheet and i have attached the criteria in the same sheet Header filters below header criteria , also pls note the headers marked on red (i have added in brackets).
Let me know if you can help with this and i havent confused u much .


Also if you see , there is another sheet with Mu SUbsets showing a summary of the data which is formulated , i have a double click even on open pending items in the Open Column of summary sheet . i want to add the double clcik or even a hyperlink even to both total and open columns now.
i dont know how to do it . have a code already in place for open alone in the attached file . Could you also help on this . When i click any row item in the
Total Columns of each country it shud show data set pertaining to that country that Row label a column, thats how the open items works now .
Open items Double click event is linked to Pending open items sheet ,
total items double click event should link to Total items sheet
Is it possible to do?

[deepa.iyer]

Did u get a chance to look into it ? please do let me know

[JohnTopley]

I replied to your PM ..BUT additionally

No data in your posted file and hence no sample output. Headers in "Total Items" do not agree with those in column A of "Headers Filters" ("NC Name" ?)

And for the "Result Sheets" filters: I am not spending time removing column references or separating out the criteria from the header details.

[JohnTopley]

Try attached: no filters for VBDw and VBDp

[deepa.iyer]

This excludes the filters as well right , this is same as the last code ? correct me if i wrong. only the sheet names are different . This works for me with your prev code .
Like you mentioned filters are time consumoing to code and rework i understand .
Could you let me know if the Hyperlink or double click event function works, there is some code for the OPen items double click , but i want it to work even on Total items .(this is exact reason i would wnat the code to populate Pneding sheet with filters and Total items with all data .Not sure if its achievable

[deepa.iyer]

Any luck on this one ?Appreciate any help in this regard