Finding a non repeating Value

[maslam15]

Hello Everyone,
I need your help to figuring out a formula or a way to analyze certain information.

I have value on a Key column for month January. I want to know if a value from January shows up again in the Month of February. If if does not show up again that means all the values in February month are new values and I want to know which new values showed up and how many. I want to repeat the same process for every month until December.

The other way of framing the question should be
What new values showed up in March that were not in February or the month prior.

Key	Month
10.168.3.200 19824	January
10.168.3.200 19825	January
10.168.3.200 19825	February
10.168.3.200 19827	February
10.168.3.200 19828	February
10.168.3.200 19824	February
10.168.3.200 19828	March
10.230.500.400 105486	March
10.230.500.400 105487	March
10.230.500.400 105488	March
10.230.500.400 105489	March

Key 10.168.3.200 19824 from January shows up in February also were key 10.168.3.200 19828 does not show up in January so I would like the count to be 1 and key 10.168.3.200 19828 to show that this key in new in February. Since key 10.168.3.200 19828 has already showed up in February, it does not matter if it showed up in March which it did since it newly appeared in February.

Thank you so much for your help. Please let me know if I can help answer any questions.

 

[jtakw]

Hi,

Since I'm not 100% following, what are the expected results for your sample above ?

 

[Anthony47]

Why don't you prepare a pivot table, with the key in the row and the months in the columns?
At that point you will have the picture of which address show up in more that one month

(don't miss jtakw's message, above)

Bye

 

[Peter_SSs]

Highlighting the new ones could be done with Conditional Formatting ..

22 03 24.xlsm
	A	B
1	Key	Month
2	10.168.3.200 19824	January
3	10.168.3.200 19825	January
4	10.168.3.200 19825	February
5	10.168.3.200 19827	February
6	10.168.3.200 19828	February
7	10.168.3.200 19824	February
8	10.168.3.200 19828	March
9	10.230.500.400 105486	March
10	10.230.500.400 105487	March
11	10.230.500.400 105488	March
12	10.230.500.400 105489	March
New Keys

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B12	Expression	=COUNTIFS($A$1:$A1,$A2,$B$1:$B1,"<>"&$B2)=0	text	NO

.. although you did talk about counting the new ones each month so perhaps something like this instead?

22 03 24.xlsm
	A	B	C
1	Key	Month	Count New
2	10.168.3.200 19824	January	1
3	10.168.3.200 19825	January	2
4	10.168.3.200 19825	February
5	10.168.3.200 19827	February	1
6	10.168.3.200 19828	February	2
7	10.168.3.200 19824	February
8	10.168.3.200 19828	March
9	10.230.500.400 105486	March	1
10	10.230.500.400 105487	March	2
11	10.230.500.400 105488	March	3
12	10.230.500.400 105489	March	4
New Keys (2)

Cell Formulas
Range	Formula
C2:C12	C2	=IF(COUNTIFS(A$1:A1,A2,B$1:B1,"<>"&$B2),"",COUNTIFS(B$1:B1,B1,C$1:C1,">0")+1)