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.
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
Bookmarks