Excel counts how many people have been hit by a phone number

A friend of the police station in the investigation of a case, encountered a problem of the title, ask me to help. He was unable to provide the original data because he wanted to keep it secret, but he gave me a request. They are now using a suspect's phone record to include n new suspects from the call log, as well as a call history for each suspect. Now it's time to count the calls, the same phone number, how many times each suspect has been hit, and how many suspects have called the same number.

Based on the above summary, Excel tables are as follows:

Figure I: A computer tutorial on RAW data sheets

Figure II: Statistical Results table

The above illustration shows:

Figure I: User One, user second, user three, user four positive number for analog phone number; the direction is to call or be called, there is no meaning.

Figure II: The number of positive users is the number of times the phone is used, if a phone is only one user hit, so do not count, in other words, the statistical results table in the phone number of at least two dozen users.

Solution of the idea:

⒈ This statistic cannot be resolved using common methods such as functions, PivotTable reports, and so on. I used the VBA programming to implement the statistics.

⒉ first of all users of the phone (do not repeat, repeat only once), extracted out into the statistical results table. The phone in the result table is unique.

⒊ based on the phone number of the result table, counts the number of times each user uses the number and stores the results of the statistics in the result table under the user.

⒋ deletes the line with the same phone number that is used by two of the following users.

Way to solve:

⒈ because the number of users is unknown, but starting from the 2nd column is already, so we can through the loop to statistics. The condition of the loop starts at row 1th from column 2nd, and the cell is not empty.

⒉ each user's phone number loop is similar to ⒈

The specific program source code is as follows:

Private Sub CommandButton1_Click ()

Sheets (2). Rows (2 & ":" & 65536) = ""

Sheets (2). Columns ("b:iv") = ""

Dim Ls, I, J, Isa, K, YHS

Isa = False

i = 2

If Sheets (1). Cells (1, 2) = "" Then

MsgBox "No users, no statistics!", vbOKOnly + vbcritical, "Error alert"

Exit Sub

Else

Do While True

If Sheets (1). Cells (1, i) <> "" Then

Sheets (2). Cells (1, i) = Sheets (1). Cells (1, i)

i = i + 1

Else

Exit do

End If

Loop

YHS = i-1

End If

Ls = 2

Do While Sheets (1). Cells (1, Ls) <> ""

i = 2

Do While Sheets (1). Cells (i, Ls) <> ""

If Sheets (2). Cells (2, 1) = "" Then

Sheets (2). Cells (2, 1) = Sheets (1). Cells (i, Ls)

Else

j = 2:isa = False

Do While Sheets (2). Cells (J, 1) <> ""

If Sheets (2). Cells (j, 1) = Sheets (1). Cells (i, Ls) Then Isa = True:exit do

j = j + 1

Loop

If not Isa Then Sheets (2). Cells (j, 1) = Sheets (1). Cells (i, Ls)

End If

i = i + 1

Loop

ls = ls + 1

Loop

Ls = 2

Do While Sheets (2). Cells (1, Ls) <> ""

i = 2

Do While Sheets (2). Cells (i, 1) <> ""

j = 2:k = 0

Do While Sheets (1). Cells (J, Ls) <> ""

If Sheets (2). Cells (i, 1) = Sheets (1). Cells (J, Ls) Then k = k + 1

j = j + 1

Loop

If k <> 0 Then Sheets (2). Cells (i, Ls) = k

i = i + 1

Loop

ls = ls + 1

Loop

'===========================================

' Delete rows used by multiple users of the same phone

'===========================================

i = 2

Do While Sheets (2). Cells (i, 1) <> ""

j = 2:k = 0

Do While J <= YHS

If Sheets (2). Cells (i, J) <> "" Then k = k + 1

j = j + 1

Loop

If CInt (k) < 2 Then

Sheets (2). Rows (i). Delete Shift:=xlup ' Remove I row

Else

i = i + 1

End If

Loop

'===========================================

MsgBox "Statistics complete!", vbOKOnly + vbinformation, "system hint"

Sheets (2). Select

End Sub