I got some problems with Excel and I'm clueless how to concatenate multiple criteria.

Below I added an overview of my excel sheet (without personal information). The personal information I deleted are mailadresses.

What I am trying to do is to identify the unique mailadresses which both got the statuses 'ENOV_20221104' and 'ENOV_PREREG_20221104'.

The blanks in the excel sheet are mailadresses and the same mailadress can be filled multiple times in the excel sheets. Is there a way to concatenate the information based on the mailadress?

I added a column with ' Vooraanmelden' which fills the column with a 1 when the SUBSCRIBE_SOURCE contains'ENOV_PREREG_20221104' and a column 'Aanmelden' which fills the column with a 1 when the SUBSCRIBE_SOURCE contains 'ENOV_PREREG_20221104'. Ideally we find rows where the two columns are both filled with 1 however each row is an entry and an entry only contains one status

CodePudding user response：

Create a formula to count if the email appears at least once with value ENOV_20221104 AND ENOV_PREREG_20221104:

My formula to FILTER is:

=AND(COUNTIFS($A$2:$A$35,A2,$B$2:$B$35,"ENOV_20221104")>0,COUNTIFS($A$2:$A$35,A2,$B$2:$B$35,"ENOV_PREREG_20221104")>0)

Then create a unique list based on this column. If you have Excel 365 you may benefit from functions like UNIQUE or FILTER, but I got an old version here so I'm doing it with a Pivot Table:

Just take EMAIL field to rows section and FILTER field to Filter section, and filter by TRUE value. Then you will get your desired output

CodePudding user response：

I have made a solution for a similar problem, creating two columns, one being the adres, and the second one a condition.
I have made a third column, containing all different addresses, and next to it a column, showing the amount of criteria each addresses belongs to, using the weird formula:

=NOT(NOT(COUNTIF(C$2:C$15,D2&1)))   NOT(NOT(COUNTIF(C$2:C$15,D2&2)))

What's the sense of NOT(NOT(...)):

• when the count is larger or equal than one, not becomes FALSE (value zero), and again not becomes TRUE (value one).
• when the count zero, not becomes TRUE (value one), and again not becomes FALSE (value zero).

=> this makes it easier to calculate their sum :-)

Oh, the values in the "C" column are a simple =UNIQUE(A2:A15).

Hereby a screenshot: