Solved

Vlookup or Pivot Table

Posted on 2011-09-20
18
596 Views
Last Modified: 2012-06-21
Hello,
I've been tasked with something that not really in my area of expertise.  I'm primarily a database developer.

I need to provide the following information from the details tab of the attached workbook
1. A list of the applications included in the Application Retirement project      
2. A list of the servers that are impacted by the Application Retirement project      
3. The list of servers that could be removed from the Bank's inventory if the applications listed were retired.      
4. What are the # of servers and AITs reduced per LOB?      
5. What is the impact of server reduction per location?      
6. Show the impact to the Data Center Closure Initiative for 2011 vs. 2030      
7. The manager would like an easy way to swap out the data in the Details tab and refresh the analysis      

Now I've worked with pivot tables but I'm trying to decide if I need to go the vlookup route.  Perhaps I should create multiple tabs on the worksheet.  I'd like some advice on the best approach to take. I can do this via a database approach, but the manager wants an Excel Based approach




Project-Impact.xlsx
0
Comment
Question by:chtullu135
18 Comments
 

Author Comment

by:chtullu135
ID: 36570271
It's not homework.  The instruction sheet is a explanation that I received from the manager.  So I'll thank you not to accuse me of submitting homework.  By the way, if you had BOTHERED to read my questiion more CAREFULLY, you would have noticed that I was asking for advice on the approach to take NOT ON THE CODING.
0
 

Author Comment

by:chtullu135
ID: 36570292
" I'd like some advice on the best approach to take"  NOTE:  I SAID ADVICE!!
0
 

Author Comment

by:chtullu135
ID: 36570331

To Teylyn:

Do you have any more comments you'd like to share with me?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 36570592
Well, seeing that the objective is data aggregation and summarising, and keeping in mind that Vlookup is designed to return a single value from a list, I'd hazard a guess that Vlookup will not provide a suitable approach, whereas with pivot tables, the data can quickly be sliced and diced in many ways.

cheers, teylyn
0
 

Author Comment

by:chtullu135
ID: 36570604
That's the solution I came up with and am implementing.  When I complete it, I'll post it.  Do me a favor. Don't comment on it.
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 36570764
I suggest creating a Table using the Insert...Table menu item on the Excel ribbon. You can then create multiple PivotTables using that Table as the source. If your manager changes the data, then you would just refresh each of the PivotTables to see the effect.

It may be that I'm not understanding the ground rules for this assignment, but it appears that some servers are running more than one app--and that not all of those apps are on the retirement list. If so, the server cannot be retired. To account for this possibility, I added a column to the table with a formula like:
=B2-COUNTIF(Table1[[#All],[Server Name]],A2)                  'I called my table Table1. Note how this formula uses the table name & header label to refer to the field

I also added a column for the retirement year, using a VLOOKUP formula:
=VLOOKUP(C2,'Data Center Closure Targets'!$A$2:$B$33,2,FALSE)

Using this groundwork, you can then build separate PivotTables to answer each of the questions. I suggest building these PivotTables each on a separate worksheet.  You will be putting the Server Name in both the Row and Value fields. If you put LOB or App. AIT Code to the left of the Server Name in the Row field, then you get a listing (and count) of servers by LOB or AIT.
0
 

Author Comment

by:chtullu135
ID: 36570806
Thanks byundt:,

Actually I've just completed doing exactly that.  My next step is to see if I can create a section on the details pages called for want of a better word "Analysis Results" .  Analysis Result would draw its data from each of the pivot tables.  I envision after a user makes a change in the details section, the user could refresh the workbook and see the results in the Analysis Results sections
0
 
LVL 81

Expert Comment

by:byundt
ID: 36571036
If the user makes a change in the Details worksheet, then each PivotTable needs to be refreshed. You may want to record a macro to perform the refresh on one of the PivotTables. You can then clone the recorded code for each of the other PivotTables.

Brad
0
 

Author Comment

by:chtullu135
ID: 36571043
I wonder if I can just put a command button on the details worksheet so that when it is clicked I could execute code to refresh each pivot table
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36571150
>>Zones: SQL Server 2008, Microsoft Excel Spreadsheet Software<<
So I gather you are not looking for a T-SQL solution.  If not, then please request that the SQL Server 2008 zone be removed.  It does not apply.
0
 

Author Comment

by:chtullu135
ID: 36571263
Sorry about that,  I hadn't noticed.  I'll take care of it
0
 
LVL 81

Expert Comment

by:byundt
ID: 36571441
You can put a button on the Details worksheet to call a macro. I used one from the Forms toolbar, and put the macro in a regular module sheet.
Sub RefreshPivotTables()
Dim ws As Worksheet
Dim i As Long, n As Long
For Each ws In ActiveWorkbook.Worksheets
    n = ws.PivotTables.Count
    If n > 0 Then
        For i = 1 To n
            ws.PivotTables(i).PivotCache.Refresh
        Next
    End If
Next
End Sub

Open in new window


Instead of using a button, I could alternatively have called the macro whenever the user left the Details worksheet after updating it. Such a Worksheet_Deactivate event macro would go in the code pane of the Details worksheet, and would look like:
Private Sub Worksheet_Deactivate()
RefreshPivotTables
End Sub

Open in new window

Note how the event macro calls the same sub as the Forms toolbar command button.

The attached workbook shows both methods in action.


Project-ImpactQ27318181.xlsm
0
 

Author Comment

by:chtullu135
ID: 36574330
To byundt:
You wrote the following
=B2-COUNTIF(Table1[[#All],[Server Name]],A2)

I'm not sure but unless I'm not understanding something shouldn't that be  a DCount function since the arguments for CountIF is
CountIF(Range, Criteria).
0
 
LVL 81

Expert Comment

by:byundt
ID: 36574392
I built the COUNTIF formula by selecting cells A1:A4431. The formula wizard then translated that into a table reference as posted (and as shown in my sample workbook).

DCOUNT would require a different (and more cumbersome) layout, with the filter criteria appearing above the data. I never use database functions like DCOUNT because the alternatives are just as capable and easier for most Askers to understand.
0
 

Author Comment

by:chtullu135
ID: 36574433
Thanks for the clarification
0
 

Author Closing Comment

by:chtullu135
ID: 36574530
Thanks for the assist, I'm particularly grateful for your confirmation of the use of Pivot tables as the best approach.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question