CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulaformulagoogle-sheets-formula
Wave Chappelle
Wave Chappelle

Reputation: 35

Excel/GSheets add unique flag to reoccuring values in a dynamic list

As a follow-up to the question I posted previously here - Excel/GSheets count unique versus reoccuring values in dynamic list I am looking to count unique values in a list of 30k+ Webinar attendees, but this time across multiple criteria.

The logic here is a bit different than a simple COUNTIFS formula as I need to retain all the rows to visualize and interact with in Google Data Studio - so in other words, I need a formula to insert a unique flag ("New" or "Returning") based on the uniqueness parameter.

In short, I need to add logic to this formula to consider different uniqueness:

=IF(COUNTIF($B$2:$B2,B2)>1,"Returning","New")

Let's use this simplified data set example for me to explain, notice all attendees are unique except P1 and notice how the values in columns F and G should change by the criteria. Note, I've added borders and colors for easier comprehension.

Link to sample data - https://drive.google.com/file/d/1LxirDbk-9w5zu5qqFNoBI9QiK4Za6lFA/view?usp=sharing

sample

Here's the graph the data is going in: Graph it is to be visualized in:

Any help would be much appreciated! Thank you!

Upvotes: 1

Views: 250

Answers (2)

kirkg13
kirkg13

Reputation: 3010

As an alternative to the earlier answer (I'm a slow worker!) consider this approach, which is basically very similar, but works to fill the column.

These are three formulas that go in E1, F1, and G1. In E1:

={"Attendee unique";
  ARRAYFORMULA(if(COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B))>1,"Returning","New"))}

In F1:

={"Week Unique";
  ARRAYFORMULA(if(COUNTIFS(B2:B&C2:C,B2:B&C2:C,ROW(B2:B),"<="&ROW(B2:B))>1,"Returning","New"))}

In G1:

={"Day Unique";
 ARRAYFORMULA(IF(COUNTIFS(B2:B&C2:C&D2:D,B2:B&C2:C&D2:D,ROW(B2:B),"<="&ROW(B2:B))>1,"Returning","New"))}

enter image description here

Upvotes: 2

Dimension Datacraft
Dimension Datacraft

Reputation: 116

It looks like there are a few ways to handle this, but to just piggy back on what you already have going in Column E... you can just extend that to handle multiple conditions in F and G by combining your Attendee column with those week and day columns respectively.

This would be the formula for F2 (just drag it down like you did in E)

=IF(COUNTIF(arrayformula($B$2:$B2&$C$2:$C2),B2&C2)>1,"Returning","New")

This would be the formula for G2

=IF(COUNTIF(arrayformula($B$2:$B2&$D$2:$D2),B2&D2)>1,"Returning","New")

Again, there are likely many other ways to handle this, but the concept is to use the combined attendee and week or day indicator as a combined unique key to look up against the list itself.

here is a sheet with the solution for you to copy https://docs.google.com/spreadsheets/d/1DGfVRKnzJM6-tmNPy5GGdD_ca8YAZy0LRB6ci4nDHK0/edit?usp=sharing

Upvotes: 2

Related Questions