Forum Discussion
EXCEL FORMULA ERROR
mtarler Yes! thats what is needed.
Does that include if Jane Doe moved from the current location, to perhaps the bottom, the formula would still work, as it's searching for the name?
If I recall correctly, right now it is using the UNIQUE of the name list as the list to output (the first column in the output) but that could be replaced with a different source if you need.
SmitLaur930 So here is the formula I created and a quick break down:
=LET(in, A8:F21, names, TAKE(WRAPROWS(TAKE(in,,1),2),,1), UniqNames,UNIQUE(names), data, WRAPROWS(TOROW(DROP(in,,1)),(COLUMNS(in)-1)*2), out,BYROW(UniqNames,LAMBDA(r,SUM(--ISNUMBER(SEARCH("bn",TOROW(FILTER(data,names=r,""))))))), HSTACK(UniqNames,out))So line 1 is the range for the input data
line 2 takes the 1st column of the input range makes into 2 columns so that the 1st column is all names and the 2nd column is all the titles and then takes just the 1st column (names)
line 3 gets the unique list of names from line 2. so if the same person is in the list multiple times it will combine those results. you could easily replace the UNIQUE(names) with some other list if you only wanted the results from certain people
line 4 takes the data and similar to line 2 re-arranges it so every 2 lines of data are made into 1 line so that data lines up with the list of names from line 2
line 5 goes through each name in UniqNames (line 3) and finds the desired result
line 6 formats the output
- Okay thank you! So using UNIQUE to help keep the names searchable?