Howdy, Stranger!
Categories
Options
Excel geekery
I use SUMIF a lot to keep an eye on stock in external warehouses. The supplier sends me a worksheet with stock sorted by code. I have the same code in a master sheet and with one particular supplier SUMIF doesn't work unless I retype the code over their code, or copy and paste special values from my code list to theirs.
I realise this is probably due to the formatting of the external worksheet, but I can't change that. Does anyone know how to get my sheet to read their sheet without retyping?
The materials in this website are in no way intended to replace the professional medical care, advice, diagnosis or treatment of a doctor, qualified personal trainer, therapist, dietitian or nutritionist Runners's World, Part of the Hearst UK well-being network © Copyright 2017
Comments
Their code might include spaces(for instance at the end of the code) which are not in your code.This can often happen if data is being exported from a stock system into excel.
Using Find and replace should be quicker than retyping.
I would be tempted to give them a master list with the proper codes on which they can compare to the codes on their stock sheet, ie get them to clean up the coding before they send it to you.
thanks Bos I'll try F&R, I can't give them a master list, my codes are in a field of their stock control system and have been exported as you say
Doesn't work
If you can copy your code into their worksheet, can you also nuke their formatting? If you Ctrl + C the whole of their worksheet and then Paste Special Data Only you should just have the values.
Are the cells in their spreadsheet formatted for text rather than numbers? Does sumif work in their spreadsheet?
The TRIM function will remove spaces at the start or finish (and excess ones in the middle)
Thanks Bear, I have deduced there are 4 spaces after my code in their sheet.
I inserted another column and used LEFT to import just the code, is there a neater way, I haven't used TRIM before?
I guess it depends a bit on how you're tackling the problem. If you're doing some sort of LOOKUP then you would need a separate column (if LEFT is doing the job then why complicate things?) but if you're just comparing a single cell you can use stuff like
IF (TRIM(B2)="abc", true value, false value )
or similarly with LEFT
You could use a macro to get rid of extra spaces at the end of text. Select the cells you want to trim and run this:
Sub trimright()
If TypeName(Selection) = "Range" Then
For Each R In Selection.Cells
R.Value = RTrim(R)
Next
Else
Title = "Trim Right Spaces"
Style = vbInformation
prompt = "A single cell or a range of cells must" & Chr(13) & _
"be selected to complete this operation."
response = MsgBox(prompt, Style, Title)
End If
End Sub
That's pretty old coding by the way, there's probably a neater way of doing it nowadays.
Of course, if it's always 4 spaces you could search for those and replace with nothing. That should work too.
Lookup won't work bear, there are sometimes three or four orders to add up and lookup seems to just give me the first one only. What I've done till I can try out the macro is insert a column with the TRIMmed code then deleted the original column so all the ranges are still correct and I can now see the stocks in my sheet
My excel has suddenly got numbers in the horizontal heading and not letters, so I have R1C1 instead of A1. What would be E9 is now R9C5.
Is this happening to anyone else or have I *done* something to make it happen. If it's me, what have I done and how can I out it back again.
I have only noticed it since I put in a =SUBTOTAL, but that does not mean that it changed then. I have changed the formulas back to =SUM but that has not solved the issue
I know it's not the end of the world, but it's making my formulas look really cumbersome
You've switched the referencing style somehow. Follow this linky thing
I have just realised LN. I decided to look through the options menu and found this
I unchecked the R1C1 style and now I am sorted
Annoying little thing, but glad I found it as it will make vlookup tutorials easier. It's a shame it can't be applied to individual spreadsheets tho as I wouldn't want it on everything
"Hello, IT. Have you tried turning it off and back on again?"
IT is gobbledeygook to me. Lol.