How to populate a table based on the searched values in a pivoted table

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

kausty

Thread Starter
Joined
Oct 22, 2011
Messages
6
I would like to populate an excel sheet with the values that are there in another adjoining sheet (The excel file is the same). The issue here is the data in the adjoining sheet table is pivoted. So what I want is:
Sheet 1 (Columns): Server_Name, Oracle - Category backup etc
Sheet 2 (Columns): Server_Name, Monitors only and the monitors here have different values for each server.

Sample for Sheet 2:

Here is an excerpt from the first pivoted data sheet. This is my sheet 2 (the pivoted table) and my first sheet is the same as above.
sDisplayName sMonitorTypeName sMonitorTypeDescription (Placeholder?)
AQUA3 AQUA3 - AOL Jobs Monitors the ETL jobs on AQUA3
RPC Service NT Service Monitor
SNMP Test availability of SNMP
AQUA4 Oracle - Category backup Active Script Monitor
Oracle - DataPump backup Active Script Monitor
Oracle - DBA account created Active Script Monitor
Oracle - Keep Cache Active Script Monitor
Oracle - locked accounts Active Script Monitor
Oracle - RMAN backup Active Script Monitor
OracleDBConsoleORCL NT Service Monitor
AQUAMARINE3 RPC Service NT Service Monitor
SNMP Test availability of SNMP
BASIL3 RPC Service NT Service Monitor
SNMP Test availability of SNMP
BERYLLIUM1 Beryllium 1 - Eon Incremental Monitors the ETL jobs on Beryllium 1
RPC Service NT Service Monitor
SNMP Test availability of SNMP
BERYLLIUM2 Oracle - DBA account created Active Script Monitor
Oracle - Keep Cache Active Script Monitor
Oracle - locked accounts Active Script Monitor
OracleDBConsoleCBORCL NT Service Monitor

Now in sheet 1 I want to populate data as:
Server Name Oracle - Category backup Oracle - DataPump backup Oracle - DBA account created
aqua3 0 0 0
aqua4 1 1 1
aquamarine3 0 0 0
black Server Not Found Server Not Found Server Not Found
black1 Server Not Found Server Not Found Server Not Found
black2a Server Not Found Server Not Found Server Not Found
blackbox1 Server Not Found Server Not Found Server Not Found
blackbox4 Server Not Found Server Not Found Server Not Found
blackbox5 Server Not Found Server Not Found Server Not Found
blackbox6 0 0 1
blackbox7 Server Not Found Server Not Found Server Not Found
blackbox8 Server Not Found Server Not Found Server Not Found
beryllium2 0 0 1


I created a code and so it works like this:

=IF(COUNTIF(Non_Critical_Monitor!A:A,A8)=0,"Server Not Found",1*(NOT(ISERROR(GETPIVOTDATA("sMonitorTypeDescription",Non_Critical_Monitor!$A$1,"sDisplayName ",A8,"sMonitorTypeName","Oracle - Category Backup")))))
which correctly populated the data for Oracle - Category Backup as per my requirements.

Now the next question is, can I get the same results using a search for a string with the pivot data values? So what I want, is instead of populating only for "Oracle - Category Backup" specifically, I want to populate that like SEARCH("Category Backup","Oracle - Category Backup") where the last one values I am getting from pivot data.

In short - I want to search if the complete range of "sMonitorTypeName" suppose for Aqua4 (Oracle - Category backup,Oracle - DataPump backup,Oracle - DBA account created etc.) has a something which contains "Category Backup" then it should give me result as 1 else 0. Your help would be much appreciated!!

Thanks in advance!!

- Kaustubh
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top