Filter dataset with multiple parameters

[acedanger]

Hello there, this request is far beyond my capabilities of excel and I'm hoping that it can be done.

I have a dataset as an example below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Email address[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Domain name[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Confidence score[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Type[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Position[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>email1@example.com[/TD]
[TD]example.com[/TD]
[TD]

97​

[/TD]
[TD]generic[/TD]
[TD]editor[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>email2@example.com[/TD]
[TD]example.com[/TD]
[TD]99[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>personal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]editors@example.com[/TD]
[TD]example.com[/TD]
[TD]96[/TD]
[TD]generic[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>1email1@example2.com[/TD]
[TD]example2.com[/TD]
[TD]99[/TD]
[TD]personal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>1email2@example2.com[/TD]
[TD]example2.com[/TD]
[TD]98[/TD]
[TD]personal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>1email3@example2.com[/TD]
[TD]example2.com[/TD]
[TD]97[/TD]
[TD]generic[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>editor@example3.com[/TD]
[TD]example3.com[/TD]
[TD]56[/TD]
[TD]generic[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>2email2@example3.com[/TD]
[TD]example3.com[/TD]
[TD]66[/TD]
[TD]generic[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>2email3@example3.com[/TD]
[TD]example3.com[/TD]
[TD]22[/TD]
[TD]generic[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The purpose is to only select 1 email address from the same domain name.

The first priority is to take first take the email that has under "position" with one of the following potential conditions in parentheses (editor, writer, publisher)

if there are no email addresses fitting this criteria then i want to:

take the highest "confidence score" that has type "personal"


if no type "personal" above then:


choose email with the email prefix with one of the following potential conditions in parentheses (blog, connect, contact, editor, editorial,hello, hi, contactus,info, team, support, editors)


so for the example above the results should be:

email1@example.com (as it's an editor)
1email1@example2.com (as it has the highest confidence score in that group that has type "personal")
editor@example3.com (as the group is all type generic and has editor in the prefix)

 

[JLGWhiz]

I don't believe you can do that by filtering in the truest sense of filtering. It could probably be done with VBA using If...Then...ElseIf statement. to execute an elimination algorithm. Or possibly use the Dictionary method from VBS.

 

[acedanger]

Thanks JLG, do you have any references for how that could be done with either of those cases you mention?

 

[JLGWhiz]

Thanks JLG, do you have any references for how that could be done with either of those cases you mention?

No, I don't believe I want to take that much time to work it out. Sorry.
Regards, JLG