Solved How Search and Add to last number???

December 2, 2014 at 08:03:07
Specs: Windows 7
Hey Dear All i want know how i callculate it in excel???
for e.g

A 10
B 10
C 10
D 10
A 11
B 11
C 11
D 11
A 12
B 12
C 12
D 12
A ??
how i search the last digit of A add in it


See More: How Search and Add to last number???

Report •

#1
December 2, 2014 at 12:24:14
I'm not quite sure what you are trying to do.

If you are trying to get the number 13 next to last A, you could try this:

=COUNTIF(A$1:A13,A13)+9

This assumes that your first A is in A1. If you drag this formula down, it will produce the next incremental count for each matching piece of data it finds.

If that is not what you are looking for, you'll need to clarify your requirements. If you need to post example data, please click on the following line and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
December 3, 2014 at 05:53:15
Thank you so much dear DerbyDad03
but im sorry to say it not working...
and
i want to callculate it auto when i write it A than auto come next no. of A...
i hope you understand it coz i cant explain it very well...

Report •

#3
December 3, 2014 at 09:20:21
✔ Best Answer
The best I can do is offer a solution based on what I think you are trying to do. If this is not correct, let me know.

I am going to assume that you want to enter data in Column A and have Column B keep track of how many times that data appears in Column A.

Let's say you enter Tom in A1. B1 should display 1. Each time you enter data in Column A, the number of times that that exact piece of data appears in that row and above, that count should appear in Column B:

e.g. After each entry in Column A, you want to see a count in Column B:


         A           B
1       Tom          1
2       Fred         1
3       Ann          1
4       Tom          2
5       Ann          2
6       Tom          3
7       Fred         2
8

If you then enter a name in A8, you want to display the count of that value:


         A           B
1       Tom          1
2       Fred         1
3       Ann          1
4       Tom          2
5       Ann          2
6       Tom          3
7       Fred         2
8       Ann          3

To accomplish that, we need to count the values in the range from A1 down to the row that contains each value. This is done with the COUNTIF function. If we lock A1 with the dollar signs ($A$1) and let the row increment each time the formula is dragged down to the next row, it will increase the size if the range and apply the COUNTIF function to the value found in Column A across the larger range.

In theory, you could enter this formula in B1 and then drag it down as far as you want:

=COUNTIF($A$1:$A1,A1)

However, this will display a zero in the rows where there is no value in Column A.

That can be fixed in one of 2 ways:

Either set the option for your spreadsheet to not display a 0 when it is the result of a formula, or use this formula, which will display an empty cell if Column A is empty and use the COUNTIF function if Column A contains a value:

=IF(A1="","",COUNTIF($A$1:$A1,A1))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 4, 2014 at 03:02:35
Thank you so much dear its work properly....

Report •

Ask Question