Excel geekery

sumif/format

17 messages
17/08/2012 at 13:33

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?

17/08/2012 at 13:51

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.

17/08/2012 at 14:04

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

17/08/2012 at 14:07

Doesn't work

 

17/08/2012 at 14:10

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.

17/08/2012 at 14:19

Are the cells in their spreadsheet formatted for text rather than numbers? Does sumif work in their spreadsheet?

17/08/2012 at 14:20

The TRIM function will remove spaces at the start or finish (and excess ones in the middle)

17/08/2012 at 14:38

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?

17/08/2012 at 14:50

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

Cheerful Dave    pirate
17/08/2012 at 16:14

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

Cheerful Dave    pirate
17/08/2012 at 16:16

That's pretty old coding by the way, there's probably a neater way of doing it nowadays.

Cheerful Dave    pirate
17/08/2012 at 16:18

Of course, if it's always 4 spaces you could search for those and replace with nothing.  That should work too.

17/08/2012 at 17:11

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

 

03/11/2012 at 08:03

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

03/11/2012 at 08:12

You've switched the referencing style somehow. Follow this linky thing

03/11/2012 at 08:18

I have just realised LN.  I decided to look through the options menu and found this

http://s3.runnersworld.co.uk/members/images/200439/gallery/excel.png?width=350

 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

03/11/2012 at 12:44

http://s3.runnersworld.co.uk/members/images/18481/gallery/theitcrowd.jpg?width=350

 "Hello, IT. Have you tried turning it off and back on again?"

IT is gobbledeygook to me. Lol. 


We'd love you to add a comment! Please login or take half a minute to register as a free member
17 messages
Previously bookmarked threads are now visible in "Followed Threads". You can also manage notifications on these threads from the "Forum Settings" section of your profile settings page to prevent being sent an email when a reply is made.
Forum Jump  

RW competitions

RW Forums