Ask Your Question
0

column Comparision

asked 2018-03-08 04:55:46 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I have a column like this

MIR548H2
MIR548H2
MIR548H2
AAGAB
AAGAB
AAGAB
AAGAB
AAGAB
AAGAB
AAGAB

Want the counts in increasing manner like this

MIR548H2    1
MIR548H2    2
MIR548H2    3
AAGAB   1
AAGAB   2
AAGAB   3
AAGAB   4
AAGAB   5
AAGAB   6
AAGAB   7
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-03-08 05:27:49 +0200

gsk gravatar image

Found solution =COUNTIF($A$2:A2,A2)

edit flag offensive delete link more

Comments

This way each formula has to sift a range whose length increases by one per row. Assuming you have 10000 rows this means about 50 000 000 comparisons needed. A sheet has > 1 000 000 rows. Filled next to completely this makes about 500 000 000 000 comparisons.
In fact your data look as if you can assure that any specific content occurs in only one contiguous range. Exploiting this assurance you can reduce the expense in comparisons to N where N is the number of used rows.

Lupp gravatar imageLupp ( 2018-03-08 10:05:22 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-03-08 04:55:46 +0200

Seen: 25 times

Last updated: Mar 08 '18