Forum Discussion
Looking up value in multiple rows
Hello all!
In the excel attached, I want to know if a value(Contact ID) also appears in other rows, based on the criteria below:
Value = YES in Column J, IF Contact ID where Degree Level = Summer and Stage = Student, also appears anywhere else in the rows where Degree Level not equal to Summer and Stage = Enrolled or Student.
I hope this makes sense!
Thanks in advance for your help!
With over 30,00 rows of data, the number of calculations to be performed is humongous, so I had to use a helper column. Without it, my fast PC completely bogged down.
In K2:
=AND(E2<>"Summer", OR(D2={"Student","Enrolled"}))
Fill down.
In J2:
=IF(AND(E2="Summer", D2="Student", COUNTIFS($B$2:$B$31195, B2, K2:K31195, TRUE)), "Yes", "No")
Fill down.
Workbook attached.
3 Replies
With over 30,00 rows of data, the number of calculations to be performed is humongous, so I had to use a helper column. Without it, my fast PC completely bogged down.
In K2:
=AND(E2<>"Summer", OR(D2={"Student","Enrolled"}))
Fill down.
In J2:
=IF(AND(E2="Summer", D2="Student", COUNTIFS($B$2:$B$31195, B2, K2:K31195, TRUE)), "Yes", "No")
Fill down.
Workbook attached.
Apologies for the delay, I was away. This is perfect, thank you!
May I ask, is it also possible to see, of the vales = "yes", what was their related school term semester in another column?
Also due to confidentiality if it's possible to delete the excel in your reply?
Many thanks to you!
In L2:
=IF(J2="Yes", TEXTJOIN(", ", TRUE, UNIQUE(FILTER($E$2:$E$31195, ($B$2:$B$31195=B2)*($E$2:$E$31195<>"Summer")*(($D$2:$D$31195="Student")+($D$2:$D$31195="Enrolled")), ""))), "")
Fill down.
I'll send you the workbook with this formula and with a couple of corrections. I removed the attachment from my earlier reply.
