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:

[TABLE="width: 158"]
<tbody>[TR]
[TD]2/24/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/24/2015[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3/31/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/31/2015[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4/29/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/29/2015[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4/30/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5/29/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/29/2015[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6/27/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/27/2015[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7/29/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/29/2015[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8/28/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/28/2015[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9/18/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/18/2015[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]10/13/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/13/2015[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]11/9/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/9/2015[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2/13/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3/13/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4/10/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4/25/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5/30/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6/26/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7/24/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8/21/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9/20/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10/16/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11/13/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12/23/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2/14/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3/12/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2/5/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3/2/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4/22/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5/18/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6/15/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7/14/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8/4/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9/2/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10/5/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10/29/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11/17/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12/22/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7/11/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8/11/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9/13/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10/14/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11/9/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12/4/2015[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2/20/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/20/2015[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3/7/2015[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


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

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Result[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]02/24/2015[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]02/24/2015[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]03/31/2015[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]03/31/2015[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]04/29/2015[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]04/29/2015[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]04/30/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]05/29/2015[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]05/29/2015[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]06/27/2015[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]06/27/2015[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]07/29/2015[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]07/29/2015[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]08/28/2015[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]08/28/2015[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]09/18/2015[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]09/18/2015[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]10/13/2015[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]10/13/2015[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]11/09/2015[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]11/09/2015[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]02/13/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]03/13/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]04/10/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]04/25/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]05/30/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]06/26/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]07/24/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]08/21/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]09/20/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]10/16/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]11/13/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]12/23/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]02/14/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]03/12/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]02/05/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]03/02/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]04/22/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]05/18/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]06/15/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]07/14/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]08/04/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]09/02/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]10/05/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]46[/TD]
[TD="align: right"]10/29/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]47[/TD]
[TD="align: right"]11/17/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]48[/TD]
[TD="align: right"]12/22/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]49[/TD]
[TD="align: right"]07/11/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]50[/TD]
[TD="align: right"]08/11/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]51[/TD]
[TD="align: right"]09/13/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]52[/TD]
[TD="align: right"]10/14/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]53[/TD]
[TD="align: right"]11/09/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]54[/TD]
[TD="align: right"]12/04/2015[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]55[/TD]
[TD="align: right"]02/20/2015[/TD]

[TD="align: center"]56[/TD]
[TD="align: right"]02/20/2015[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]57[/TD]
[TD="align: right"]03/07/2015[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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]