Excel Question #46

[Kevy Baby]

Thanks Alex, I will check that out: I need to learn pivot tables.

I have never taken any classes on Excel (and it shows) - almost everything I know has been due to a need to know how (I am currently learning how to use lookup tables).

[Alex]

Same here. I suck at pivot tables and generally brute force my way through things when the need would otherwise arise. But a count in a single column is pretty straightforward.

[Moonliner]

Originally Posted by Kevy Baby

1. Get a list of all entries in a column (in other words, if I have 50,000 records and column H has a listing by state, I want a list of the states covered in this list [obviously not all 50 states]).

2. Create a list of records with birthdays in a certain month (in this case, April).
[list=1][*]Convert the Birth Date column to text. Note that this data came to me with the date already in a text format listed at YYYYMMDD (no dashes or slashes).

When all you have is a hammer, every job looks like a nail.
-- Unknown.

Seriously dude, import this **** into a database.

#1 Then becomes: Select DISTINCT State from Your.bd

#2 Would be: Select * from Your.db where mid(datefield,5,2)='04'

[Kevy Baby]

Originally Posted by Moonliner

#2 Would be: Select * from Your.db where mid(datefield,5,2)='04'

I found a better to resolve for #2: I had the client provide me with records already suppressed to the month of April

Originally Posted by Moonliner

When all you have is a hammer, every job looks like a nail.
-- Unknown.

Seriously dude, import this **** into a database.

Well, yes, this might be a better tool. But then I would have to purchase and learn said new tool. If 95% of what I need to transport can be covered by a pickup truck, I am not going to buy a semi-truck and get a class A license for that other 5% - I'll just make a couple of extra trips as needed.

Besides, I got to learn how to make a lookup table today!

[Moonliner]

Originally Posted by Kevy Baby

I found a better to resolve for #2: I had the client provide me with records already suppressed to the month of April

Well, yes, this might be a better tool. But then I would have to purchase and learn said new tool. If 95% of what I need to transport can be covered by a pickup truck, I am not going to buy a semi-truck and get a class A license for that other 5% - I'll just make a couple of extra trips as needed.

Besides, I got to learn how to make a lookup table today!

Purchase is not a problem, if you have excel you probably already have Microsoft Access in house. That's all you would need. If not, MySQL (Which runs this site) is free for the downloading albeit with a somewhat higher learning curve than Access.

[Kevy Baby]

Originally Posted by Moonliner

Purchase is not a problem, if you have excel you probably already have Microsoft Access in house. That's all you would need.

Since I am, for the moment, still running on Office 2003, I have no Access. Alas, much to my chagrin, I am updating to Office 2007 Professional (the disk was just placed on my desk), so I have it will soon.

I have been reticent to move to 2007 as it seems that everything has been moved in Excel. But I need to be only three years out of date and not seven (we do get a free upgrade to 2010 as well).

[Alex]

One question, are manipulations something you have do repeatedly over time in the same way or are they always one offs?

[Moonliner]

Originally Posted by Kevy Baby

Since I am, for the moment, still running on Office 2003, I have no Access. Alas, much to my chagrin, I am updating to Office 2007 Professional (the disk was just placed on my desk), so I have it will soon.

I have been reticent to move to 2007 as it seems that everything has been moved in Excel. But I need to be only three years out of date and not seven (we do get a free upgrade to 2010 as well).

I ran the beta of Word 2010 for awhile. I was less than impressed it struck me as a big step backwards in usability. Be sure to take a long look at it before you leap, free might be too high of a price to pay.


As for 2007 the "Ribbon" interface is not that bad. Just remember to look at the tabs across the top and think which one best fits what you are trying to do. Chances are that's were you will find it.

[Kevy Baby]

Originally Posted by Alex

One question, are manipulations something you have do repeatedly over time in the same way or are they always one offs?

I usually only ask if I think that it is something I MIGHT need to use again in the future. If I know something is one-off, I usually just kluge through it. For example, while I didn't come here for learning about Lookup tables and the project I used it on really didn't need it, I still used the opportunity to learn how to use them as I have needed them in the past and I am sure I will in the future.

But for the most part, most of what I do is similar in nature, but not the same thing. If there is something that needs to be done repeatedly, I use programmers at my vendors for that. Also, I need to be careful about using anything more sophisticated than Excel as I often have to share the information with others who struggle just with Excel. In other words, I have to keep what I do user friendly.

I also ask here because I just like to learn something new.

[Ghoulish Delight]

I should write a routine that increments the thread title every time Kevy posts a question.