CODEWITHSUNDEEP
excel
rustysys-dev
rustysys-dev

Reputation: 863

excel, extract unique values from one column based on the relationship between two columns

I have a list of users and their session ID's. I need to extract the unique session ID's for each user using preferably a formula.

I have placed a simplified image of what I am trying to achieve below.

I have tried the following array... but it doesn't limit the output to a specific user...

{=INDEX(session_ids, MATCH(0, COUNTIF(D$1:D1, session_ids),0))}

enter image description here

If anyone has any knowlege of the portion of the formula that I am missing, any help would be greatful!

Thank you

Upvotes: 1

Views: 96

Answers (1)

eshwar
eshwar

Reputation: 694

Your formula is not considering the username. You need to filter session ids matching username.

=INDEX(IF(user_id="user2",session_ids,""), MATCH(0, COUNTIF(D$1:D1, IF(user_id="user2",session_ids,"")),0))

EDIT

OP's solution is better than what I came up with as it avoids creatting a filtered array which is done by the IF function (twice) in my formula. Here is the more efficient formula modified slightly in for consistency with my formula:

=IFERROR(INDEX(session_ids, MATCH(0, IF(user_id="user2", COUNTIF(D$1:D1, session_ids)),0)),"")

Upvotes: 2

Related Questions