-1

First of, sorry for the title, I have no clue what to search for or how to name the question.

I got an example sheet which might already explain everything: https://docs.google.com/spreadsheets/d/1pRhtNaw5mk464lK7QpczFY39taqCFPaiEUUcz1il52E/edit?usp=sharing

In Words:

I have an attendance sheet with each row being a day with the first few (fixed amount) columns being some informations and after a fixed amount of columns every next column only contains a name of a person who attended the event.

This attendance sheet is filled automatically via Google App Script and can be modified.

In another Sheet I have all Persons listed with their Name but additionally also their Nicknames.

My Goal: I want a 1 / 0 Matrix (single row) for each person if his name or one of the nicknames attended.

So If Person A attended Event 1 and Event 3, but with different names (Person A and Nickname A) I want 1 0 1.

Improve this question
10
  • You appear to be trying to show that you actually have three separate sheets with data in them. But in the linked sample spreadsheet, you've just stacked a bunch of data on top of one another in one sheet. This makes writing a formula inefficient, both for the volunteer contributors here and, later, for you in trying to adjust it to your real setup. I recommend that you edit your sheet to actually contain three sheets, name them according to your real sheet names, and move the sample data into the corresponding sheet. Then report back here.
    – Erik Tyler
    Nov 16, 2021 at 19:04
  • It is also unclear in your matrix whether rows are event dates and columns are people, or vice versa, because you have no headers or labels around the matrix. And it is further unclear where (in which sheet) you would want such a matrix placed. Again, please construct the sample sheet to realistically reflect the setup and data in your real-world spreadsheet, and be clear about where the results should go as well as what any headers or left-side labels should be.
    – Erik Tyler
    Nov 16, 2021 at 19:05
  • As far as I know it does not matther which way it is because I can simply transpose it and it's the way I want it I actually have two Sheets, one is called Attendance Sheet, the other one is called Person Sheet and the "third" was called "Wanted Person Sheet" And since I displayed the matrix right behind the name it should be obvious which is which, the row is the person and the columns are the date (preferable without header) But as you wish, I seperated them and fixed the visuals to make it even more obvious
    – Hailo90
    Nov 16, 2021 at 19:16
  • And to repeat myself: My Goal: I want a 1 / 0 Matrix (single row) for each person if his name or one of the nicknames attended. So If Person A attended Event 1 and Event 3, but with different names (Person A and Nickname A) I want 1 0 1. So obviously the date is the row and the name is the header /ironie off. please read next time, thank you
    – Hailo90
    Nov 16, 2021 at 19:30
  • This free forum is run by contributors who donate our time as we choose, not by paid employees. I am a professional developer who would be charging you quite a lot of money to even look at your issue, let alone solve it, if it were not via this forum. And having taken the time to read your post and view your sheet, it lacks clarity (your matrix isn't even aligned with names). I asked for you to supply that clarity. And you've responded with rudeness, entitlement and demands. So I will not be helping you now or in the future via this forum. Other contributors can make their own choice.
    – Erik Tyler
    Nov 16, 2021 at 19:41

1 Answer 1

Reset to default
0

So I found an answer myself:

In the Attendance Sheet I created a helper column which contains all attandee with a comma seperated (can be found in the example above)

And the result formula to display what I wanted is:

=ARRAYFORMULA(IF($A17<>"",
TRANSPOSE(IF(
IFERROR(IF(SEARCH($A17, $C$4:$C$6) > 0, 1, 0),0)
+IFERROR(IF(SEARCH($C17, $C$4:$C$6) > 0, 1, 0),0)
+IFERROR(IF(SEARCH($D17, $C$4:$C$6) > 0, 1, 0),0)
-COUNTBLANK(C17:D17) > 0, 1, 0))
,""))

I am pretty sure it is not perfect, maybe someone comes up with a better idea

But at least I didn't have to pay quite a lot to a professional

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.