Geeks on Campus

Another amazing bgsu blog

Copy Down

Posted by jeggent on August 8, 2017

This is part of the Tuesday Excel Tips series.

If your spreadsheet needs some kind of list or series of labels such as a count, days of the week, days, months, etc. Excel can help you out with that.  As shown in the example below, a count is being entered into the first column.  We only need to enter the first couple of values so that Excel can determine that we increment the value by 1 each time.  Select those values and then place your mouse pointer on the box in the lower right corner of the selection.  Drag the cursor down to the last row you’d like filled and Excel will fill in the values for you.  If you already have data populated in the adjoining rows, you can just double-click that box and it will fill in the series down to your last row of data.  I think you will be pleasantly surprised how often Excel will figure out what values you’d like filled in.  This also works with formulas.  If in cell A1 you have the formula “=B1+C1” you can copy that down to the following rows and Excel will automatically update the references to B2+C2, B3+C3, and so on.

Check out the listing of all the Tuesday Excel Tips.

CopyDownSeries

Posted in Excel | Comments Off on Copy Down

Column Width

Posted by jeggent on August 1, 2017

This is part of the Tuesday Excel Tips series.

Adjusting Column Widths – When you first open Excel all of the columns are the same width.  If the entries in a column are longer than the column is wide you may want to adjust the column width to fit.  Or if you’re like me and have lots of columns, you may want to make some columns small to get more on the screen.

There are LOTS of ways to adjust the width of a column or a group of columns, here are some of them:

  1. Put your mouse pointer between the columns and drag the divider to the right or left.
  2. Double click the divider to automatically widen the column enough to fit your longest entry.
  3. Right click on a column header, choose column width, and specify a value.
  4. Click the upper left most corner (above 1 and left of A) to select the entire worksheet, then double click any column divider to adjust all columns to their longest entry.

Check out the listing of all the Tuesday Excel Tips.

AdjustColumnWidth

Posted in Excel | Comments Off on Column Width

Format Painter

Posted by jeggent on July 25, 2017

This is part of the Tuesday Excel Tips series.

Format Painter allows you to apply the formatting of one cell to other cells.  You just select the cell that has the formatting, click on the Format Painter, then select the cells you want to format.  In the example below, cell A1 is bold and has a bottom boarder.  This formatting is applied to the other column titles using the Format Painter.

Check out the listing of all the Tuesday Excel Tips.

FormatPainter

Posted in Excel | Comments Off on Format Painter

Excel Tip Tuesdays

Posted by jeggent on July 25, 2017

Every Tuesday for the next couple of months I am going to post a tip to help you be more proficient with Excel.  You can bookmark this page as I will add links to each of the posts as they are published.

Here are the posts that have already been published and some future topics:FormatPainter

2017-07-25:  Using Format Painter
2017-08-01:  Adjusting Column Width
2017-08-08:  Copy Down Series/Formulas
2017-08-15:  Highlighting Cells for Quick Info
2017-08-22:  Quick Formulas
2017-08-29:  Identifying/Removing Duplicates
2017-09-05:  Filters

If you have any tips to share, I would love to hear them in the Comments section!

Cheers,

John

Posted in Excel | Comments Off on Excel Tip Tuesdays

File Size

Posted by jeggent on February 19, 2016

This is just a public service announcement to save network traffic and file storage space.

When you run a query to Excel from PeopleSoft it will initially be in an Excel 2010 .xls file format.  The query results that I just download had 39,181 rows and 10 columns of data.  As an xls file it had a file size of 6,733 KB.  Without making any other changes I saved the file as an Excel 2013 .xlsx file and it reduced the file size to 1,756 KB.  That is almost 75% smaller.

Thank you for your attention.  You may now return to your memes and cat videos.

Posted in Excel, PeopleSoft, PS Query | Comments Off on File Size

Query get CLOBed?

Posted by jeggent on October 9, 2015

2339660

Disclaimer: This may only apply to Oracle databases.

I think my favorite PeopleSoft Query error occurs when you are outputting a CLOB field and try to use an aggregate or DISTINCT.

inconsistent datatypes: expected – got CLOB

The messages always makes me think, “Got Milk?”  The two fields that I seem to run into this the most often with are 3C comments and checklist item Long Description.  In PS Query the fields show as “Text” format, but behind the scenes the content is stored in what is called a CLOB field.  You don’t really need to know the details about how CLOBs are stored in the database, you just need to know when it’s a problem with PS Query and what to do about it.

It seems that you can output a CLOB (Text) field in PS Query just fine.  However, if you mark your query as distinct or make any fields an aggregate (count/sum/etc), you will get the above error message.  The workaround is to create a “Character” type expression, use the TO_CHAR function, and specify a length.  Our checklist item descriptions can be pretty long, so I’ve used a length of 900 in the example below.  You then use your expression as a field instead of the CLOB field from the record.  I hope this helps and maybe solves the mystery of the weird CLOB error. – Cheers, John

clob

Posted in PS Query | Comments Off on Query get CLOBed?

Improve name recall with this exercise

Posted by jeggent on April 14, 2015

By me (Own work) [Public domain], via Wikimedia CommonsI was never very good with names.  I tried numerous tricks to improve this skill, but I didn’t seem to get any better.  Saying someone’s name multiple times within the first couple of minutes of meeting them always seemed really weird anyways.  This was an area that I really wanted to improve, so I continued to work on it.

Recently, I accidentally came up with a useful exercise to help me better remember names.  While I am walking to a meeting or just out for exercise, I started looking at people and thinking about who they most look like.  The people you see won’t necessarily look a lot like the people you know, but try to think of who they most look like.  This will cause you to picture the other person and give you time to recall their name.  This regular practice of associating names with faces really seems to be helping.   Similarly, just picturing the people you know will be present at the meeting you are headed to will help you to be prepared to greet them by name.

Best of luck!
John @ BGSU

Posted in Career | Comments Off on Improve name recall with this exercise

What The Font!

Posted by jeggent on March 20, 2015

Did you know that there is a website that will identify the font used in an image?!

It’s true.  If you need to edit an image and need to match the font to add or change text, you can use What The Font to find out which font was used.  You just upload the part of the image that has the text and What The Font will tell you which font was used.  In my case the font was Forte’.

Cheers!

-John

Posted in Resource, Tools, Web | Comments Off on What The Font!

Left Outer Joining Effective Dated Tables

Posted by jeggent on March 18, 2015

This Changes Everything!

A colleague just taught me how to left-outer-join (LOJ) an effective dated table in PeopleSoft Query.  In the past I would only be able to LOJ non-effective dated tables or use views that had the effective date logic built in.  I just looked through my old posts and it doesn’t look like I’ve posted about LOJs before.  So I’ll give a quick rundown of why you would want to use one.

Example

Say you have three different tables for First, Middle, and Last names.  If you just standard joins you will only see results for those records that are present in all three tables.  But what about my dad?  My dad doesn’t have a middle name.  Weird, I know.  Here is some sample data.

First Name
UID First
1001 John
1002 Larry
1003 Joanne
Middle Name
UID Middle
1001 Isa
1002 The
Last Name
UID Last
1001 Coolguy
1002 Cat
1003 Rowling

So using standard joins and connecting these tables on UID, you would get:
1 John Isa Coolguy
2 Larry The Cat

What about Joanne?  That’s right, JK Rowling also has no middle name.  She gets left out because of the standard join.  However, using a left-outer-join would allow you to make sure that she and any others were not left off.  And that output would look like this:
1001 John Isa Coolguy
1002 Larry The Cat
1003 Joanne  Rowling

Cool, but how?

In PeopleSoft Query, when you go to join a record you will get a “Join Type” box.  Typically you would select Standard Join “Join to filter and get additional fields.  To do an LOJ, you would select the other option “Join to get additional fields only” (Left outer join).  An important thing to remember is that if you need to put any criteria on the LOJed table, you need to specify that the criteria belongs to the ON clause of the outer join in the Edit Criteria Properties box.
loj

So that is how to do a normal LOJ, which I was already comfortable with.  The extra complication is with effective dated tables.  Those tables come with special criteria which is also a subquery.  It isn’t as simple as saying that criteria “belongs” to the left outer join like any other regular criteria.  The magic to make this work is to leave that effective date criteria but then to manually add an additional criteria for the effective date and set the condition type to “is null”.  Then change the logical setting from AND to OR and group those two lines with parenthesis.  As such…loj2

I can only assume that I’ve made your life 1,000% better. Well, we both have Jeff M. to thank. He’s the one that taught it to me.  Let me know in the comments if you have any questions about how to make this work.

Cheers.

-John

Posted in PS Query | Comments Off on Left Outer Joining Effective Dated Tables

More of my Best Advice: For Next lists

Posted by jeggent on December 12, 2014

ForNextIf you missed it, I posted my best advice (recording your accomplishments earlier.  As the year winds down and end of year reports need to be written, I stand by this being great advice!

My next bit of advice is to create a series of “For Next” lists.  I use Microsoft’s OneNote since I have it on my desktop, tablet, and on the web.  You should use whatever system you typically have quick access to.  Even if it is paper and pen.

Then start a lists for the people and groups that you regularly communicate with.  For example, I have a “For Next” lists for my boss and all of the committees that I am on.  Then whenever you think of something that you need to bring up the next time you meet with that person or group, you’ll have it handy.  Then after talking about those items I date them and start another list.  Here is what a “For Next” list might like:

Next
End of year evals
New flatbed scanner quote
Personal time on Tuesday

12/1/14
Website presentation ideas
Conference in January

A “For Next” list is useful as a reminder to to get answers or give updates and to keep a running list of when things were discussed.  Keeping for next lists is an easy way to make all of your meetings more productive.

Cheers!
John

Posted in Career, Productivity | Comments Off on More of my Best Advice: For Next lists