igarren
igarren

Reputation: 37

Count How many times a value appeared in a column(excel)

Ok, So I have an excel file that have a column that may contain duplicate value like this

PO NUMBER        PO COUNT
P100293490                4
P100293490
P100293490
P100293490
P100293492                2
P100293492
P100293494                3
P100293494
P100293494
P100293497                4
P100293497
P100293497
P100293497
P100293499                1
P100293490                2
P100293490
P100293492                1

I need to count how many times it appeared on the column and place it beside the first occurrence of the word. The problem is that the PO NUMBER is not sorted so values may repeat on the lower rows of the excel. i tried everything (Lookup, Match, Countif, IF) and nothing seems to work. Please Help? Thanks in Advance

Upvotes: 1

Views: 1401

Answers (1)

XOR LX
XOR LX

Reputation: 7762

Assuming the data as you give it is in A1:B18 (with headers in row 1), enter this in B2:

=IF(A1<>A2,MATCH(TRUE,INDEX(A2:A$1000<>A2,),)-1,"")

Copy down as required.

Amend the 1000 to a sufficiently higher row reference if necessary.

Upvotes: 1

Related Questions