Replies

Another method is to put a ^ in front of the formula, copy it to as many cells as you want, then block the cells and use find and replace. Find the ^ and replace it with nothing.

Hans,

My dates are generally dd/mm/yyyy. My PC is set up as English(Australian). When I click on them and check the format it is general. When I run the macro the attachment is what happens. If I put my cursor in a cell that didn’t convert and press F2, then it switches to be right aligned and format dd/mm/yyy, as do the dates 12 and below.

If I block the cells and change the format to date I still have some right and some left aligned. The left aligned ones will not group in pivot tables unless I go through each cell and hit F2. When working with 45,000 rows of data, that is not practical.

As I’m not the only person who works with data from this source, I think the only solution will be getting the people who run the database, to have it extract the data without the sticky spaces. I’ve been asking for that and so far they have fixed some of the date columns but not all.

It just puzzles me at to what is happening. I can’t understand why the dates would convert to the American format when I use the Australian format.

capri

Hi,

I’ve run into a problem using the macro to get rid of sticky spaces. As you can see from the attached, it seemed to fix dates where the day was 1 to 12, but anything with a date 13 to 31 it did not fix. When I check the format, the ones that were fixed are formatted as dates and the others as general. Does any one have any ideas on what is happening or a possible solution?

Thanks
capri

Hi,

I was explaining to another person this morning about Han’s macro to remove sticky spaces. We also discussed another problem I have with a different extract. In the other extract, the first approx. 100 dates or number format properly. The remainder look like numbers or dates, but instead of aligning on the right hand side of the column, they align on the left and there is a small red triangle in the top corner. Once on this site someone suggested that if you had the cursor in the cell and hit F2, the cell would correct which it did. I could not block these columns and change the format. It occurred to me that perhaps there was a sticky space at the end of the number or date. I just got to work, tried Han’s macro and presto, it worked like a charm. So now I have 2 uses for that handy macro.

Thanks again Hans.

Hans,

I don’t know how I can ever thank you for the macro. There are many problems with the new method of data extracts and what they contain, but this was the biggest one, impacting on so many calculations.

I was searching on sticky spaces rather than empty spaces.

As the Aussies say “Thanks Heaps”.

capri

Thanks Hans for the explanation and sample.

capri

For the most part I want to adjust the column to the approximate width of the contents, so when scrolling through or filtering I can see what I am looking for. Eg Surname column needs to be at least 8.5 wide, while gender column only needs to be 3.5 wide as it contains either M or F. The previous program exported everything at 8.43 and I just dragged the edges of a column to widen or shrink it’s size. The majority could be left at 8.43. With the new program exporting all columns at 14 wide, it just makes viewing to awkward as you are continually scrolling and you can’t see as much on the screen at one time. I could block all the columns and resize to 8.43 and continue as I did in the past.

I just thought if I could find a way to get them all right once, and just copy that format each time I take an extract, it would make life so much easier. Generally I find that if you think of something you are doing as boring and repititious, then there is an easier way to do it. It’s just a question of figuring out what the easier way is. This forum is great, because I’ve learned so many time saving techniques just from reading other people’s posts. I was hoping someone else had already had and solved a similar problem, and could let me know how they did it.

capri

Thanks Andrew and Steve,

I guess I will try opening one and recording a macro as I re-size each column, then applying it to each new extract. I’m not very good with VBA, just haven’t had the time to learn much, but hopefully the macro recorder should work. I can alter other’s macros to suit my data, but don’t have enough understanding of what the various commands do to create my own. I don’t tend to use the macro recorder much as it tends to pick up cell addresses, which in most cases I don’t want, but for this purpose, should not impact on the result.

In the new program all the columns are 14 and I want to make most much smaller. Sometimes I just draw a blank at figuring out what to do, (a senior moment). Thanks for pointing me in the right direction.

capri

Thanks Hans.

That answers why Excel on my home PC runs much quicker than at work. I thought it was because I had a faster processor, but it’s because I have Excel 2003.My big problem will be trying to get the upgrade as I work in government and they have standard software which it won’t be easy to convince them to allow just me to have an upgrade.

capri

Thanks mbarron,

Your formula works great. As a bonus I won’t have to change it each month.
Thanks for explaining about Excel viewing the date as a number. I frequently have to do calculations based on dates, and I should be able to adapt this formula for other uses as well.

capri

Thanks Hans,

That’s exactly what I was looking for.

capri

Thanks for the various suggestions. It’s always nice to have more than one method. I’ve certainly learned a few new things, about the DATEIF function and using the YEAR/TODAY functions in formulas.

The formula that Mbarron gave which starts =IF(AND has me wondering how you would know to use that combination in a formula. I understand creating IF statements and nesting them, but don’t understand that combination and how it works. Could someone suggest where I might read more on that combination or other ways to make formulas more flexible by combining functions in a similar manner.

capri

Thanks so much Mbarron

The formula is exactly what I need. I am working on rolling 12 months data, so the dates need to change each month, and this will allow me to easily access the information i need.

Thanks Hans,

That’s exactly what I was looking for. Once I set it up, I’ll just copy and paste each month, use find and replace to increase the size of the data extract, and I’ll have the numbers I need to use, without having to pivot the data, and hopefully my file sizes won’t be as large. I regularly lose my work having too many large files with pivot tables open at once, as my PC struggles to handle the data. I can see a lot of potential with this feature of eliminating lots of data I usually pivot. My IT area has been no help with my problems, so anything I can do to save file size makes my life easier.

capri

Hans,
Each month I pull a data extract which has year to date information (our year run July to June). Sometime people want to see the month to month information and other times they just want year to date.
Pivot tables are easy, and that is what I do now, however I thought this method would save time each month, if I just copied and posted the sumproduct tables (replacing the previous final row of data with the current final row). It would save re-doing pivot tables each month. It might also save on file size as pivot tables tend to bloat my files. My monthly extracts run from 5,000 to 60,000 rows of data depending on the month.

Attached is a sample of what I have and what I would like. I just need to know how to construct the formula in the shaded green cells.

capri