I have a list of children that belong to an employee. I need to add a rank or count that restarts every time you change to the next employee. This will allow me to separate them out and pull all of child #1 to a column with an xlookup and then all of child # 2 to the next column. Moving children from rows to columns. If I can get this formula I can save hours of work for myself and my team.
Thanks for reaching us. I understand that you want to add a rank or count that restarts every time you change to the next employee. For that, I used a similar dataset to yours and inserted the following formula in Cell C2 and dragged the Fill Handle icon to copy the formula in the remaining cells.
=COUNTIF($A$2:A2,A2)
You also wanted to separate and pull out all children of similar rank to separate columns. Although you wanted to use the XLOOKUP formula, we can achieve a similar result by using the FILTER function. For example, we can insert the following formula in Cell D2 to pull all Child#1 in Column D.
=FILTER(B2:B9,C2:C9=1)
For other ranks of children, we can apply similar formula. However, the FILTER function is available in Excel 365. So, if you are not using Excel 365, then you can apply an alternative formula that combines the TEXTSPLIT, TEXTJOIN, and IF functions. For instance, we can apply the formula below to extract all Child#2 in Column E.
