Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
andrum94 24 ani
 | Femeie
24 ani
Galati
cauta Barbat
27 - 80 ani |
|
|
TRaP
Moderator
Inregistrat: acum 8 ani
Postari: 951
|
|
I want to extract a list of unique accounts which have mapped certain categories of data, even for sheets not yet added to my file (but planned to be in the coming months).
The one below works. It can surely be improved more.
- sheets variable contains my sheet names, currently only first 2 items of that array exist in my file.
- rngM_str contains the account numbers to return
- rngN_str contains the category mapping per account
- A13 contains the current category
=LET(
sheets; {"1-3";"4-6";"7-9";"10-12"};
combined; REDUCE(""; sheets; LAMBDA(acc;sh;
LET(
rngM_str; "'" & sh & "'!$M$2:$M$100";
rngN_str; "'" & sh & "'!$N$2:$N$100";
sheetExists; ISREF(INDIRECT(rngM_str));
qData; IF(sheetExists; IFERROR(FILTER(INDIRECT(rngM_str); INDIRECT(rngN_str) = A13; ""); ""); "");
VSTACK(acc; qData)
))
);
TEXTJOIN(", "; TRUE; SORT(UNIQUE(combined)))
) |
Source: Gemini
|
|
| pus acum 2 saptamani |
|
|
TRaP
Moderator
Inregistrat: acum 8 ani
Postari: 951
|
|
My original approach was to update the formula below once new sheets are added. Simply copy/paste arr_Q2 formula, change 4-6 to 7-9 and done.
If I add formulas for missing sheets, upon formula executing (Enter) a Browse dialog opens, obviously, to locate the missing sheet.
Maybe the 2 can be combined by using ISREF.
The formula may be further simplified by adding the ranges $M$2:$M$100 and $N$2:$N$100 to variables as well.
=LET(
arr_Q1; IFERROR(FILTER('1-3'!$M$2:$M$100; '1-3'!$N$2:$N$100 = A13; ""); "");
arr_Q2; IFERROR(FILTER('4-6'!$M$2:$M$100; '4-6'!$N$2:$N$100 = A13; ""); "");
arr_Q3; "";
arr_Q4; "";
TEXTJOIN(", "; TRUE; SORT(UNIQUE(VSTACK(arr_Q1; arr_Q2; arr_Q3; arr_Q4))))
) |
|
|
| pus acum 2 saptamani |
|