MS EXCEL Help

[Saratoga NX-3842]

Ok, here's what I am trying to do. It may a simple matter.

I run a report every morning with about 3000 lines and about 14 columns. I need fast way to weed out criteria, moving lines with a certain # in colum c to a new sheet and lines with something in colum d to another and deleting lines that do not contain certain criteria.


Maybe I need a macro for this, I'm sure how to do it, any help would be welcome.

 

[Trekker4747]

Ok, here's what I am trying to do. It may a simple matter.

I run a report every morning with about 3000 lines and about 14 columns. I need fast way to weed out criteria, moving lines with a certain # in colum c to a new sheet and lines with something in colum d to another and deleting lines that do not contain certain criteria.


Maybe I need a macro for this, I'm sure how to do it, any help would be welcome.

I'd love to help, but I don't have the faintest idea what the hell you're trying to do. Mind giving us a clearer, more precise, example?

 

[Saratoga NX-3842]

Ok, here's what I am trying to do. It may a simple matter.

I run a report every morning with about 3000 lines and about 14 columns. I need fast way to weed out criteria, moving lines with a certain # in colum c to a new sheet and lines with something in colum d to another and deleting lines that do not contain certain criteria.


Maybe I need a macro for this, I'm sure how to do it, any help would be welcome.

I'd love to help, but I don't have the faintest idea what the hell you're trying to do. Mind giving us a clearer, more precise, example?

Ok, here goes:

Need to select out only items that have say a "0" in colum B and move it to a new sheet.

then i need to move data with "LE" "LF" "LR" in column c and move it to a new sheet.

then need to move lines that have "Alaska" in column d and move to a new sheet.

Is there an automated way to do this?

 

[Trekker4747]

Ok, here's what I am trying to do. It may a simple matter.

I run a report every morning with about 3000 lines and about 14 columns. I need fast way to weed out criteria, moving lines with a certain # in colum c to a new sheet and lines with something in colum d to another and deleting lines that do not contain certain criteria.


Maybe I need a macro for this, I'm sure how to do it, any help would be welcome.

I'd love to help, but I don't have the faintest idea what the hell you're trying to do. Mind giving us a clearer, more precise, example?

Ok, here goes:

Need to select out only items that have say a "0" in colum B and move it to a new sheet.

then i need to move data with "LE" "LF" "LR" in column c and move it to a new sheet.

then need to move lines that have "Alaska" in column d and move to a new sheet.

Is there an automated way to do this?

"Automated" I do not believe so. And someone is going to have to likely come and double-check me on this, but I think this is how you would do it (and I've only self-taught myself XL.)

In your new sheet: For the column/cell you want the information type "=" then select the column in your other sheer. This should print the "path" to the column into the new worksheet (which will be something like C:\windows\username\documents and settings\my documents\filename.xls column name.

Now do this change the formula to "=if [path] = "0" and that *should* cause that new worksheet's column to move data over that contains zero.

You'd do this the same for our other examples with commas seperating each instance "=if [path] ="LE", "LF", LR""

I think this will do it. If not it's in the right direction.

Again, I'm sure someone well more versed in more complicated XL macros will be along to correct me.

 

[Chaos Descending]

You'd definitely be better off running this as a VBA macro.

 

[Charlie Kelly]

You can easily do this using VBA if you know how to use it that is. I don't think it would be possible to simply record a macro to do something so specific but you could record macros to give you clues to the type of code to use.

Charlie

 

[Saratoga NX-3842]

You'd definitely be better off running this as a VBA macro.

What is VBA and where can i find it?

 

[zephramc]

VBA is "Visual Basic for Applications" and should be part of your Excel installation. As far as learning it, Google is your friend.

 

[USS KG5]

Ok, here's what I am trying to do. It may a simple matter.

I run a report every morning with about 3000 lines and about 14 columns. I need fast way to weed out criteria, moving lines with a certain # in colum c to a new sheet and lines with something in colum d to another and deleting lines that do not contain certain criteria.


Maybe I need a macro for this, I'm sure how to do it, any help would be welcome.

I'd just use autofilter and a bit of copy and paste - you would be able to do this hundreds of times in the time it will take before you can code effectively in VBA to do something this precise, unless you are already a programmer of course.

A spreadsheet this size is heading towards database in my mind (as someone who uses databases a lot) so if you have a lot of these to work with have you looked at knocking up something in access? Then you can run queries on the table to select everything with value X in field Y, and do everything at the push of a button.

 

[TremblingBluStar]

Need to select out only items that have say a "0" in colum B and move it to a new sheet.

then i need to move data with "LE" "LF" "LR" in column c and move it to a new sheet.

then need to move lines that have "Alaska" in column d and move to a new sheet.

Is there an automated way to do this?

I could be wrong, but I think you can simply record a macro with all of this. Simply start the macro recording, search for a value, select the entire column, cut, and paste it into a new sheet.

Having said that, this is the perfect instance where I wish people would stop using Excel for huge tables like this and start using a proper database management system.

 

[USS KG5]

Having said that, this is the perfect instance where I wish people would stop using Excel for huge tables like this and start using a proper database management system.

Amen. Problem is a lot of small businesses cannot afford to employ a full-time IT professional who can effectively use a DBMS, despite the fact a lot of them have Office Pro licenses oozing out of their ears.

I'm personally very interested in the future of OO Base - it is very simple in its current incarnation but has to grow.

has anyone used it as a front-end for SQL Server through ODBC??

 

[Dojhar son of Bob]

Nonsense. 3,000 lines of data is nothing. You don't need a database for that. When you get into the tens of thousands, you need a database. Excel can easily manage 3,000. Certainly, a database is more efficient, but an Excel solution is much easier for the casual user and much easier to teach to the next person who takes over your job (assuming that situation might arise).

Here's my suggestion.

1. Look up how to use the advanced filter (it's easy).
2. Make a copy of your original worksheet.
3. On the new worksheet, use the advanced filter to create your new list (copying it to a separate range to the right of the original data).
4. Delete all of the columns to the left of the new list you just created.
5. Repeat 2-3 for each set of criteria.

Once you figure out how to do the above, record it as a macro. No need to learn the VBA. Something that simple (assuming your column set and criteria don't change) should be very recordable.

 

[USS KG5]

Certainly, a database is more efficient,

Which is all anyone is saying.

but an Excel solution is much easier for the casual user and much easier to teach to the next person who takes over your job (assuming that situation might arise).

Well that depends I guess - but as a general rule, yes.

 

[Dojhar son of Bob]

Certainly, a database is more efficient,

Which is all anyone is saying.

I disagree. I took "stop using Excel" to be more of an absolute statement. I like databases, but a database is overkill when you're only dealing with a smattering of data (and yes I consider 3,000 records a smattering). You don't need the 2-horsepower garbage disposal when all you're ever going to grind up is leftover soggy cheerios.

 

[USS KG5]

I disagree. I took "stop using Excel" to be more of an absolute statement. I like databases, but a database is overkill when you're only dealing with a smattering of data (and yes I consider 3,000 records a smattering). You don't need the 2-horsepower garbage disposal when all you're ever going to grind up is leftover soggy cheerios.

I'd say anything over a few hundred benefits from the ability to run complex queries using SQL, if we want value x from field a, value y form field b and value z from field c you can do this with an advanced filter in Excel or an SQL statement in Access, my preference is for SQL.

I also find with SQL you only need to return the information you want - so with excel you start with everything and filter it down where with SQL you can just "select... from... where... order by..." and you are away...

 

[Dojhar son of Bob]

I'd say anything over a few hundred benefits from the ability to run complex queries using SQL, if we want value x from field a, value y form field b and value z from field c you can do this with an advanced filter in Excel or an SQL statement in Access, my preference is for SQL.

I also find with SQL you only need to return the information you want - so with excel you start with everything and filter it down where with SQL you can just "select... from... where... order by..." and you are away...

Absolutely, but we're not talking about a complex query here. We're talking about a user who wants to separate a block of data into discrete lists on separate worksheets based on some pretty basic criteria. It's almost more work to take it out of Excel, build multiple SQL queries to generate the discrete lists, then put those lists back into separate worksheets in Excel (assuming that's where you still want them). Never mind the whole "learn SQL" step involved if one is not already a user (back to my casual user comment).

 

[Dojhar son of Bob]

I'm actually more curious to know what you all think of my Excel solution. Would you do it differently?

 

[USS KG5]

I'm actually more curious to know what you all think of my Excel solution. Would you do it differently?

I'd probably use the autofilter option, filter the lines using the autofilter then copy and paste the results into seperate sheets on the same workbook or a different workbook if required. Your solution seems perfectly decent though.

Though I still think if returning filtered data is required SQL can be better. If this is for presenting to management a nice Access report with the company logo at the top can go down very well. Access also offers a very good GUI for building queries, so the "Learn SQL" bit is not required.

 

[Dojhar son of Bob]

As much as I love Access, the reports drive me nuts. They are completely designed for the purpose of printing them out, as opposed to e-mailing them to someone. How freakin' backward is that? You can export reports to Word but you lose half the formatting. I think you can export them to PDF files, but that's just an electronic piece of paper, which also offends me. I like something I can at least copy and paste somewhere else, but prefer something you can link to, like an Excel spreadsheet.

Really, 99% of presentations or reports where I work are done via PowerPoint, another imperfect program, so Access reporting is moot. Access is an analysis tool only, which is what it ought to be.

 

[USS KG5]

As much as I love Access, the reports drive me nuts. They are completely designed for the purpose of printing them out, as opposed to e-mailing them to someone. How freakin' backward is that? You can export reports to Word but you lose half the formatting. I think you can export them to PDF files, but that's just an electronic piece of paper, which also offends me. I like something I can at least copy and paste somewhere else, but prefer something you can link to, like an Excel spreadsheet.

You can export reports to excel usually with excellent results to share the data - I often develop formatted reports for work which can be printed to paper or PDF for presentation or exported to excel for manipulation.

We supply a PDF to one of our customers every day based on an Access report, its great - they print it out and we have our logo all over our customers offices

The default formats provided are DREADFUL, but switch all the fonts to something san serif and simplify the lines etc and you can really make something professional and effective.

Really, 99% of presentations or reports where I work are done via PowerPoint, another imperfect program, so Access reporting is moot. Access is an analysis tool only, which is what it ought to be.

Access is a very good tool for presenting pure data with some pretty formatting, although obviously for actual presentations does not come near Powerpoint.

As far as I use Access I use it a lot as a front-end for SQL Server 2005, although sadly ADPs are on the way out.

This allows me to use SQL Server Management studio for my views and procedures (great tool) and just use access to create forms and reports, while leaving a data infrastructure in place for a new executable or web based front-end as my systems develop.