"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!
|