Replies

So it seems, since that works perfectly! Many thanks, RG! I have to admit I just took the examples on the page you sent me to at face value, and the one that said it would count all unique text and number values whilst ignoring blanks seemed what I wanted – obviously not quite, though.

I still don’t understand quite how it does it, I don’t think – my guess is that it looks at each value in the range, and if it hasn’t occurred before in that range, adds one to the total? Still don’t know how – there’s no loop that I can see, and it looks like it’s saying if its frequency is more than once, ignore it, but it can’t quite be that since then that value wouldn’t get counted at all, and they all have to be counted once …

Anyway, thanks very much again – my spreadsheet is working properly again!

Thanks, Paul and RG. I know the dates are just straight dates since I enter them, though.

I’m fairly certain this is what I want:

=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),””), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),””))>0,1))

but it says that this “counts the number of unique text and number values in cells A2:A10 , but does not count blank cells or text values (6)” and since I get #VALUE! when I apply it to my column of dates I presume it doesn’t like the dates, and I don’t understand what it’s doing well enough to know if it’s even possible to get it to allow them or not!

My dates are simply entered as, eg, 09 05 14 and set to display as ‘Fri 9 May 14’, if that’s of interest.

I is confuggulated …

Okay, thanks again RG and Zeddy – I’ve got something usable now that’s *almost* exactly what I wanted! I discovered that I don’t need to add an extra column to the results table, if I get the list of month labels by entering them as dates (doesn’t matter what the date is as long as the month is right in each case) and formatting them as “mmmm” – that is, I put “1 1 1” in the cell for January (without the quotes, of course), “2 2 2” for February, etc. Having done that, Zeddy’s formula can be slightly modified to:

=SUMIF($E:$E,MONTH(L2),$D:$D)

And it works perfectly. I still can’t work out how to get XL to pick the month out of the date in column A rather than have to add column E, and have therefore had to add that column but hidden so it doesn’t interfere with the layout of the form.

One other thing to note, if you want to use this, or a variation of it – the extra (hidden) column can’t just be “=Month(A1)” all the way down as for some reason XL reads a blank cell as “1”, which would throw all the January figures, so I ended up using “=if(A1″”,Month(A1),””)”. That’s if, as I am, you’re setting up a ‘master’ that will get filled in over the year, and don’t know exactly how many rows it’ll be yet, not to mention will be blanking and reusing it for next year. I’ve assumed a max of 200 and filled in my formalae down that far.

Once again, many thanks, everyone – although if anyone does know how to get XL to extract the month from the date without having to use an extra column, please do say so! That would make it just perfect!

:clapping:

Hi RG, thanks for this but I must admit I was hoping for something that didn’t include the year – and my layout is per the attached, sorry should have done that in the first place. I can live with the shortened versions of the months (Jan, Feb, etc) in both original and results but would really prefer to have shortened ones in original and full length in results – that’s why I wanted something that had XL recognising that they were months!

36815-woodys

In fact I have just tried to apply your suggestion but it didn’t work – I’ve a feeling it relies on the fact that your months are displayed as numbers, and of course mine aren’t.

Thanks for the suggestion, though.

Thank you, Zeddy, that’s great!

Hi Zeddy, slight misunderstanding here – I need to be able to select a block of cells, several columns by several rows – which will not necessarily be the current region as we have columns of formulae to the right of the data cells that are not to be touched by the sort – then sort by just one column within that block. Re putting blank lines, mine simply doesn’t, as you can see from the earlier posts (and probably this one) – I’ve been trying to remember to put spaces at the end of the paras so when it runs them together it doesn’t do so quite literally, but I’d much rather they stayed as separate paras! Thanks for your help.

Thank you, Zeddy, I think that will do what I want! One question, though – how do you “put the cellpointer in the column you want sorted”? I did it by starting the selection in that column (which for my purposes at this moment, sorting always on the last column, works fine) but what if I was selecting several columns and I wanted to sort by one of the middle ones – how do I put the cell pointer in that column? When I try I just lose the selection! By the way, how does one put a blank line in one of these posts now?!! You used to just press return twice as per a wordprocessor, but I notice that it now runs them together (as you’ll probably see with this para, which will no doubt run into the previous one even though I’ve put returns between them!Thanks again!

I keep trying to edit the above post to make my request clearer, but it won’t accept any changes, so herewith the altered version separately: “Is there any way to write a macro that would tell XL to just sort by the last column of the current selection (ie far right column, whether fourth of four, eighth of eight, or whatever), and largest to smallest? I do get so fed up with having to set up the sort, telling it which column and changing to largest to smallest every time when we have literally dozens of selections to do this to …! Any help would be gratefully and greatly appreciated!”

- Beryl’s suggestion on Word option to be unchecked: O2003 does not have an Advanced set of options but it does have a Print set of options that includes “Allow A4/Letter paper resizing.” It is checked. Not sure what unchecking it will do given subsequent observations since your posting about similar problems in PDF and Excel.

Well, I did say it seemed strange, and even counter-intuitive – but it worked for a similar problem for us.

Try it. If it doesn’t work, re-check it.

What have you got to lose?!

I don’t know if this will help, but we had a similar problem in O2007 recently (the ends of prints were being lost), and it turned out that it was fixed by going to Office > Word Options > Advanced and UNchecking “scale output for A4 and 8.5 x 11″ paper sizes”. Yes, UNchecking it!

I know this sounds weird (and even counter-intuitive!) but it worked – and hey, with Microsoft, if it works, you just give up asking why after a while!

Assuming that a cell name is text, wouldn’t the error problem be resolved if you asked

If ActiveCell.Name = “” then …
or
If ActiveCell.Name “” then … (for the double negative route)

rather than ‘is Nothing’?

I might point out that I haven’t tried it, I’m just curious – for future reference, as it were!

BerylM, the chart is cropped. If you look at the right margin, you will see that the right side of the box and the end of the x-axis have been cut off. Resizing will not display them; using the cropping tool will.

Well, all I can say is, when I opened your example doc, right-clicked on the ‘cropped’ picture, chose ‘size’ and reduced the percentage, the rest of the picture began to appear.

The chart is not being cropped, it’s being resized to match the height of the space in one instance and the width in the other; and where it’s the height, the part of the chart that won’t fit on the righthand side is just not showing. Reduce the size some more and the rest appears.

I suspect you need to decide which of the statements in your macro you wish to be true – at the moment you have three statements, any two of which will work together, but not all three; lock ratio = true and both the height and the width measurements.

Unless every chart you paste is going to be EXACTLY the same height:width ratio every time, if you want to keep your ratio locked (which is of course the desirable course) you need to specify EITHER the height OR the width and let XL/Word set the other according to the original ratio – specify both and unless by chance the chart was exactly that shape to begin with, something’s got to give.

At the moment, you lock the ratio and set the height, then the width on this macro; and I suspect on the other you have them the other way around. Therefore, here, with lock ratio on, the software sets the height, with the appropriate width – then changes the width and has to make the height match. So you have a chart that is the right width (but much shorter). If in the other macro the statements are the other way around, it’ll set the width with the appropriate height, then change the height and makes the width match – giving a chart that is the right height for the space, but far too wide.

I imagine you’ll get the result you want if you remove the height line from the macro, letting it set the width (to match the page) and make the height match, because the ratio is locked, but if you want the other way around then go for it.

Thanks, Gary, that was the switch I was looking for – I just didn’t know what it was called!