Find first instance, offset and mark

[Alphacsulb]

Hi Excel World,

I'm hoping there is a way to automate my process through VBA, or maybe even a formula with the use of helper columns.


I'm primarily concerned with Column C and J and M.
Column C represents a zip code sort for mailing.
Column J represents a consecutive number.
Column M is a list of the unique cells in Column C.

Essentially whenever I find the first instance of the unique cell in C, I want column J to be overwritten with the number 1.

Here is my sample data:
Sheet1

*	C	D	E	F	G	H	I	J	K	L	M
1	*************************SNGLP	6788	165	52	T38	735100	5	12	*	*	Unique List
2	*************************SNGLP	6789	165	56	T38	939069	5	13	*	*	*************************SNGLP
3	*************************SNGLP	6790	165	163	T38	111320	5	14	*	*	***********AUTO**5-DIGIT 95947
4	***********AUTO**5-DIGIT 95948	6791	249	86	T6	104485	5	25	*	*	***********AUTO**5-DIGIT 95948
5	***********AUTO**5-DIGIT 95948	6792	249	232	T6	103915	5	26	*	*	***********AUTO**5-DIGIT 96122
6	***********AUTO**5-DIGIT 95948	6793	249	45	T7	938900	5	27	*	*	**********AUTO**MIXED AADC 926
7	***********AUTO**5-DIGIT 96122	7361	270	192	T28	933626	5	1	*	*	*
8	***********AUTO**5-DIGIT 96122	7362	270	193	T28	933945	5	2	*	*	*
9	***********AUTO**5-DIGIT 96122	7363	270	202	T28	111434	5	3	*	*	*
10	***********AUTO**5-DIGIT 96122	7381	270	249	T28	938324	5	4	*	*	*
11	**********AUTO**MIXED AADC 926	7382	97	6	T1	622170	5	99	*	*	*
12	**********AUTO**MIXED AADC 926	7383	97	52	T1	108113	5	100	*	*	*
13	**********AUTO**MIXED AADC 926	7384	97	53	T1	107942	5	101	*	*	*
14	**********AUTO**MIXED AADC 926	7385	97	57	T1	111240	5	102	*	*	*
15	**********AUTO**MIXED AADC 926	7386	97	67	T1	934109	5	102	*	*	*
16	**********AUTO**MIXED AADC 926	7387	97	68	T1	110372	5	104	*	*	*
17	**********AUTO**MIXED AADC 926	7388	97	74	T1	103128	5	105	*	*	*
18	*************************SNGLP	7389	165	111	T38	104766	5	15	*	*	*
19	*************************SNGLP	7390	165	112	T38	385500	5	16	*	*	*
20	*************************SNGLP	7391	165	118	T38	108250	5	17	*	*	*
21	***********AUTO**5-DIGIT 95947	7392	270	74	T2	935289	5	33	*	*	*
22	***********AUTO**5-DIGIT 95948	7441	249	91	T6	103877	5	28	*	*	*
23	***********AUTO**5-DIGIT 95948	7442	249	92	T6	937697	5	29	*	*	*
24	***********AUTO**5-DIGIT 95947	7392	270	74	T2	935289	5	34	*	*	*

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



The desired result would be:
Sheet1

*	C	D	E	F	G	H	I	J
1	*************************SNGLP	6788	165	52	T38	735100	5	1
2	*************************SNGLP	6789	165	56	T38	939069	5	13
3	*************************SNGLP	6790	165	163	T38	111320	5	14
4	***********AUTO**5-DIGIT 95948	6791	249	86	T6	104485	5	1
5	***********AUTO**5-DIGIT 95948	6792	249	232	T6	103915	5	26
6	***********AUTO**5-DIGIT 95948	6793	249	45	T7	938900	5	27
7	***********AUTO**5-DIGIT 96122	7361	270	192	T28	933626	5	1
8	***********AUTO**5-DIGIT 96122	7362	270	193	T28	933945	5	2
9	***********AUTO**5-DIGIT 96122	7363	270	202	T28	111434	5	3
10	***********AUTO**5-DIGIT 96122	7381	270	249	T28	938324	5	4
11	**********AUTO**MIXED AADC 926	7382	97	6	T1	622170	5	1
12	**********AUTO**MIXED AADC 926	7383	97	52	T1	108113	5	100
13	**********AUTO**MIXED AADC 926	7384	97	53	T1	107942	5	101
14	**********AUTO**MIXED AADC 926	7385	97	57	T1	111240	5	102
15	**********AUTO**MIXED AADC 926	7386	97	67	T1	934109	5	102
16	**********AUTO**MIXED AADC 926	7387	97	68	T1	110372	5	104
17	**********AUTO**MIXED AADC 926	7388	97	74	T1	103128	5	105
18	*************************SNGLP	7389	165	111	T38	104766	5	15
19	*************************SNGLP	7390	165	112	T38	385500	5	16
20	*************************SNGLP	7391	165	118	T38	108250	5	17
21	***********AUTO**5-DIGIT 95947	7392	270	74	T2	935289	5	1
22	***********AUTO**5-DIGIT 95948	7441	249	91	T6	103877	5	28
23	***********AUTO**5-DIGIT 95948	7442	249	92	T6	937697	5	29
24	***********AUTO**5-DIGIT 95947	7392	270	74	T2	935289	5	34

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:233px;"><col style="width:35px;"><col style="width:28px;"><col style="width:28px;"><col style="width:49px;"><col style="width:49px;"><col style="width:14px;"><col style="width:28px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Any leads would be appreciated.

 

[Ragnar1211]

Helper column approach - just put the formula in row 1 of the helper column and fill down, it will give you a new j column.

=if(countif($c$1:c1,c1)=1,1,j1)

 

[skywriter]

Here's the same solution as Ragnar1211 he gets the credit he beat me to it. Mine just shows the results. I copied your original values from column H into the helper column L and the formula in the H column can be copied down.



Excel 2010

	B	C	D	E	F	G	H	I	J	K	L
1	6788	165	52	T38	735100	5	1	*	*	Unique List	12
2	6789	165	56	T38	939069	5	13	*	*	*************************SNGLP	13
3	6790	165	163	T38	111320	5	14	*	*	***********AUTO**5-DIGIT 95947	14
4	6791	249	86	T6	104485	5	1	*	*	***********AUTO**5-DIGIT 95948	25
5	6792	249	232	T6	103915	5	26	*	*	***********AUTO**5-DIGIT 96122	26
6	6793	249	45	T7	938900	5	27	*	*	**********AUTO**MIXED AADC 926	27
7	7361	270	192	T28	933626	5	1	*	*	*	1
8	7362	270	193	T28	933945	5	2	*	*	*	2
9	7363	270	202	T28	111434	5	3	*	*	*	3
10	7381	270	249	T28	938324	5	4	*	*	*	4
11	7382	97	6	T1	622170	5	1	*	*	*	99
12	7383	97	52	T1	108113	5	100	*	*	*	100
13	7384	97	53	T1	107942	5	101	*	*	*	101
14	7385	97	57	T1	111240	5	102	*	*	*	102
15	7386	97	67	T1	934109	5	102	*	*	*	102
16	7387	97	68	T1	110372	5	104	*	*	*	104
17	7388	97	74	T1	103128	5	105	*	*	*	105
18	7389	165	111	T38	104766	5	15	*	*	*	15
19	7390	165	112	T38	385500	5	16	*	*	*	16
20	7391	165	118	T38	108250	5	17	*	*	*	17
21	7392	270	74	T2	935289	5	1	*	*	*	33
22	7441	249	91	T6	103877	5	28	*	*	*	28
23	7442	249	92	T6	937697	5	29	*	*	*	29
24	7392	270	74	T2	935289	5	34	*	*	*	34

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet21​

Worksheet Formulas Cell Formula H1 =IF(COUNTIF($A$1:$A1,A1)=1,1,$L1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Alphacsulb]

Awesome, thank you!