CODEWITHSUNDEEP
excel-formuladuplicatesuniquecountif
ckp
ckp

Reputation: 593

Find duplicates and create bands in Excel

I am trying to create buckets based on the number of times id is repeated using an Excel formula

I have the following data format

  ID
 101
 200
 100
 100
 125
 200

I am trying to achieve the following

  1        2              /*Unique Ids*/
  2        2              /*Ids with two repetitions */
  3        0

This is basically removing the duplicate IDs and finding the number of repetitions for each id. I would like to do this is in a single cell using Excel formulas

All that I did was copy these IDs to another sheet, remove duplicates, use COUNTIF to count the number of occurrences, and use that to create my buckets.

Thanks

Upvotes: 0

Views: 82

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

If one has the dynamic array formula put this in the first cell:

=CHOOSE({1,2},SEQUENCE(3),INDEX(FREQUENCY(COUNTIF(A2:A7,A2:A7),SEQUENCE(3))/SEQUENCE(3),SEQUENCE(3)))

enter image description here

With the new LET() formula:

=LET(seq,{1;2;3},rng,A2:A7,CHOOSE({1,2},seq,INDEX(FREQUENCY(COUNTIF(rng,rng),seq)/seq,seq)))

If not then use put this array formula in the first cell:

=CHOOSE(COLUMN(A1),ROW(A1),INDEX(FREQUENCY(COUNTIF($A$2:$A$7,$A$2:$A$7),{1;2;3})/{1;2;3},ROW(A1)))

Confirm with Ctrl-Shift-Enter instead of Enter and copy over one column and down three rows.

enter image description here

Upvotes: 1

Related Questions