4

I have a listing of video conference calls in an excel spreadsheet.

The data rows are grouped into calls which all dialed the same dial in number. The first column is something like:

  • 6024
  • 6024
  • 6024
  • 6184
  • 6184
  • 6432
  • 6432
  • 6432

So the first three entries are people all talking to each other in one video conference, using the dial in number of 6024.

The next two numbers is a 2 person video conference, and so on.

I want to count the number of participants who dialled into each dial in number. I want to find out the distribution of meeting sizes. To do this, I want to count the number of identical dial in numbers that occur in next to each other.

Note that dial in numbers are recycled, and can be used by different participants on other days, so I can't use a COUNTIF() function over the entire data range, as it will count duplicate dial in numbers occuring at different times.

I can only count duplicates that occur up until a different dial in number occurs.

How could I insert the number of participants in column B next to the last participant, so that I have:

  • 6024
  • 6024
  • 6024 3
  • 6184
  • 6184 2
  • 6432
  • 6432
  • 6432 3
Improve this question

1 Answer 1

Reset to default
6

Assuming A is your called numbers column, then put "1" in B1

Then in B2 put

=IF(A2=A1,B1+1,1)

And drag it down. This will result in

6024    1
6024    2
6024    3
6184    1
6184    2
6432    1
6432    2
6432    3

So the highest number is the number of participants.

If you want to get rid of the counts, you can put this into C and it will populate the final count only.

=IF(A1<>A2,B1,"")


6024    1   
6024    2   
6024    3   3
6184    1   
6184    2   2
6432    1   
6432    2   
6432    3   3

You can hide column B if it is distracting.

Improve this answer
4
  • Hi Paul. See this image (i112.photobucket.com/albums/n180/SRD001/screenshots/excel.png). It isn't producing the expected sequence.
    – Steve
    Aug 14, 2013 at 3:13
  • The formula in that cell is wrong, so it looks like it didn't copy down correctly. For row 5, it should start =if(A5...
    – Paul
    Aug 14, 2013 at 3:26
  • Hey, this worked like a charm! Question: How to modify the formula so that the dupe count is on the same row as the first occurrence of the dupe, not the last.
    – samthebrand
    Sep 6, 2018 at 20:34
  • 1
    @samthebrand You can do the same thing, but put the initial 1 in the bottom of the column and in the next row up put =IF(A8=A7,B8+1,1) (assuming A8 is the bottom row), then drag up
    – Paul
    Sep 8, 2018 at 0:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .