Index match first value that contains "@"

[Creese11]

Hey,
First time posting.

I have a data set with duplicate account keys and values in the form of email addresses.
I need to create a consolidated data set that has only one account key and a corresponding email value.
My issue is this -- some of the duplicate account keys have blanks or "old account" listed in the value section, while others have email addresses. I want to pull only the addresses using the partial match of "*@*".

I have to imagine this is an array formula that could work similar to the one that Index matches the first non-blank value.

Formula suggestions would be perfect, but I'm also open to suggestions on if VBA, Power BI, Power Query, etc would be better options

Thanks!

 

[Aladin Akyurek]

Care to post 5 rows of (fake) data, including one "old account"?

 

[Creese11]

[TABLE="width: 189"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Account[/TD]
[TD] Owner[/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD] Joe@gmail.com[/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD] old account[/TD]
[/TR]
[TR]
[TD="align: right"]3 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2 [/TD]
[TD] old account[/TD]
[/TR]
[TR]
[TD="align: right"]3 [/TD]
[TD] Tim@gmail.com[/TD]
[/TR]
</tbody>[/TABLE]

 

[Creese11]

Also would add that it won't always say "old account" .... could be any number of things like "stopped", "old", "closed", etc

 

[Aladin Akyurek]

Can we suppose that we don't want to list accounts which do not have an owner with "@"?

 

[Fazza]

one way is a simple query,

SELECT  DISTINCT Account, Owner
FROM [YourSheetName$]
WHERE Owner LIKE '%@%'

Would that be OK?

 

[Aladin Akyurek]

Can we suppose that we don't want to list accounts which do not have an owner with "@"?

In case the question is confusing...

Book1
	A	B	C	D
1	Account	Owner	acc	owner
2	1	Joe@gmail.com	1	Joe@gmail.com
3	2	old account	3	Tim@gmail.com
4	3
5	1
6	2	old account
7	3	Tim@gmail.com
Sheet1

In C2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$7,SMALL(IF(FREQUENCY(IF($A$2:$A$7<>"",IF(ISNUMBER(SEARCH("@",$B$2:$B$7)),MATCH($A$2:$A$7,$A$2:$A$7,0))),ROW($A$2:$A$7)-ROW($A$2)+1),ROW($A$2:$A$7)-ROW($A$2)+1),ROWS($C$2:C2))),"")

In D2 control+shift+enter, not just enter, and copy down:

=IF(C2="","",INDEX($B$2:$B$7,MATCH(C2,IF(ISNUMBER(SEARCH("@",$B$2:$B$7)),$A$2:$A$7),0)))