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

    Question

  • 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:

    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


    Friday, June 08, 2012 9:31 AM

Answers

  • Hi,

    Supposed that in the Sheet1, the first line and first column are existed. Then copy the following code in to “ThisWorkBook” in Visual Basic for Application.

    Sub query_sheet()

    Dim loop_num_sheet1 As Integer

    Dim range_row_sheet1 As Integer

    Dim loop_num_sheet2 As Integer

    Dim range_row_sheet2 As Integer

    Dim server_name_sheet1 As String

    Dim server_name_sheet2 As String

    Dim server_colB As Integer

    Dim server_name_null As Integer

    Dim loop_colB_sheet2 As Integer

    Dim server_not_found As Boolean

    range_row_sheet1 = Sheets(1).UsedRange.Rows.Count

    range_row_sheet2 = Sheets(2).UsedRange.Rows.Count

    For loop_num_sheet1 = 2 To range_row_sheet1

        server_not_found = False

        server_name_sheet1 = Sheets(1).Cells(loop_num_sheet1, 1)

        loop_num_sheet2 = 1

        Do Until LCase(Sheets(2).Cells(loop_num_sheet2, 1)) = server_name_sheet1

           

            Sheets(1).Cells(loop_num_sheet1, 2).Value = "Server Not Found"

            Sheets(1).Cells(loop_num_sheet1, 3).Value = "Server Not Found"

            Sheets(1).Cells(loop_num_sheet1, 4).Value = "Server Not Found"

            If loop_num_sheet2 = range_row_sheet2 Then

                server_not_found = True

                Exit Do

            End If

            loop_num_sheet2 = loop_num_sheet2 + 1

        Loop

       

       

       If server_not_found = False Then

      

        server_name_null = loop_num_sheet2 + 1

        server_colB = 1

        Do Until Sheets(2).Cells(server_name_null, 1) <> ""

            If server_name_null = range_row_sheet2 + 1 Then

                Exit Do

            End If

            server_name_null = server_name_null + 1

            server_colB = server_colB + 1

        Loop

        For loop_colB_sheet2 = loop_num_sheet2 To loop_num_sheet2 + server_colB - 1

            Select Case Sheets(2).Cells(loop_colB_sheet2, 2).Value

                Case "Oracle - Category backup"

                    Sheets(1).Cells(loop_num_sheet1, 2).Value = 1

                Case "Oracle - DataPump backup"

                    Sheets(1).Cells(loop_num_sheet1, 3).Value = 1

                Case "Oracle - DBA account created"

                    Sheets(1).Cells(loop_num_sheet1, 4).Value = 1

               

            End Select

        Next

            Dim i As Integer

            For i = 1 To 3

                If Sheets(1).Cells(loop_num_sheet1, i + 1) <> 1 Then

                    Sheets(1).Cells(loop_num_sheet1, i + 1).Value = 0

                End If

            Next

    End If

    Next

    End Sub


    Jaynet Zhang

    TechNet Community Support

    Monday, June 11, 2012 5:39 AM
  • Thanks Jaynet,

    I think instead of having a Macros we can do it simply by using in-build function which I figured out after posting that question here.

    The code:

    =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")))))

    But now I have another query:

    I would need your help once again.  I modified my code to 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 1 - is data is there and 0 if not there.

    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

    Wednesday, June 13, 2012 6:34 AM

All replies

  • Hi,

    Supposed that in the Sheet1, the first line and first column are existed. Then copy the following code in to “ThisWorkBook” in Visual Basic for Application.

    Sub query_sheet()

    Dim loop_num_sheet1 As Integer

    Dim range_row_sheet1 As Integer

    Dim loop_num_sheet2 As Integer

    Dim range_row_sheet2 As Integer

    Dim server_name_sheet1 As String

    Dim server_name_sheet2 As String

    Dim server_colB As Integer

    Dim server_name_null As Integer

    Dim loop_colB_sheet2 As Integer

    Dim server_not_found As Boolean

    range_row_sheet1 = Sheets(1).UsedRange.Rows.Count

    range_row_sheet2 = Sheets(2).UsedRange.Rows.Count

    For loop_num_sheet1 = 2 To range_row_sheet1

        server_not_found = False

        server_name_sheet1 = Sheets(1).Cells(loop_num_sheet1, 1)

        loop_num_sheet2 = 1

        Do Until LCase(Sheets(2).Cells(loop_num_sheet2, 1)) = server_name_sheet1

           

            Sheets(1).Cells(loop_num_sheet1, 2).Value = "Server Not Found"

            Sheets(1).Cells(loop_num_sheet1, 3).Value = "Server Not Found"

            Sheets(1).Cells(loop_num_sheet1, 4).Value = "Server Not Found"

            If loop_num_sheet2 = range_row_sheet2 Then

                server_not_found = True

                Exit Do

            End If

            loop_num_sheet2 = loop_num_sheet2 + 1

        Loop

       

       

       If server_not_found = False Then

      

        server_name_null = loop_num_sheet2 + 1

        server_colB = 1

        Do Until Sheets(2).Cells(server_name_null, 1) <> ""

            If server_name_null = range_row_sheet2 + 1 Then

                Exit Do

            End If

            server_name_null = server_name_null + 1

            server_colB = server_colB + 1

        Loop

        For loop_colB_sheet2 = loop_num_sheet2 To loop_num_sheet2 + server_colB - 1

            Select Case Sheets(2).Cells(loop_colB_sheet2, 2).Value

                Case "Oracle - Category backup"

                    Sheets(1).Cells(loop_num_sheet1, 2).Value = 1

                Case "Oracle - DataPump backup"

                    Sheets(1).Cells(loop_num_sheet1, 3).Value = 1

                Case "Oracle - DBA account created"

                    Sheets(1).Cells(loop_num_sheet1, 4).Value = 1

               

            End Select

        Next

            Dim i As Integer

            For i = 1 To 3

                If Sheets(1).Cells(loop_num_sheet1, i + 1) <> 1 Then

                    Sheets(1).Cells(loop_num_sheet1, i + 1).Value = 0

                End If

            Next

    End If

    Next

    End Sub


    Jaynet Zhang

    TechNet Community Support

    Monday, June 11, 2012 5:39 AM
  • Thanks Jaynet,

    I think instead of having a Macros we can do it simply by using in-build function which I figured out after posting that question here.

    The code:

    =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")))))

    But now I have another query:

    I would need your help once again.  I modified my code to 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 1 - is data is there and 0 if not there.

    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

    Wednesday, June 13, 2012 6:34 AM
  • Hi All,

    Can anyone please help me on this?

    - Kaustubh

    Wednesday, June 20, 2012 6:12 AM
  • Hi,

    I saw you post this issue as a new case. I have replied in that case.


    Jaynet Zhang

    TechNet Community Support

    Thursday, June 21, 2012 1:34 AM