Filtering based on the desired value from a reference table.

[yasarcan]

12

gogle sheets  database

Hello, I am a search and rescue volunteer. I need an application that can count and list volunteers entering and exiting the disaster area. I got the counting part done, but I'm having trouble creating a list. Can you please help me with this? Thank you!

SELECT(güvenlik[Gönüllü adı], 
AND([Durum] = "GİRİŞ", [maps id] = LOOKUP([_THISROW], "güvenlik", "maps id", "maps id")))

I can filter and count based on the desired value using this formula. However, I'm having trouble excluding entries where the [Durum] = "ÇIKIŞ". I created two virtual columns for entry and exit and listed them separately. Then I tried another virtual column, but still couldn't solve it. What is your suggestion? (DURUM=STATUS, GİRİŞ=ENTER, ÇIKIŞ=EXİT; turkish) 

[AleksiAlkio]

Not 100% sure what you are looking for. Your existing SELECT() formula should already exclude "Exits" away as it's looking only Enters. Would you please elaborate your case, thanks.

[yasarcan]

Hi but; 

test2 ----------SELECT(güvenlik[Gönüllü adı],
AND([Durum] = "GİRİŞ", [maps id] = LOOKUP([_THISROW], "güvenlik", "maps id", "maps id")))

I need a formula that will show only the volunteer named Veli. because he entered the field and did not leave.

[lynchk21]

Here might be an approach:

• Make a virtual column [Virtual ID] to identify the volunteer and map for a unique id:  [Gönüllü id]&[maps id]
• Make a slice "ÇIKIŞ" for "ÇIKIŞ" on the güvenlik table with the expression: [Durum]= "ÇIKIŞ"
• Make a virtual column for [Current Status] with the expression: IF(IN([Virtual ID],ÇIKIŞ[Virtual ID],"ÇIKIŞ","GİRİŞ"))
• Use the [Current Status] to filter 

[yasarcan]

Thank you for your suggestion. However, the work areas are separate. The (maps id) entry table will not be common, the people entering and exiting each field will be listed separately, so I need to use a reference table.

[yasarcan]

SELECT DISTINCT güvenlik[Gönüllü adı]
WHERE AND(
[Durum] = "GİRİŞ",
[maps id] NOT IN (
SELECT DISTINCT güvenlik[maps id]
WHERE AND(
[Durum] = "ÇIKIŞ",
[maps id] = LOOKUP([_THISROW], "güvenlik", "maps id", "maps id")
)
)
)

gemini ai gave this formula but it was not solved 🙂

[yasarcan · Accepted solution]

INTERSECT(
INTERSECT(
SELECT(güvenlik[Gönüllü adı], [Durum] = "GİRİŞ"),
SELECT(güvenlik[Gönüllü adı], [maps id] = [_THISROW].[MapKey])
),
INTERSECT(
SELECT(güvenlik[Gönüllü adı], [Durum] = "ÇIKIŞ"),
SELECT(güvenlik[Gönüllü adı], [maps id] = [_THISROW].[MapKey])
)
)