Forum Discussion

SmitLaur930's avatar
SmitLaur930
Copper Contributor
Oct 24, 2023

EXCEL FORMULA ERROR

Hi All,  I'm looking to create a formula that searches for codes across various departments and collates them together on a top level sheet. This will be an easier way of showing the data to snr m...
excel
Formulas and Functions
SmitLaur930's avatar
SmitLaur930
Copper Contributor
Oct 31, 2023
Okay thank you! So using UNIQUE to help keep the names searchable?
mtarler's avatar
mtarler
Silver Contributor
Oct 31, 2023

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

 

Resources