Query - I would like to populate a table based on the values in a pivoted table

[kausty88]

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:

[pre]

AQUA3	AQUA3 - AOL Jobs
RPC Service
SNMP
AQUA4	Oracle - Category backup
Oracle - DataPump backup
Oracle - DBA account created
Oracle - Keep Cache
Oracle - locked accounts
Oracle - RMAN backup
OracleDBConsoleORCL
AQUAMARINE3	RPC Service
SNMP
BASIL3	RPC Service
SNMP
BERYLLIUM1	Beryllium 1 - Eon  Incremental
RPC Service
SNMP
BERYLLIUM2	Oracle - DBA account created
Oracle - Keep Cache
Oracle - locked accounts
OracleDBConsoleCBORCL
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

[/pre]

 

[Luke M]

From example, I'm not sure if the first PivotTable has a data field. For this formula to work, you'll need to have one (even if it's just a dummy "placeholder").


Formula will be something like this:

=IF(COUNTIF(Sheet2!$A:$A,$A2)=0,"Server Not Found",1*(NOT(ISERROR(GETPIVOTDATA("Placeholder_Field",Sheet1!$A$3,"ServerName",$A2,"Oracle",B$1))


Note that you will probably have to change the arguments in the GETPIVOTDATA function to exactly match your setup (such as location and field names).

 

[kausty88]

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.

[pre]

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

[/pre]

 

[kausty88]

 

[kausty88]

Thank you so very much Luke. That one worked wonders!! . I really appreciate your help!! _/_

- Kaustubh

 

[kausty88]

Luke,


I would need your help once again. Your suggestion to my question worked handsomely but now I have another similar kind of requirement. I modified my code you gave me last time so work 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

 

[kausty88]

Hi,


Can anyone please take a look at this please?


- Kaustubh