Counting Values from a Single Column

[elmotactics]

I have an unfortunately formatted report of repeat callers. It's a single column, with the phone number, and then all the dates/times it called in the cells blow it, and then the next number, and all the dates/times it called, etc... for thousands of cells. What I need to do is somehow count all the times each number called. It would look something like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Phone number 1[/TD]
[/TR]
[TR]
[TD]9/25/17 10:13am
[/TD]
[/TR]
[TR]
[TD]9/25/17 11:36am[/TD]
[/TR]
[TR]
[TD]Phone number 2[/TD]
[/TR]
[TR]
[TD]10/03/17 8:28am
[/TD]
[/TR]
</tbody>[/TABLE]
etc...

Is it possible to count the number of cells after each phone number until it runs up to the next phone number?

 

[Scott Huish]

Perhaps:
=IF(LEFT(A1,5)="Phone",IFERROR(MATCH("Phone*",A2:$A$5000,0),COUNTA($A$1:$A$5000))-ROW(A1),"")
Copy down.

Excel 2010
	A	B
1	Phone number 1	2
2	9/25/17 10:13am
3	9/25/17 11:36am
4	Phone number 2	1
5	10/03/17 8:28am
Sheet1

Cell Formulas
Range	Formula
B1	=IF(LEFT(A1,5)="Phone",IFERROR(MATCH("Phone*",A2:$A$5000,0),COUNTA($A$1:$A$5000))-ROW(A1),"")
B2	=IF(LEFT(A2,5)="Phone",IFERROR(MATCH("Phone*",A3:$A$5000,0),COUNTA($A$1:$A$5000))-ROW(A2),"")
B3	=IF(LEFT(A3,5)="Phone",IFERROR(MATCH("Phone*",A4:$A$5000,0),COUNTA($A$1:$A$5000))-ROW(A3),"")
B4	=IF(LEFT(A4,5)="Phone",IFERROR(MATCH("Phone*",A5:$A$5000,0),COUNTA($A$1:$A$5000))-ROW(A4),"")
B5	=IF(LEFT(A5,5)="Phone",IFERROR(MATCH("Phone*",A6:$A$5000,0),COUNTA($A$1:$A$5000))-ROW(A5),"")

 

[elmotactics]

Perhaps:
=IF(LEFT(A1,5)="Phone",IFERROR(MATCH("Phone*",A2:$A$5000,0),COUNTA($A$1:$A$5000))-ROW(A1),"")
Copy down.

Excel 2010

	A	B
Phone number 1
9/25/17 10:13am
9/25/17 11:36am
Phone number 2
10/03/17 8:28am

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(LEFT(A1,5)="Phone",IFERROR(MATCH("Phone*",A2:$A$5000,0),COUNTA($A$1:$A$5000))-ROW(A1),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(LEFT(A2,5)="Phone",IFERROR(MATCH("Phone*",A3:$A$5000,0),COUNTA($A$1:$A$5000))-ROW(A2),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=IF(LEFT(A3,5)="Phone",IFERROR(MATCH("Phone*",A4:$A$5000,0),COUNTA($A$1:$A$5000))-ROW(A3),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=IF(LEFT(A4,5)="Phone",IFERROR(MATCH("Phone*",A5:$A$5000,0),COUNTA($A$1:$A$5000))-ROW(A4),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=IF(LEFT(A5,5)="Phone",IFERROR(MATCH("Phone*",A6:$A$5000,0),COUNTA($A$1:$A$5000))-ROW(A5),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The only issue with that is that it doesn't actually say "Phone" in the cell, it's an actual phone number...

But now that I think about it, would it work similarly if I did something like =LEFT(A1,9)="000000000" ?
Or would that look specifically for 9 zeros?

 

[Scott Huish]

Is it entered as a number and formatted as a phone number and is it always a 10 digit number?

and yes that would specifically look for 9 zeros

 

[elmotactics]

Yes, it's always 10 digits and always formatted as just numbers.

I actually just checked it again, and the dates are just dates, no timestamps.

 

[Scott Huish]

=IF(ISNUMBER(A1),IFERROR(MATCH(TRUE,ISNUMBER(A2:$A$5000),0)+ROW(A1)-1,COUNTA($A$1:$A$5000))-ROW(A1),"")

Confirm with CTRL-SHIFT-ENTER rather than just Enter.
Copy down.

Excel 2010
	A	B
1	(503) 555-1212	2
2	9/25/17 10:13am
3	9/25/17 11:36am
4	(714) 555-1212	1
5	10/03/17 8:28am
6	(712) 555-1212	1
7	10/03/17 8:28am
Sheet1

Cell Formulas
Range	Formula
B1	{=IF(ISNUMBER(A1),IFERROR(MATCH(TRUE,ISNUMBER(A2:$A$5000),0)+ROW(A1)-1,COUNTA($A$1:$A$5000))-ROW(A1),"")}
B2	{=IF(ISNUMBER(A2),IFERROR(MATCH(TRUE,ISNUMBER(A3:$A$5000),0)+ROW(A2)-1,COUNTA($A$1:$A$5000))-ROW(A2),"")}
B3	{=IF(ISNUMBER(A3),IFERROR(MATCH(TRUE,ISNUMBER(A4:$A$5000),0)+ROW(A3)-1,COUNTA($A$1:$A$5000))-ROW(A3),"")}
B4	{=IF(ISNUMBER(A4),IFERROR(MATCH(TRUE,ISNUMBER(A5:$A$5000),0)+ROW(A4)-1,COUNTA($A$1:$A$5000))-ROW(A4),"")}
B5	{=IF(ISNUMBER(A5),IFERROR(MATCH(TRUE,ISNUMBER(A6:$A$5000),0)+ROW(A5)-1,COUNTA($A$1:$A$5000))-ROW(A5),"")}
B6	{=IF(ISNUMBER(A6),IFERROR(MATCH(TRUE,ISNUMBER(A7:$A$5000),0)+ROW(A6)-1,COUNTA($A$1:$A$5000))-ROW(A6),"")}
B7	{=IF(ISNUMBER(A7),IFERROR(MATCH(TRUE,ISNUMBER(A8:$A$5000),0)+ROW(A7)-1,COUNTA($A$1:$A$5000))-ROW(A7),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[elmotactics]

So I tried that formula, and did ctrl+shift+enter to make it an array formula, and I just get zeros. I'm not sure what's going on. I even copy pasted it to a new sheet to remove the headers so it starts at A1.

 

[Scott Huish]

Is the only thing that is in the phone number field just the phone number or is there text or something with it and is your time format in the same exact format as your sample.
For my formula to work, the phone number field must be only a number (it can be formatted as a phone #), and your time/date fields don't match an Excel date/time format (because there is no space between the time and AM/PM) so it won't find that as a number.

 

[elmotactics]

The data would basically look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1234567890[/TD]
[/TR]
[TR]
[TD]9/25/17
[/TD]
[/TR]
[TR]
[TD]9/26/17[/TD]
[/TR]
[TR]
[TD]2234567890[/TD]
[/TR]
[TR]
[TD]9/25/17[/TD]
[/TR]
[TR]
[TD]9/27/17[/TD]
[/TR]
[TR]
[TD]9/30/17[/TD]
[/TR]
</tbody>[/TABLE]


There are no time-stamps like I originally thought in my first post.

 

[Scott Huish]

=IF(A1>100000,IFERROR(MATCH(TRUE,A2:$A$5000>100000,0)+ROW(A1)-1,COUNTA($A$1:$A$5000))-ROW(A1),"")

Confirm with CTRL-SHIFT-ENTER
Copy down.