SOLVED

Matching e-mails with specific continous number

Copper Contributor

Hello everyone,

I am having trouble to find out the right function how to make customer either an aquisition or retention. (by his order)

On the screen are visible informations that I have and in the column H I want to specifically select customer as Aquisition or Retention. The function that I have in would work fine if there was an Customer ID which is numeric, but right now I have only e-mail or IP adress. I thought that if I would combine these two and then apply this function it would work but as I have found out it does not listen the e-mail part of the cell and only the IP. The problem occurs when there are same e-mails (so the same customer) but different IP adresses from which he/she made the order. Then the function is not working and I have in the H column aquisition for the same customer which is wrong because I have the rule that every new order from the same customer should be retention. For example in the first 7 rows there is the same person who made the order but has different IPs. There should be in the 2nd row that it is an aquisition because it is first time order and in the rows 3-7 it should be retention because the order is either second or 2+. What happens is that there are 2 different IPs and they mess it up. 

The A column represents combination of mail (D column) and IP (B column). C is just ID of an order, E is date, F is value and G is date that works for the function that I have had in H.

I am in a desperate situation and I would appreciate your help. 


Screenshot.png

Thank you in advance!

3 Replies
best response confirmed by Lukas_P11 (Copper Contributor)
Solution

@Lukas_P11 

=IF(COUNTIF($D$2:D2,D2)=1,"aquisition","retention")

Does this formula return your expected result?

email.JPG 

When you think you have found out the simpliest solution there is always another :D I did not even think about this one. Thank you very much!
Thx a lot, it works very well
1 best response

Accepted Solutions
best response confirmed by Lukas_P11 (Copper Contributor)
Solution

@Lukas_P11 

=IF(COUNTIF($D$2:D2,D2)=1,"aquisition","retention")

Does this formula return your expected result?

email.JPG 

View solution in original post