Matching between two workbooks

[DV07]

Hey guys, I have (what i believe to be) a very simple issue, but cannot seem to figure it out.

I have two workbooks, each with multiple sheets:

What I would like to know how to do is,

in wbk1 i have sheetx and in wbk2 i have sheety

within each, i have a table

what i would like to do is state that if the individual columns of the headings of each sheet match, copy and paste the data, if not, then don't do anything.

I feel it can be done with a simple hlookup/index/match however can't quite seem to figure it out.

thanks

D

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

It's not at all clear what you mean. You mention matching column headings. Are you saying that for instance the A column heading in one worksheet is the same as the D column heading in the other?
And if so then what? Copy which Data to where and in what format?

[DV07]

Apologies for not making myself clear. I am uploading two excel workbooks within this post to explain myself
one is called "forum upload pivot table"
the other is called "forum upload headings"

If we look at the file "forum upload pivot table", the enclosed pivot table has three headings , being row labels, sum of population, count of year
if we look at the file "forum upload headings", the headers are, row levels, sum of population, precipitation, count of year

I would like to generate a formula which will match the two headers, if they match, it will extract the data from "forum upload pivot table" and paste it under the corresponding heading in "forum upload headers"

therefore if the headers "row labels" match, if will get the data "England, NI, Scotland, Wales" from the pivot table, and paste them under the header "Row labels" within "forum upload headers".

If there is no match, (header precipitation does not exist in both the files, only in one, therefore no match) it will populate the column with 0

kind regards

Drij

[Richard Buttrey]

Is this not ovecomplicating what you want?

It seems to me that the column labels are largely irrelevant. As I understand it the requirement is to grab the data that generates the Pivot Table and paste it in the other workbook.

Admittedly there's an extra precipitation column which is in the middle of the columns that do match, but is there any reason why this can't be the last column.
In the example you show the PT data doesn't contain a precipitation column so it will always be zero anyway.

Clearly this is a trivial example and doesn't really give a sense of your production workbooks. For instance your PT data is the same size as the PT, in the real world the data would be lots of rows which are summarised into a 4 row PT.
I sense you might be implying that column labels change from time to time and if so that's a whole new dimension to the request.


Would you therefore upload truly representative copies of your workbooks and show a before and after situation with manually calculated results.

[DV07]

So yes I agree this is an oversimplification, however in the actual productionised data, there are multiple columns in the middle which are missing, hence why the match with the specific pivot table headers is vital

[Richard Buttrey]

Sorry I don't uderstand

Two of the columns are the same and you say there are many others. Why can't the order of the columns be changed so that the two that are common are the first two.

Is it the case that the two column headers are always the same?

If the order of the columns can't be changed then all the PT Data could first be copied over and then the columns sorted left to right to the required order.

Back to Basics, Is the intention to have all the PT data in the the other workbook but with the column order different?

I still don't know the answer to whether the column labels change from time to time or whether they are static.


One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data. Absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result.
You must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data).

[DV07]

Right I will attempt to make myself clearer. I appreciate you are trying to help and it is difficult without the data, so will try again with improved data, something which is more representative of the data I work with.

If we look at the PT data, we can see the pivot table is derrived from the data to the left. The pivot table is also filtered to show certain data. The columns of the Pivot tables are dates. Within this PT, the headers have the dates for data sourced on the 07/04/2019, 21/04/2019, 28/04/2019.

If we now look at the headers data, this is the master document from which a report is generated.
as we can see, it includes a heading for 14/04/2019- something which is missed from the pivot table data (this can be due to the fact that data may not have come in for this date).

From the master, the headers cannot be changed/altered/deleted/have their order changed. They represent data for a week by week basis. I would like a dynamic connection between the PT data and the headers data. If there is a match in the headers (ie- if the dates match) then insert the PT data into the appropriate header within the headers data. If they do not match (ie a date is missed out in the PT data, like 14/04/2019 has been, then insert 0s all the way down.

I appreciate why one may think just do a simple manual copy and paste to insert the new columns on a weekly basis, however, data for past dates can change depending on changes that take place in new dates, eg- the data within column 07/04/2019 can change when data from 28/04/2019 is inserted into the pivot table, hence why i wish for a dynamic link.

[Richard Buttrey]

Put the following macro in the workbook into which you want the PT data placed

Please Login or Register  to view this content.

Note it assumes the PT file name doesn't change - here it is "forum upload pivot table .xlsx" which you'll see in the macro.
It also assumes that you have already opened that file in memory.

If it does change name every time then a couple of lines of code will be needed at the start to use the GetOpenFilename instruction so that the user can select the file which the macro will then open in memory.

It also assumes the column labels start in