"Sumif" not working consistently
 

I have a file that includes the names of companies, each of which is supposed
to have a unique number to identify it. Each company also has dollar value
information. Any company that did not have a unique number associated was
removed and put on a separate sheet, I filtered both files for "contains" and
looked for specific character strings to see if there was a similar company
name in my "master" file.

If I found a sufficiently similar name in my "NoCo#" file I copied the cell
that had the unique value and pasted it into a separate column on the NoCo#
sheet.

File Source Name Name fr Master Value
Master (12345) SmithParts 15.00
NoCo# (0) SmithParts (12345) SmithParts 25.00
NoCo# (0) PartsSmith (12345) SmithParts 11.00

Because of the inconsistency of the data there are a number of instances in
which the same company appeared multiple times with differing varieties of
the name. Even in those instances, I need to capture the value of *all* the
entries and add them to the total value in the master file. In the instance
above, for (12345) SmithParts I should have a total value of $51.00

I did a "sumif" to match the "name" column against the "name from master"
column and although most of the entries came over, not all did and I can't
figure out why. Even if I go to my master file and (again) copy that unique
cell and drop it onto the other file, it still returns a "#N/A" value - 150
of 166 have no conflict but I am having to look at all of them because of the
instances of multiples.

Any thoughts on why the bulk of them would match but a just a few wouldn't?
Thx!

"Sumif" not working consistently
 

Perhaps you had some extra spaces in the name field for those that did
not match - you could use TRIM to get rid of those, but if you had non-
breaking spaces (character 160) you would have to use Edit/Replace.

Hope this helps.

Pete

On Apr 28, 6:57*pm, smartgal
wrote:
I have a file that includes the names of companies, each of which is supposed
to have a unique number to identify it. *Each company also has dollar value
information. *Any company that did not have a unique number associated was
removed and put on a separate sheet, I filtered both files for "contains" and
looked for specific character strings to see if there was a similar company
name in my "master" file. *

If I found a sufficiently similar name in my "NoCo#" file I copied the cell
that had the unique value and pasted it into a separate column on the NoCo#
sheet.

File Source * * Name * * * * * * * * * * * * * Name fr Master * * * * * *Value
Master * * * (12345) SmithParts * * * * * * * * * * * * * * * * * * * * *15.00
NoCo# * * * * * (0) SmithParts * * * * * * * (12345) SmithParts * * * 25.00
NoCo# * * * * * (0) PartsSmith * * * * * * * (12345) SmithParts * * * 11.00

Because of the inconsistency of the data there are a number of instances in
which the same company appeared multiple times with differing varieties of
the name. *Even in those instances, I need to capture the value of *all* the
entries and add them to the total value in the master file. *In the instance
above, for (12345) SmithParts I should have a total value of $51.00

I did a "sumif" to match the "name" column against the "name from master"
column and although most of the entries came over, not all did and I can't
figure out why. *Even if I go to my master file and (again) copy that unique
cell and drop it onto the other file, it still returns a "#N/A" value - 150
of 166 have no conflict but I am having to look at all of them because of the
instances of multiples.

Any thoughts on why the bulk of them would match but a just a few wouldn't? *
Thx!