1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by stefanoste78, Jul 15, 2018.

  1. stefanoste78

    stefanoste78 Member

    Messages:
    128
    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
  2. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    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 …
  3. stefanoste78

    stefanoste78 Member

    Messages:
    128
    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

    Attached Files:

  4. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    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 …
  5. stefanoste78

    stefanoste78 Member

    Messages:
    128
    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-pr...-headings-in-excel-between-many-files-that-ca. html
    I can not alone realize the macro ..
    Last edited by a moderator: Jul 16, 2018
  6. Marc L

    Marc L Excel Ninja

    Messages:
    4,258

    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.
  7. stefanoste78

    stefanoste78 Member

    Messages:
    128
    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 ...
  8. Marc L

    Marc L Excel Ninja

    Messages:
    4,258

    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 …​
  9. stefanoste78

    stefanoste78 Member

    Messages:
    128
    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?
  10. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    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.
  11. stefanoste78

    stefanoste78 Member

    Messages:
    128
    I found a macro that extracts the table titles but between sheets of the same file

    Attached Files:

  12. stefanoste78

    stefanoste78 Member

    Messages:
    128
    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?
  13. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    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 !
  14. stefanoste78

    stefanoste78 Member

    Messages:
    128
    So now you're telling me that s impossible to do?
  15. Marc L

    Marc L Excel Ninja

    Messages:
    4,258
    No, just well read again …

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

    stefanoste78 Member

    Messages:
    128
    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
  17. Marc L

    Marc L Excel Ninja

    Messages:
    4,258

    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 ?!
  18. stefanoste78

    stefanoste78 Member

    Messages:
    128
    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: Jul 18, 2018
  19. Marc L

    Marc L Excel Ninja

    Messages:
    4,258

    So first try manually for few files with differents headers
    to check if your logic can match your need …​
  20. stefanoste78

    stefanoste78 Member

    Messages:
    128
    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 ..

Share This Page