Solved

Vlookup or Pivot Table

Posted on 2011-09-20
18
570 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
Comment Utility
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
Comment Utility
" I'd like some advice on the best approach to take"  NOTE:  I SAID ADVICE!!
0
 

Author Comment

by:chtullu135
Comment Utility

To Teylyn:

Do you have any more comments you'd like to share with me?
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
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 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:chtullu135
Comment Utility
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
Comment Utility
>>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
Comment Utility
Sorry about that,  I hadn't noticed.  I'll take care of it
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
Thanks for the clarification
0
 

Author Closing Comment

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now