• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

extract all the column headings of tables present in different files

extract all the column headings of tables present in different files

good morning
You could help me create a macro that extracts the column headers of tables contained in files that are included in a folder.

Example:

file "1" has the headings: a, b, c
file "2" has the headings: d, e

In the "to" column of the file containing the macro, the list of the vertically arranged headers in the same column will be displayed:

column "a"
a
b
c
d
e

Thanks
 

Marc L

Excel Ninja
Hi !

A very beginner way easy to reproduce so with the help
of the Macro Recorder you can get your own code base :
• copy each column headers to the destination worksheet
• use the Excel inner feature Remove Duplicates …
 
I do not think it's a beginner when it comes to different tables, arranged differently and if there are other data besides the table.It seems that the combination of the tables as required in the forum "www.excelforum.com" is not possible if first I do not change the headers making them equal (you could see from the post 19 of that forum). This is why I created this post ...The extraction of the titles would allow me to identify which title repeats the most and which to change to make them homogeneous.

Is this thing possible? according to those who have experience is the best way to go?

In the first table of the merge file you extract the file names and the headings, in the second I delete the duplicates and count how many times the headings are siped (at least some formula I can create :)). In the third individual table the headers to be changed indicating the new title in the cell to the right.


In this way I would later be able to combine the different files with identical headers.

I am attaching an example with only 2 files (the files will be many) ..

Thank you
 

Attachments

Marc L

Excel Ninja
Bad link in your previous post as it is not a thread link,
as links for every thread on every forum are needed
- as you were already warned in your previous thread ‼ -
as you already ask this question to Sandy on another forum and
if you prefer to follow another forum, why creating a thread here ?!

As operating manually is at beginner level …

If some headers have not the same names between worksheets
you just need to create a horizontal headers convert data in a parameter
worksheet where you can match with the source worksheets
via the Find VBA method aka the search Excel feature
like you can see using the Macro Recorder …

If the headers names are not the same but the columns order is respected
then it not needs any convert worksheet ! Just copy data directly …
 
When the request topic is different from the post created, you need to create a new one. The extraction of titles is a different topic for this I thought about creating a new post request for help.
If you want to know why I posted it here first and because here there are many good users with in-depth knowledge. If then I do not find the answer here I will try to find it elsewhere (I often do this).
I have not put the link of the post because I have indicated only for you ...
I have no problem inserting it, it is this:
https://www.excelforum.com/excel-programming-vba-macros/1237549-how-to-combine-multiple-tables-with-different-headings-in-excel-between-many-files-that-ca. html
I can not alone realize the macro ..
 
Last edited by a moderator:

Marc L

Excel Ninja

Even if all forum rules were respected, I won't go that way
'cause operating manually to create the convert worksheet
is faster than creating any code to be used only once !

To see if headers match or not with the result worksheet headers,
I just use the MATCH Excel function
(at very beginner level, to see in Excel help) …
When any header doesn't match, I add it to the convert worksheet
in the column of corresponding result header.
 
This is possible if, as you wrote, the sheets are few ...
It will be difficult for me because the files will be a lot (20000 per state) and it would be impossible manually ...
 

Marc L

Excel Ninja

Even with a code to just list the headers,
you will have to manually create the convert worksheet
then you'll have to mod one of the samples from any VBA forum …​
 
In fact I wrote it down and posted it here earlier to give an idea.
extracting the table headers files that present totally different tables is so easy to do with the macro recorder as you wrote before?
 

Marc L

Excel Ninja
As the Macro Recorder just records what you manually do …
Just operate a few files (3, 4 or 5) for the headers to copy
before using the Remove Duplicates Excel feature.

With the generated code, as a code base, you will have to mod it
according to your analyze of the need.
 
Hi Mark.
I did as you advised me and you can see the result in the file cartel1.
Starting the macro recorded and does not work :(
While pasting the lines I noticed that clicking with the right mouse button on paste does not exit the transposing option from horizontal to vertical (for this I left them that way).
I saw the code but it seems totally different from the normal codes ...
I spent the whole day finding macros that extract table headers and I could not find anything else ..
What should I do now?
 

Marc L

Excel Ninja
Bad idea to transpose, read post #4.

★ ★
So wait in case of anyone has time to waste or
see on some forums their commercial services …

★ ★
For 20 000 workbooks it will last a very very very long long long time
and you should be lucky if Excel won't crash !
 

Marc L

Excel Ninja
No, just well read again …

★ ★
Think about this : once you will have the result, what will you do with it ?
 
Sorry, Mark.

I just realized that the annex (rr file) was not attached as soon as I get home today, I'm gonna show you what happened using the macro recorder.
Later
 

Marc L

Excel Ninja

I do not need it as again you didn't answer to question !

If you have 20 000 files, you will get a worksheet result
with columns on 20 000 rows : what will you do with it ?!
 
placing the titles of headings on a single column "example column a" and in the column accando remove the duplicates, I can count the number of times a title is repeated to understand what to use as a table header (in the table to be created by joining table data of each file ".
I will focus on the headings that interest me as basic headers in the file to be created, and I will replace all the other headers similar to the one I will use.
If I use the tel iteration for the phone number I will search the list of the column that contains the titles of which I removed the duplicates with a formula that finds the word similar by removing the spaces (so I will find the words containing the string " tel "). Once these words have been identified, I will change all the headings in each file in "tel" in order to obtain identical column headings. In this way, having tables with the same information can be used to merge all the tables with the same headings.
It is an indirect process to create a single table that implies a visual evaluation (together with the use of excel formulas) of the titles.
 
Last edited:

Marc L

Excel Ninja

So first try manually for few files with differents headers
to check if your logic can match your need …​
 
yes that is possible .. it will take some time for each state to examine all the headers but on the other hand I will be able to extract automatically from each table the data to create a database of companies

my problem is not the commitment but understand how to reduce the times with the help of macros that I can not create ... If something can not be done with a macro I have to find the alternative to reach the goal anyway. For this I look for alternative roads ..
 
Top