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 25, 2023
The cells match in text (ie, capital letters, spaces etc.)
There is a list of staff, it has staff name on row 1 and then their job title on row 2.

There is a top sheet that looks at the monthly calendar and tallies up the following (bn for bookings, holidays) this then is translated into a capacity %.

I have a sheet already that does this and is working, but when a staff member leaves/begins currently, I'm manually adding them in and changing the formulas, which takes time, so wanted to add a formula in that would look for the staff members name regardless of where they are on the sheet.
mtarler's avatar
mtarler
Silver Contributor
Oct 25, 2023

SmitLaur930  I'm having some trouble knowing exactly the structure and what you need.  My best guess is the following and attached.  

on the left is a table with name, title on successive rows and the corresponding 2 rows of data.

based on that you are trying to count cases of *bn* in that data.  I created a compound formula on the right that outputs names and counts.

Am I even close to what you need?  Can you create a better sample sheet instead?

  • 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

     

  • 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
    yes.
    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's avatar
    SmitLaur930
    Copper Contributor
    Oct 31, 2023

    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? 

Resources