Nov 30 2020 07:31 AM
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
Nov 30 2020 10:27 AM
Dec 01 2020 10:22 AM
@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 )
Dec 09 2020 01:28 AM
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
Dec 09 2020 04:01 PM
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.
Dec 10 2020 09:13 AM
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>
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
Dec 10 2020 10:02 AM
Dec 11 2020 03:11 AM
Okay thanks for the explanations!
And one more thing, in this line <w.Range("B" & t).Value = v(r, 4 + s)> what does the 4+ s stands for?
And at the end in this line: <w.Range("A1:B1" & t).Borders....etc> the A1 and B1 correspond to the new sheet too? I am having troubles identifying where you set that the code opens a new sheet for these variables.
Cheers!
Dec 11 2020 03:48 AM
The line
Set w = Worksheets.Add(After:=Worksheets(Worksheets.Count))
creates a new worksheet and assigns it to the variable w, so in the rest of the code, we can use w to refer to the new sheet.
The variable s contains a number representing the selected language: 1 for Portuguese, 2 for English etc.
I see now that 4 + s should have been 3 + s. This is the column number containing the titles in that language: column 4 (D) for Portuguese, 5 (E) for English etc.
I will attach a corrected workbook.
In w.Range("A1:B" & t).Borders, w is the new sheet, so this line refers to columns A and B on the new sheet.
Dec 11 2020 04:57 AM
Okay! Starting to understand better! This code is really impressive and cool.
I tried it on my new worksheet (because I had to add things) (and adapting the code to it), but when I select the languages, nothing happens. I don't even get an error message. There is just nothing.
In the file attached to this message, there are the headers I am using and their positions in the worksheet. I thought I changed everything I needed to make it work, but does not seem to give me results.
Do you think you could have a look at the changes I made to your code and identify what I've missed?
I marked all the changes in green with this " ' "
Cheers!
Dec 11 2020 05:59 AM
SolutionThe 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.
Dec 18 2020 03:58 AM
okay, I see! thanks for the explanation!
Another last thing, do you know how to define the width of the 2nd column in the new sheet created?
Because, i get super wide columns and the text does not fit in it.
Attached, there is a picture illustrating better what I'm trying to explain.
All the best,
Déborah
Dec 18 2020 04:24 AM
Hopefully, this works better.
Mar 11 2021 02:38 AM
Dec 11 2020 05:59 AM
SolutionThe 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.