none
Excel MACRO : combine txt file by naming + vlookup RRS feed

  • Question

  • Dears,

    I need to create a n Excel Macro that takes files (txt) from a default folder and then combine them in 2 different sheets.

    TXT files that starts with "name1" has to be combined into sheet A, txt files that starts with "name2" has to combined into sheet B.

    At this point I normalize what I have into each single sheet (and that is the part of the macro that I know).

    Next, I have to do a vlookup + concatenate from the remaining 2 sheets, which in summary I send with the following link: https://fil.email/w49gGaMk

    The sheet RESULT is what I need to get to. That is a sheet B aggregation where Column A values are repeated N times with column B in the next column a different value.

    tks, Buni

    Sunday, May 21, 2017 3:48 PM

All replies

  • I suggest to process the text files with VBA directly and create the result sheet on the fly (without sheet A and B). That is faster and easier.

    Read the whole text file into a string, split by line breaks into an array.

    Create a Scripting.Dictionary from "name1" files as keys with an empty array as item.
    Then process "name2" and add each BCD to the array if ID is found in the dictionary.

    If you need further help please upload a bunch of your text files on an online file hoster like www.dropbox.com and post the download link here.

    Andreas.

    Monday, May 22, 2017 7:16 AM
  • Dear Andreas,

    I appreciate your help, but I prefer a solution where macro keep files.

    what is a Scripting.Dictionary? 

    Buni

    Monday, May 22, 2017 7:22 AM
  • I appreciate your help, but I prefer a solution where macro keep files.

    what is a Scripting.Dictionary? 

    VBA is a macro. Upload some of your text files, I'll show you.

    Andreas.

    Monday, May 22, 2017 10:04 AM
  • Dear Andrea,

    here 2 file : https://fil.email/05orszcH

    all files that start with ABCDE and end with DAT, should go to first sheet of my macro, all files that start with EFGHI and end with DAT, should go to second sheet of my macro.

    files are always "couples", so I will have at least 1 or more of this couple. if macro find 8 files, it should have to put 4 ABCDE files into first sheet and 4 EFGHI files into second sheet.

    tks, Buni

    Wednesday, May 24, 2017 4:39 AM
  • Why the 2 sheets? Do you need them?

    I don't need them, I can create the result sheet directly. Okay?

    Andreas.

    Wednesday, May 24, 2017 7:10 AM
  • here 2 file : https://fil.email/05orszcH

    I've had a little time today and write a code for testing, works, but as your files doesn't have headings, so I don't know where the ID and BCD are in that files.

    I guess the ID is the 1st value in each lineof each file?

    But where is the BCD in the file EFGHI*.DAT?
    I can see 9 numbers in the 1st line, which one is the BCD?

      2553141 255 2017  3 0987659      000000000     1 4022464       0

    Andreas.

    Wednesday, May 24, 2017 1:05 PM
  • Dear Andrea,

    I appreciate but I prefer to have them in 2 different sheets of the same file (macro) because after this I make some jobs on each sheets.

    tks, Buni

    Thursday, May 25, 2017 3:57 AM
  • I appreciate but I prefer to have them in 2 different sheets of the same file (macro) because after

    No problem, but which number is the BCD number in this line from the EFGHI file:

    2553141 255 2017  3 0987659      000000000     1 4022464       0

    Andreas.

    Thursday, May 25, 2017 8:10 AM
  • now I understand.

    I solved this part, about BCD, I found a useful formula.

    but I still miss how to keep similar files and put them into a specific sheet.

    Buni

    Thursday, May 25, 2017 11:47 AM
  • I solved this part, about BCD, I found a useful formula.

    If you don't answer my question I can't help you!

    Andreas.

    Thursday, May 25, 2017 1:25 PM
  • dear Andrea,

    I don't need anymore VBA about BCD. I just need to understand how to keep file with MACRO and put them into two different sheets of the same MACRO.

    Buni

    Thursday, May 25, 2017 3:00 PM
  • I don't need anymore VBA about BCD.

    In that case go into the Data taba and import your file as usual using Get External Data.

    Andreas.

    Friday, May 26, 2017 6:48 AM
  • Dear Andreas, but I don't know how to write this part of VBA.

    Can you help me?

    Tks

    Tuesday, May 30, 2017 4:16 AM
  • Tuesday, May 30, 2017 7:06 AM