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.
|
Nou pe simpatie:
oana89
 | Femeie
25 ani
Bucuresti
cauta Barbat
25 - 46 ani |
|
|
Mrrrr's Forum (VIEW ONLY)ReguliInregistrareLoginPozeNu sunteti logat. Lista Forumurilor Pe Tematici
|
| pus acum 1 luna#1 |
TRaPModerator
Postari: 960
|
|
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 1 luna#2 |
TRaPModerator
Postari: 960
|
|
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))))
) |
|
|
| |
|
