Scan Column and Create Value in Adjacent Cell

[slinger52]

Trying to have excel scan down a column of dates and insert a value that corresponds to a changing date. As the column is scanned if there are duplicates a value of 2 would be entered in the cell adjacent to the bottom most duplicate cell leaving the the remaining adjacent cells blank. If there is not a duplicate value above then a 1 would be entered in the adjacent cell. Have tried many formulas with no success. Below is an example of the result I am looking for utilizing a snapshot of my data:

2/24/2015
2/24/2015	2
3/31/2015
3/31/2015	2
4/29/2015
4/29/2015	2
4/30/2015	1
5/29/2015
5/29/2015	2
6/27/2015
6/27/2015	2
7/29/2015
7/29/2015	2
8/28/2015
8/28/2015	2
9/18/2015
9/18/2015	2
10/13/2015
10/13/2015	2
11/9/2015
11/9/2015	2
2/13/2015	1
3/13/2015	1
4/10/2015	1
4/25/2015	1
5/30/2015	1
6/26/2015	1
7/24/2015	1
8/21/2015	1
9/20/2015	1
10/16/2015	1
11/13/2015	1
12/23/2015	1
2/14/2015	1
3/12/2015	1
2/5/2015	1
3/2/2015	1
4/22/2015	1
5/18/2015	1
6/15/2015	1
7/14/2015	1
8/4/2015	1
9/2/2015	1
10/5/2015	1
10/29/2015	1
11/17/2015	1
12/22/2015	1
7/11/2015	1
8/11/2015	1
9/13/2015	1
10/14/2015	1
11/9/2015	1
12/4/2015	1
2/20/2015
2/20/2015	2
3/7/2015	1

<tbody>
</tbody>

Any and all help would be greatly appreciated!!!

 

[Scott Huish]

Assuming that is starting in row 1, column A.

In B1:
=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A:A,A1)=COUNTIF($A$1:A1,A1),2,""),1)

Copy down.

 

[Nishant94]

Unknown
	A	B
1	Date	Result
2	02/24/2015
3	02/24/2015	2
4	03/31/2015
5	03/31/2015	2
6	04/29/2015
7	04/29/2015	2
8	04/30/2015	1
9	05/29/2015
10	05/29/2015	2
11	06/27/2015
12	06/27/2015	2
13	07/29/2015
14	07/29/2015	2
15	08/28/2015
16	08/28/2015	2
17	09/18/2015
18	09/18/2015	2
19	10/13/2015
20	10/13/2015	2
21	11/09/2015
22	11/09/2015	2
23	02/13/2015	1
24	03/13/2015	1
25	04/10/2015	1
26	04/25/2015	1
27	05/30/2015	1
28	06/26/2015	1
29	07/24/2015	1
30	08/21/2015	1
31	09/20/2015	1
32	10/16/2015	1
33	11/13/2015	1
34	12/23/2015	1
35	02/14/2015	1
36	03/12/2015	1
37	02/05/2015	1
38	03/02/2015	1
39	04/22/2015	1
40	05/18/2015	1
41	06/15/2015	1
42	07/14/2015	1
43	08/04/2015	1
44	09/02/2015	1
45	10/05/2015	1
46	10/29/2015	1
47	11/17/2015	1
48	12/22/2015	1
49	07/11/2015	1
50	08/11/2015	1
51	09/13/2015	1
52	10/14/2015	1
53	11/09/2015	1
54	12/04/2015	1
55	02/20/2015
56	02/20/2015	2
57	03/07/2015	1
Sheet9

Cell Formulas
Range	Formula
B2	=IF(A2=A3,"",COUNTIF(INDEX(A$2:A2,LOOKUP(1,MATCH(A$2:A2,A2,0),ROW(A$2:A2)-ROW($A$2)+1)-1):A2,A2))

 

[slinger52]

This does produce a positive result within the sample data, however when applied to the full data set it adds the number of occurrences of the date up. For example once the date 02/24/2015 appears again in the column it then inserts a 3 instead of a 1 or 2? Any thoughts? Thanks so much...

Unknown

	A	B
1	Date	Result
2	02/24/2015
3	02/24/2015	2
4	03/31/2015
5	03/31/2015	2
6	04/29/2015
7	04/29/2015	2
8	04/30/2015	1
9	05/29/2015
10	05/29/2015	2
11	06/27/2015
12	06/27/2015	2
13	07/29/2015
14	07/29/2015	2
15	08/28/2015
16	08/28/2015	2
17	09/18/2015
18	09/18/2015	2
19	10/13/2015
20	10/13/2015	2
21	11/09/2015
22	11/09/2015	2
23	02/13/2015	1
24	03/13/2015	1
25	04/10/2015	1
26	04/25/2015	1
27	05/30/2015	1
28	06/26/2015	1
29	07/24/2015	1
30	08/21/2015	1
31	09/20/2015	1
32	10/16/2015	1
33	11/13/2015	1
34	12/23/2015	1
35	02/14/2015	1
36	03/12/2015	1
37	02/05/2015	1
38	03/02/2015	1
39	04/22/2015	1
40	05/18/2015	1
41	06/15/2015	1
42	07/14/2015	1
43	08/04/2015	1
44	09/02/2015	1
45	10/05/2015	1
46	10/29/2015	1
47	11/17/2015	1
48	12/22/2015	1
49	07/11/2015	1
50	08/11/2015	1
51	09/13/2015	1
52	10/14/2015	1
53	11/09/2015	1
54	12/04/2015	1
55	02/20/2015
56	02/20/2015	2
57	03/07/2015	1

<tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula B2 =IF(A2=A3,"",COUNTIF(INDEX(A$2:A2,LOOKUP(1,MATCH(A$2:A2,A2,0),ROW(A$2:A2)-ROW($A$2)+1)-1):A2,A2)) <tbody> </tbody>

<tbody>
</tbody>

 

[Scott Huish]

The formula I posted doesn't do that. How did it not work for you?

 

[Nishant94]

Try this:

[COLOR=#333333]=IF([/COLOR][COLOR=#0000FF]A2=A3,"",IF(COUNTIF([COLOR=#FF0000]INDEX([COLOR=#00FF00]A$2:A2,LOOKUP([COLOR=#800080]1,MATCH([COLOR=#008080]A$2:A2,A2,0[/COLOR]),ROW([COLOR=#008080]A$2:A2[/COLOR])-ROW([COLOR=#008080]$A$2[/COLOR])+1[/COLOR])-1[/COLOR]):A2,A2[/COLOR])>1,2,1)[/COLOR][COLOR=#333333])[/COLOR]