jump to navigation

working excel: how to find employees, reporting to someone in a flat and non-descript hierarchy February 12, 2018

Posted by Steven in The-Web, Work-related.
add a comment

Excel got me excited today. This rarely happens so I thought I would share my excitement and my learnings about this great and simple data crunching tool.

The Problem

It’s actually not so hard to explain: Imagine you have an spreadsheet list of employees (so far so good) and in columns further down the hierarchical reporting line of these employees is shown. So in the very simple example (filled with totally randomly generated data!) – column A shows the employees email address and column B the direct boss. Column C would contain his boss’s boss and so on.

As outlined in the above picture the relationships between the bosses levels are non-descript. Meaning that boss “Jon Doe” can appear on the first boss level or on the third and so on. I.e. it’s not clear from the data how the hierarchy works in every case it is just given in these columns on an per employee basis.

Now the core of the problem would be: Imagine you would be given a list of 5 bosses. Now we need to develop a nice and efficient report on this spreadsheet data, which shows all employees reporting in any hierarchical way (i.e. through bosses in the middle) to these 5 bosses. How do we do that (again remember – each of these 5 bosses could show up anywhere in column B, C or D)?

Building Blocks of the Solution

  • First of all I’d like to mention once again: This is sample data (i.e. I went to a place called randomlists.com and generated data)
  • I inserted a real table into the Excel spreadsheet (this makes relative cell links more readable and so on)
  • In order to make later steps like the pivot step work nicely I’ve inserted an artificial name counter (a column filled with 1)

Key steps to build the solution:

  • Do a string addition of all the bosses (just add up the boss-strings to one bigger string)  (my formula: =[@BossLv1]&”,”&[@BossLv2]&”,”&[@BossLv3] )
  • Make sure you can search for the relevant bosses in the hierarchy. I did this with a kind of parameter and an if clause that marks the boss I’m looking for (again this may seem strange but it helps in one of the next steps when it comes to the pivot)  (my formula:   =IF(ISNUMBER(FIND($I$3;[@AddedString];1));”Analysis Found”;”no”)  )
    • Note that in the above you need to catch the number with ISNUMBER otherwise the find function doesn’t help if something is not found.
  • Insert a Pivot. Define the Row data to show the added string of bosses and the users that show up in these string-added-hierarchies. Set a parameter on top of it to only show the boss you’re looking for (AnalysisFound flag is the parameter). And Add up the counter field in the Value section of your pivot.
  • Voila that’s it: and if you now change your analysis boss and refresh the pivot it shows you exactly how many users are attached to a particular boss’s hierarchy

I’ve attached my sample:  Test-pivot01 :-)

Advertisements

Tipp for working with filters / text-filters in Microsoft Excel December 15, 2009

Posted by Steven in Work-related.
add a comment

For some database related work / spreadsheet work I had to find a way in Excel’s text filter system to escape characters. Specifically: it you need to know exactly what to type in when you want to look for example for the character “?” in an Excel column or range.

Here is how you do it (I explain it on the example of Excel’s auto filtering):

  • select the range or columns
  • Ribbon Data > hit Filter button
  • Click the little down triangle on the column / header of the column you need to filter
  • Enter “Text filters” e.g. “Contains”
  • In the dialogue ‘contains what’.. enter ~ ? for example (to search for actual ? characters

Configuration Tips for Windows XP – Accounts and User Restrictions November 23, 2009

Posted by Steven in The-Web, Work-related.
add a comment

Microsoft’s Windows XP is still a widely used operating system. Especially in the corporate world many colleagues still see the Windows XP desktop as the basis of their daily work.

Now if you – like me – are fortunate enough to have administrative access to your Windows machine then you can use some tweaks to the registry and some configuration tools to make your daily work life a little bit easier. In this post I would like to summarize a couple of good sites which give tipps for configuring Windows XP on a fairly deep level:

http://www.kellys-korner-xp.com/win_xp_passwords.htm

Instructions on how to stop / shutdown Symantec Endpoint Security Services November 13, 2009

Posted by Steven in Work-related.
add a comment

tostopsmcservice

On corporate PCs you may often find components for the corporate virus protection. One very popular product range is the Symantec Endpoint Security Suite.

It offers protection against viruses, malware and spyware. It also provides machines with a desktop firewall and network policies.

On the flip-side these comprehensive security suites take up lots of system resources. I would even go that far to say that these products may get into the way of completing business critical tasks. While I would not suggest to compromise companies’ security I do encourage everyone to explore ways to temporarily free up more system resources.

For the specific case of disabling Symantecs Endpoint Security Suite’s  Services completely (including the sticky Symantec Client Management Service) I would suggest downloading the Sysinternals Process tools for current Windows platforms or for Windows XP and to follow the instructions embedded in the screenshot above.

For Symantec SMC specifically the command:

Run:  smc -stop

seems very useful when you are trying to get rid of it.

Very Useful Illustrator Guides November 7, 2008

Posted by Steven in The-Web, Work-related.
add a comment

I came across this slightly outdated website. It has excellent visual guides to older versions (or basic concepts) of Adobe Illustrator.

http://www.developingwebs.net/illustrator/combiningshapes.php

combining_shapes

 

PS: I used to call the related Application “AI” but I now realize that the term “AI” has totally different meaning nowadays.

Windows XP Modifying Default Logon Screen November 3, 2008

Posted by Steven in The-Web, Work-related.
add a comment

The environment that loads during your Windows XP Logon procedure is partly derived from settings that the windows registry stores in a default user profile stream.

You can modify these registry values in order to change the appearance and the bahavior of the logon screen. One example noted here is the desktop settings folder to change the background image for example:

HKEY_USERS\.DEFAULT\Control Panel\Desktop

The Future of Securitization October 16, 2008

Posted by Steven in Work-related.
add a comment

… was analyzed by the Frankfurt based CFS – Center for Financial Studies. I don’t really think the fellow gentlemen have discovered so much new stuff. But thanks for writing it down!

http://www.ifk-cfs.de/fileadmin/downloads/publications/wp/08_31.pdf

http://www.ifk-cfs.de/index.php?id=1462

Open Office 3.0 – 3 Major Platforms – Available Today October 13, 2008

Posted by Steven in In-Media, The-Web, Work-related.
add a comment

For me it is the Gmail of Office Applications. It’s easy to use, free and extremely straight forward. I would say much more straight forward than the big Microsoft Office 2007. As an average but regular user of the Microsoft Package since Winword 2.0 came out – I was really disappointed by the ribbon concept introduced in Microsoft Office 2007.

Open Office is different. It is like green IT. Several enthusiasts have worked really hard to create a free and powerful office suite with all the major features. Unfortunately the Excel competitor can only cater for 1024 columns. That is definitely not enough. But: The OO package is available for free as a lightweight 128MB download for Mac OS X, Linux and Windows. I am especially curious to try it out on Mac OS X.

Here is a summary of download resources as the official page seems to be down at the moment:
http://openoffice.mirror.aussiehq.net.au/stable/3.0.0/
http://scott.yang.id.au/2008/10/openofficeorg-30-download-it-now/

Abscheuliche Praktiken Eines Vormals Geachteten Berufsstandes June 20, 2008

Posted by Steven in The-Web, Work-related.
add a comment

Sehr gute Zeitschriftenartikel und Nachrichtenbeiträge machten mich darauf aufmerksam, dass im Internet speziell im deutschsprachigen Raum unglaubliche Praktiken von Anwälten unterstützt werden. Dubiose und abscheuliche Menschen versuchen an persönliche Daten wie E-Mailadressen und Anschriften heranzukommen. Dann wird ein dubioser Vertragszusammenhang konstruiert und über Drohkulissen eine Inkassoforderung gestellt. Die Formulierungen sind aberwitzig die Methoden sittenwidrig.

Meist ist der kleine Personenkreis bekannt. Anwälte, die zu tausenden sogar über automatische Systeme mit Rückmeldefunktionen (wurde die Mail gelesen, geöffnet etc. – um an weitere persönliche Daten zu kommen!) diese Horror-Inkasso-Mails und Briefe verschickt haben sind unter Anderem:

Katja ####
Olaf ####
Boris ####

Firmen auf welche acht gegeben werden muss:

NetContent Ltd.
Katarína Dovcová
Michael Burat
Online Content Ltd.
Andreas und Manuel Schmidtlein

Persönlich und aus den genannten Artikeln kann nur eine Warnung ausgesprochen werden wenn diese Namen in einem Impressum oder in einer Mail auftauchen.

Diese Literatur ist zu empfehlen:
Web-Abzocke
Mailing-Dienstleister stoppt Mahnungs-Spam
Abo-Abzocker mit neuer Mahnungswelle

Bad Habits for Graphic Designers… June 12, 2008

Posted by Steven in Freetime, The-Web, Work-related.
add a comment

It’s been tough. After the most wonderful weekend in London since a long time a tough week cought up with me. Not sleeping enough, coping with emotional losses, with great heat and some unbearbable sports mass events left scars on my shape.

That’s why sometimes you need a break. This link is sort of funny. That’s all:

http://www.youthedesigner.com/2007/11/19/25-bad-habits-of-graphic-designers/

Embedding Google Maps in WordPress.com Posts May 2, 2008

Posted by Steven in The-Web, Work-related.
add a comment

This seems to be possible with the recently introduced Google Maps – Static Maps API. You can get full coverage of this embedding here:

http://daniel.fallenste.in/2008/03/28/howto-google-maps-on-wordpresscom/

Transfer Calendar Data Between Google Calendar and Microsoft Outlook April 10, 2008

Posted by Steven in In-Media, Work-related.
add a comment

Outlook is quite a grown application and now that we have Outlook 2007 and we recently experienced the latest update wave we may actually start using it productively.

But what do we experience in our increasingly digital and connected world? We need to keep multiple calendars in sync. I found that Google Calendar is a good connection point to do that. With Google Calendar you can import several calendar file formats and you can display calendar feeds. Google also allows you to share any calendar that you have writing access to with other and to export all calendars as files. The Google Help pages give very good explanations of the ever growing features.

Also Microsoft itself put together a nice guide how to make Outlook 2007 work together nicely with Google Calendar. I bet with the Yahoo acquisition we will then only be able to import yahoo! calendar which would be a shame. Anyway – right now you can still read the nice Microsoft guide including links to the Google 2 way synchronization tool.

http://office.microsoft.com/en-us/outlook/HA101674951033.aspx

Google Calendar Sync by Google (Help and Download)

Transfer Contacts from Outlook to GMAIL:
http://office.microsoft.com/en-us/outlook/HA011489021033.aspx

WPKube

technology and life hacking

CodeinWP Blog

Designer's guide to WordPress

Elegant Themes Blog

technology and life hacking

WPBeginner

technology and life hacking