Allocation of columns to other sheet based on matching headers

[etaver87]

In the attached Excel file i'm trying to figure out how to allocate columns of the sheet "InfoPath Import" to the correct columns in the sheet "Final Layout".

The problem is, that when I import data from the InfoPath forms, there are not always the same number of column header which makes the allocation to sheet "Final Layout" harder. The header names are the same, but not their positions.

Is there maybe a way to only always load/import certain columns at the same positions? If not, how could I accomplish the allocation with a macro?

Many many thanks for your help and support!

Best regards,

[oeldere]

Although I can't help you enough with your question in VBA; you show no results so forummember maybe will not know what you want to achieve. That could be the reason you have no respond on the question (41 views).

[IonutC]

Hi,

I had this situation before and i was not able to find the right answer. My solution however was to manualy add the columns to my destination.
So if you know that in the source file col. A has to be inserted into col. G in the destination file you can do a macro that will copy all your columns based on a specified position.

Please find below an example of a code:

Please Login or Register  to view this content.

you can create also a variable that will calculate your lastrow so you can do a Foor Loop function:

the code can be

Please Login or Register  to view this content.

B in my example is a function in which you can assign a value. in the format "cells( B, 15) B repents the row and 15 the column.
Also you will need to have no blank cells into your columns...

Hope that it helps.

[etaver87]

Thanks a lot for this first step. the problem is, that it is not always the same column from sheet "InfoPath Import" that corresponds to a certain column in the "Final Layout". I think it would be best to match the headers from sheet "InfoPath Import" with the ones from "Final Layout" That way I would have the right data below each header in the sheet "Final Layout".

I updated the template upload from my first post.

So basically, Excel should check from the InfoPath Import sheets (in the final version it will be just one sheet where I import the data which then gets loaded into the "Final Layout" sheet) the headers and copy to the columns in "Final Layout" with the same header. The thing is that after the imports, the headers are not always in the same column, so therefore there is the need for matching headers.

[IonutC]

Hi,

I can't help you further. I had an idea of having a dynamic area, but I don;t know how to write the code so you can find the specified column.

Maybe we can see, if other reply to your question and to learn both of us the solution.

Have a great day ahead,
Ionut

[Marc L]

Hi !

If headers are exactly the same, no matter their position with an advanced filter …

[Keebellah]

Hi, if you start working with non consistent column names then manual checking is the only option.
I have reattached your file but added a worksheet named Layout-Sheet
Column A o=contains the requested output layout and column B the corresponding column number; A=1, B=2 etc.

In Column C I placed the top row of your input file.
In Column D you could place the corresponding column number of the input file or you can place the column number of where you want it to be placed in the output sheet

You would the need vba code to read each row and check win which column each value has to be placed.

This is just a quick-and-dirty explanation but it's just to give you my idea and something to brood upon.

If you have more source layouts then you would have to do the same for each and maybe it can work.

See if you can work something out, I'd gladly help and stick time into it if you need more help.

[etaver87]

Thanks so much for your time, really appreciated!

Have you seen the newest template I uploaded in the first post? What if the headers are the same? Then it should be easier, no? That would be a solution too.

[etaver87]

I saw something similar in another workbook, where in "LeaveDates" we have the first row with date headers (not every date of the year) and the "AllDates" sheet has all dates of the year in the headers. The following macro then filled only those columns in "AllDates" that matched with the headers in "LeaveDates":

Please Login or Register  to view this content.

maybe that helps someone or you Keebellah find the solution?

[6StringJazzer]

etaver87, duplicate posts are a rule violation. If you need to clarify your question, please do so in the existing thread instead of starting again in a new thread. This is confusing to the people who have been trying to help in the original thread. Rather than try to merge the threads and cause even more confusion I am going to close this one, referring members to your newer thread.

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.