Return a list of column data based on a unique value in another column

[jonbadman]

Hello, I am trying to write a formula which will return a list of data from a column (A) based on the specific value in another column (B). The issue I have is that the data in column A might be duplicated as it might have multiple statuses in column B.

For example, 12345 has status A and B so I don't want it to return a value in column C with a unique status of "Status A", as opposed to 12347 which has a unique status in column B of "A" and the job number needs to be returned in column C. I have tried using the UNIQUE function but returns all job numbers with a specified status in the formula but does not exclude where there is more than 1 status value (unless I haven't used enough arguments).

Hope this makes sense, grateful for any help that can be offered.

 

[Fluff]

Hi & welcome to MrExcel.
What version od Excel are you using?
Your profile shows 2016, but you are talking about the Unique function which doesn't exist in 2016.

 

[jonbadman]

Hi, I am using 2016 but have access to sharepoint which I can run the spreadsheet through if required. I have a couple of other spreadsheets I use TEXTJOIN on which I have had to run through Sharepoint. Thanks

 

[Fluff]

Ok, how about

=FILTER(A2:A100,(COUNTIFS(B2:B100,"<>A",A2:A100,A2:A100)=0)*(A2:A100<>""))

 

[jonbadman]

Perfect! Thanks for your help.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jonbadman]

Is there a way of including a UNIQUE function into this rather than using this data as a lookup column?

 

[Fluff]

How about

=UNIQUE(FILTER(A2:A100,(COUNTIFS(B2:B100,"<>A",A2:A100,A2:A100)=0)*(A2:A100<>"")))