Looking for some Excel help

Printable View

03-27-2017, 09:33 AM
outsider

Looking for some Excel help

I'm having a difficult day already and I can't seem to think of a formula for this. In one column I have a set of numbers in one column (far left) and in the other dates. What I need is to create a formula to have a number sequence of 1,2,3 but changing with each new number in the far left column:
0000010910 05/04/2014 5/31/2015
0000010910 05/05/2013 5/3/2014
0000011270 05/04/2014 5/31/2015
0000011270 07/15/2013 5/3/2014
0000011280 05/04/2014 5/31/2015
0000011280 07/15/2013 5/3/2014
0000011290 05/04/2014 5/31/2015
0000011290 07/15/2013 5/3/2014
0000011300 05/04/2014 5/31/2015
0000011300 07/15/2013 5/3/2014
0000011310 05/04/2014 5/31/2015
0000011310 07/15/2013 5/3/2014
0000011320 05/04/2014 5/31/2015
0000011320 07/15/2013 5/3/2014
0000011330 05/04/2014 5/31/2015
0000011330 07/15/2013 5/3/2014
0000011340 05/04/2014 5/31/2015
0000011340 07/15/2013 5/3/2014
0000011350 05/04/2014 5/31/2015
0000011350 07/15/2013 5/3/2014
0000011360 05/04/2014 5/31/2015
0000011360 07/15/2013 5/3/2014
0000011370 05/04/2014 5/31/2015
0000011370 07/15/2013 5/3/2014
0000011380 05/04/2014 5/31/2015
0000011380 07/15/2013 5/3/2014
0000011390 05/04/2014 5/31/2015
0000011390 07/15/2013 5/3/2014
0000011400 05/04/2014 5/31/2015

If you can help that would be great. Thanks
03-27-2017, 09:36 AM
Thaco

you just want a count of unique numbers in the far left column?

=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))
03-27-2017, 09:48 AM
outsider

Thaco I must be doing something incorrect. I have the #'s in column C and the dates in D &E. I'm trying to replace A with C but i'm getting an error.

I tried =IF(C2<>C1,1,F1+1) and get the number 1 but when I click and drag down it only stays as 1
03-27-2017, 09:55 AM
tobslau

1 Attachment(s)

formula in Column B - assuming the length of the left text doesn't change (10 characters).

Else, text to columns with spaces first to isolate it.

=IF(LEFT(A3,10)=LEFT(A2,10),B2,B2+1)

attached an excel file.

Column A Column B
Title 0
0000010910 05/04/2014 5/31/2015 1
0000010910 05/05/2013 5/3/2014 1
0000011270 05/04/2014 5/31/2015 2
0000011270 07/15/2013 5/3/2014 2
0000011280 05/04/2014 5/31/2015 3
0000011280 07/15/2013 5/3/2014 3
0000011290 05/04/2014 5/31/2015 4
0000011290 07/15/2013 5/3/2014 4
0000011300 05/04/2014 5/31/2015 5
0000011300 07/15/2013 5/3/2014 5
0000011310 05/04/2014 5/31/2015 6
0000011310 07/15/2013 5/3/2014 6
0000011320 05/04/2014 5/31/2015 7
0000011320 07/15/2013 5/3/2014 7
0000011330 05/04/2014 5/31/2015 8
0000011330 07/15/2013 5/3/2014 8
0000011340 05/04/2014 5/31/2015 9
0000011340 07/15/2013 5/3/2014 9
0000011350 05/04/2014 5/31/2015 10
0000011350 07/15/2013 5/3/2014 10
0000011360 05/04/2014 5/31/2015 11
0000011360 07/15/2013 5/3/2014 11
0000011370 05/04/2014 5/31/2015 12
0000011370 07/15/2013 5/3/2014 12
0000011380 05/04/2014 5/31/2015 13
0000011380 07/15/2013 5/3/2014 13
0000011390 05/04/2014 5/31/2015 14
0000011390 07/15/2013 5/3/2014 14
0000011400 05/04/2014 5/31/2015 15
03-27-2017, 10:44 AM
outsider

Thanks Thaco & tobslau. I was able to get it to work.
03-27-2017, 10:46 AM
Thaco

Quote:

Originally posted by outsider
Thaco I must be doing something incorrect. I have the #'s in column C and the dates in D &E. I'm trying to replace A with C but i'm getting an error.

I tried =IF(C2<>C1,1,F1+1) and get the number 1 but when I click and drag down it only stays as 1

it should work if you change "A2:A10" to your range, in both places, and the cells are formatted as numbers, if its formatted text it wont work.