Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Working out age from "Day" "Month" "Year" | Excel Worksheet Functions | |||
Formula not working -- =SUMIF($F$6:$F$91,"=90",G6:I91) | Excel Discussion (Misc queries) |