Skip Blanks Based on Multiple Criteria's

[CuriousForge]

Hello!

I am at wits end with this one having spent an entire day on it

My desired results are in Col D in blue.

I have unique Case ID's but duplicate codes. I want the Approved codes to show the approved Case ID by skipping blanks & picking the first match it gets.

 

[duggie33]

What version of Excel are you using?

Doug

 

[CuriousForge]

What version of Excel are you using?

Doug

365 Enterprise

 

[Fluff]

365 Enterprise

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[duggie33]

Try this...

Book1
	A	B	C	D
1	Case ID (unqiue)	Code (Duplicates)	Approved	Approved Case ID
2	AlliA	828		A138
3	A123A	553		A132
4	A124A	642
5	A132	553	Approved	A132
6	A138	828	Approved	A138
7	A145	727		A158
8	A151	633
9	A158	727	Approved	A158
10	A164	828		A138
Sheet2

Cell Formulas
Range	Formula
D2:D10	D2	=FILTER($A$2:$A$10,($B$2:$B$10=B2)*($C$2:$C$10="Approved"),"")

It would likely result in an error if a code is "Approved" more than once.

Hope that helps,

Doug

 

[duggie33]

Here is an alternate formula that I don't think will give an error for the scenario I mentioned.

Book1
	A	B	C	D	E
1	Case ID (unqiue)	Code (Duplicates)	Approved	Approved Case ID
2	AlliA	828		A138	A138
3	A123A	553		A132	A132
4	A124A	642
5	A132	553	Approved	A132	A132
6	A138	828	Approved	A138	A138
7	A145	727		A158	A158
8	A151	633
9	A158	727	Approved	A158	A158
10	A164	828		A138	A138
Sheet2

Cell Formulas
Range	Formula
D2:D10	D2	=FILTER($A$2:$A$10,($B$2:$B$10=B2)*($C$2:$C$10="Approved"),"")
E2:E10	E2	=IFERROR(INDEX($A$2:$A$10,MATCH(B2&"Approved",$B$2:$B$10&$C$2:$C$10,0)),"")

 

[CuriousForge]

Thanks a ton Doug - it works!!
I really wish I had posted this earlier, would've saved me so much time & effort. You're a saviour! Cheers!

 

[CuriousForge]

And its the first one that worked right away

 

[duggie33]

I learned by struggling...I have spent days on lesser problems.

You're welcome. Glad to help.

Doug

 

[CuriousForge]

I learned by struggling...I have spent days on lesser problems.

You're welcome. Glad to help.

Doug

Very relatable... been there myself, as recent as today.
Having worked on Excel so many years one would think they have a hang of things, but it always finds a way to humble you.