currently I am working at a Golfclub where I need to sort out an Excel list with hunderets of names and T-Shirt sizes. I need to sort those out between men and women. I was wondering if it is possible to write a formula for each cell to get the needed Data?
This would be the Table where I would get all the Data from:
And in this table in a differenet sheet I need to fill out the following Data somehow (this is the table for men, but it is the same for women):
Note: The tables are in German. In the first table the m=men and w=women.
CodePudding user response：
I suppose you are using Excel 365. If you are using any older Excel version, please let me know so I'll update the answer.
The easiest and clearest way is using the function "FILTER". It allows you to filter a range of data based on the criteria you define.
My formula for the Vorname is: =FILTER($B$2:$B$6,$J$2:$J$6="m")
- Column B is where the original Vorname stands, the range to filter.
- Column J contains the m or w, the condition.
After that, you just need to replicate the formula for the Nachname and for "w".
Just to give the formula for Nachname as well: =FILTER($A$2:$A$6,$J$2:$J$6="m")
Now that we have the filters explained, let's combine it with "if" to select the size of the T-Shirt.
When I replicated your table, the size name "S" filled cell D8. Consequentially, M is E8, L is F8 and so on.
Then on D9, add: =IF(FILTER($G$2:$G$6,$J$2:$J$6="m")=D$8,D$8,"")
- The sizes are located in column G. Therefore, the filter select the size of the correspondent person. Again, this is the table for men.
- The if clause only shows the size if it matches the name of the column (in the example, if size is "S")
The final result for men is the following.
