Excel Numbering of identical cells
I can not fill out a form as the function I seek. (Probably because I do not know what I had to search for :shifty:
But I have a column of text, some of which are identical. Eg. in the range from A1-A9
The text can be distributed so that A1-A3 is called "Test", A4-A5 = "Test 2" and A6-A9 = "Test 3"
I would now like to excel can autonumber identical boxes for me in a new column. Eg. Column C
This will look something like this out. A1-A3, in column C will be number 1, 2, 3 A4-A5 = 1, 2 and A6-A9 are assigned numbers 1, 2, 3
Hope the above makes sense and that one of you can give me an idea of how to get this (probably simple) problem :biggrin:
Re: Excel Numbering of identical cells
=COUNTIF(A$1:A$9;A1)-COUNTIF(A1:A$9;A1)+1
It puts you into the C1 and drag down to C9. So C9 ends with this:
=COUNTIF(A$1:A$9;A9)-COUNTIF(A9:A$9;A9)+1
That it does is to count all occurrences of a value throughout the column and pull the remaining bodies from.
Re: Excel Numbering of identical cells
Super. It was just as it should be. Thank you.
Can your form also be rewritten to instead of numbered each one, count the number of identical columns, giving a number next to the last cell?
Eg. according to the example in #1 would then A1-A3 give the result 3 in cell C3, 2 of the C5 and 4 of the C9
Re: Excel Numbering of identical cells
=if(countif(a1:a$9;a1)=1;countif(a$1:a$9;a1)
;"")
EDIT: delete "" if you want a zero instead of nothing.
Re: Excel Numbering of identical cells
Nice trick with the only view it once.
Low a pivot table, where does the column in both the left and right side. Well into the rows and values (data). So you have to format your data to show count.
Re: Excel Numbering of identical cells
Quote:
Originally Posted by
Zecho
=if(countif(a1:a$9;a1)=1;countif(a$1:a$9;a1)
;"")
EDIT: delete "" if you want a zero instead of nothing.
Again spot on. Once again many thanks :thumbup1:
Re: Excel Numbering of identical cells
Quote:
Originally Posted by
Praetor
Low a pivot table, where does the column in both the left and right side. Well into the rows and values (data). So you have to format your data to show count.
It was 49,000 lines imported as a CSV file, and one of my colleagues was about to go through them manually zzz ... As expected there was one here who had a quicker solution, as was the case :ohyeah:
