Forum Discussion

dfhidromod's avatar
dfhidromod
Copper Contributor
Nov 30, 2020
Solved

Create a new table selecting info from an existing table in Excel vba

Hi everyone!

I am trying to make a nice table (sheet 2) out of different information about a company from a bigger table (sheet1). The info is in different language and has more sections than needed to create the new table (intended to create a nice CV). 

The goal is to create a VBA that automatized this to gain time. 
I am so lost with this because I do not know anything about VBA, so I was hoping someone could help/guide me. 

 

I put an example of what I would like to create. So basically all the different projects are on sheet 1 with all the different sections (=columns) per project. On sheet 2, there is the table I would like to be able to create with a VBA.

Let me know if you are able to help me, I would be so grateful!!!

 

Cheers,

 

Déborah

excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Dec 11, 2020

    dfhidromod 

    The major problem: you changed cmdCancel_Click to cmdCancel_Click3 and cmdOK_Click to cmdOK_Click3. That won't work: Click is the name of the event, you can't just change that.

    Other problems: the headers are now in row 1 instead of row 2, and your column offsets for the context and activity were off by 1.

    See the attached version.

13 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 01, 2020

    dfhidromod  It is possible to do much of this with worksheet formulas.  I have use Excel 365 to put the entire calculation into a single formula in cell F1.

    = LET(
\0, "Generate indices",
    N, COUNTIF(Project[Working Area],Criterion),
    k, SEQUENCE(8*N,1,0),
    recordOffset, QUOTIENT(k,8),
    fieldOffset, MOD(k,8),
\1, "Filter fields and records to extract relevant data",
    headers, FILTER(Project[#Headers],columnFilter),
    selectedFields, FILTER(Project, columnFilter),
    filteredKey,  FILTER(Project[Projeto], Project[Working Area]=Criterion),
    filteredTable, FILTER(selectedFields, Project[Working Area]=Criterion),
\2, "Assemble report recombining project key and selected data",
    reportKey, INDEX(filteredKey, 1+recordOffset),
    reportHeader, IF(fieldOffset, INDEX(headers, 1+fieldOffset),  reportKey),
    reportData, INDEX(filteredTable, 1+recordOffset, 1+fieldOffset),
    report, CHOOSE({1,2}, reportHeader, reportData),
    report )

    • dfhidromod's avatar
      dfhidromod
      Copper Contributor
      Dec 09, 2020

      PeterBartholomew1 

      Dear Peter,

       

      thanks a lot for your help! I see in your file that you got the exact result I wanted. 

      However, when I try to redo it, it does not work.

      So the pink table should select and filter the projects according to their working area, right?

      So for this, you used the formula "= SORT(UNIQUE(Project[Working Area]))" but when I do it, it does not want to link both sheets: the one you named DataTable and Report. 

       

      And then on the left side of that same worksheet "Report", you have "N=" in one cell and then "=COUNTIF(Project[Working Area],Criterion)" which does not work either when I try it on my own file. 

       

      Do you think you know what is the problem here? and if yes, would you mind explaining it to me?

       

      Thanks a lot for your file again, when it will work, it will simplify and automatize so much my work!! 🙂

       

      Regards

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Dec 10, 2020

        dfhidromod 

        Is your table named 'Project' or is it named as default 'Table1' (or the equivalent in Portuguese).

        For the filtering to work you need to be using Excel 365.  Otherwise the formulas get very messy.

    • dfhidromod's avatar
      dfhidromod
      Copper Contributor
      Dec 10, 2020

      HansVogelaar 

      Dear Hans,

       

      what a script! Thanks a lot!

      However there are things I do not understand and I am having a hard time to reproduce the code on the real document.

      In these lines:

      <For r = 3 To m
      For i = 1 To n
      If v(r, 13) = arrSelected(i) Then> 

       

      1. Why did you choose 3 for r =3?
      2. and also, the v(r,13)= arrSelected(i), what does it mean? why 13?
      3. What do "A" and "B" stand for? because there is nothing in Columns A and B, so I am guessing it is not referring to the columns. But what is it then?

      I know you've already helped immensly, and I am novice in this language, but would appreciate if you took the time to answer me again 🙂

       

      Regards,

       

      Déborah

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Dec 10, 2020

        dfhidromod 

        1. Because the data on Sheet1 start on row 3 (row 1 is hidden and row 2 contains headers).
        2. v is an array that contains the values of the cells in columns A to Y, row 1 to m (the last used row). v(r, 13) is the array element in row r and column 13. Column number 13 corresponds to column M, the Working Area column.
        3. A and B are the columns on the new sheet that are filled by the code.

Resources