Using COUNTIF to count values with leading zeros

0
votes

In Microsoft Excel 2016 I am using a VBA function to add values to a column and then I want to use COUNTIF to count the occurrences of those values in another worksheet.

I am adding the values like this: 

Public Function addPackage()
With Sheets("Scanned packages")
Dim scannedPackage as String
Dim nextrow As Long
scannedPackage = "00370726207879082204"
nextrow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(nextrow, 1).Value = "'" & scannedPackage
End With
End Function

However, when I use the COUNTIF function to check this value for matches in another sheet, it says 49 occurrences when there is really only 1 of this specific (I used CTRL+F to verify that there really is just one match).

The values in both sheets all start with two leading zeros.

I have tried to format the cells as "Text", "General", "number" etc. without any luck.

Any input would be highly appreciated.

The data in the "Database" sheet, column "B":

00370726207879082204
00370726207879082211
00370726207879082228
00370726207879082235
00370726207879082242
00370726207879082259
00370726207879082266
00370726207879082273
00370726207879082280
00370726207879082297
00370726207879082310
00370726207879082433
00370726207879082334
00370726207879082358
00370726207879082389
00370726207879082372
00370726207879082402
00370726207879082426
00370726207879083485
00370726207879083492
00370726207879083591
00370726207879082457
00370726207879082549
00370726207879082471
00370726207879082495
00370726207879082662
00370726207879082518
00370726207879084062
00370726207879082532
00370726207879083409
00370726207879082563
00370726207879082587
00370726207879082600
00370726207879082617
00370726207879082631
00370726207879082655
00370726207879082686
00370726207879083454
00370726207879082709
00370726207879082754
00370726207879082853
00370726207879083508
00370726207879082723
00370726207879082747
00370726207879082778
00370726207879082785
00370726207879084154
00370726207879082808
00370726207879082822
00370726207879082846
00370726207879082877
00370726207879082891
00370726207879082914
00370726207879082938
00370726207879082952
00370726207879082976
00370726207879082990
00370726207879083010
00370726207879083034
00370726207879083058
00370726207879084031
00370726207879083072
00370726207879083096
00370726207879083119
00370726207879083362
00370726207879083133
00370726207879083157
00370726207879083171
00370726207879083195
00370726207879083201
00370726207879083225
00370726207879083249
00370726207879083263
00370726207879083287
00370726207879083300
00370726207879083324
00370726207879083348
00370726207879083355
00370726207879083386
00370726207879083393
00370726207879083423
00370726207879083447
00370726207879083478
00370726207879083522
00370726207879083546
00370726207879083560
00370726207879083584
00370726207879083614
00370726207879083638
00370726207879083652
00370726207879083676
00370726207879083690
00370726207879083706
00370726207879083720
00370726207879083744
00370726207879083768
00370726207879083782
00370726207879083805
00370726207879083829
00370726207879083843
00370726207879083867
00370726207879083881
00370726207879083904
00370726207879083928
00370726207879084079
00370726207879084147
00370726207879083942
00370726207879083966
00370726207879083980
00370726207879084000
00370726207879084024
00370726207879084055
00370726207879084093
00370726207879084116
00370726207879084130
00370726207879084178

This is the data (in cell A2 of "Scanned packages" sheet I want to count:

00370726207879082204

This is the COUNTIF formula I am using:

=COUNTIF(Database!A:B,'Scanned packages'!A2)
2
excelvbaexcel-formula
Show COUNTIF formula and some sample data to us. – basic
Sample data: 00370726207879082204 COUNTIF formula: =COUNTIF(Database!A:B,'Scanned packages'!A2) – Emil
How can you get the 49 occurrences if there is only one number? – basic
Now, updated in the question, thanks. – Emil
Please include the formula you are using too. – JvdV

2 Answers

2
votes

use

=COUNTIF(Database!A:B,CONCATENATE("*",'Scanned packages'!A2))
0
votes

You could also get around this problem if you wrap your values in column A in double quotes, and equally look for "00370726207879082204" instead of 00370726207879082204

Macro to use to add double quotes to values in your list:

  Sub AddQuote()
  Dim myCell As Range
  For Each myCell In Selection
        myCell.Value = Chr(34) & Replace(myCell.Value, Chr(34), "") & Chr(34)
  Next myCell
  End Sub