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

All Replies

• Monday, June 11, 2012 5:39 AM

  Moderator

   

   
  

  0

  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

  

  • Marked As Answer by Jaynet ZhangMicrosoft Contingent Staff, Moderator Friday, June 15, 2012 1:20 AM
  •  
  • Reply
  • Quote

   
• Wednesday, June 13, 2012 6:34 AM

   

   
  

  0

  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

  • Marked As Answer by Jaynet ZhangMicrosoft Contingent Staff, Moderator Friday, June 15, 2012 1:20 AM
  •  
  • Reply
  • Quote

   
• Wednesday, June 20, 2012 6:12 AM

   

   
  

  0

  Hi All,

  Can anyone please help me on this?

  - Kaustubh

  • Reply
  • Quote

   
• Thursday, June 21, 2012 1:34 AM

  Moderator

   

   
  

  0

  Hi,

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

  ---

  Jaynet Zhang

  TechNet Community Support

  

  • Reply
  • Quote

   
• Thursday, June 21, 2012 12:45 PM

   

   
  

  0
  I have replied to that as well. http://social.technet.microsoft.com/Forums/en-US/excel/thread/5dd73d43-589a-4008-aae2-e01eee9cfcb8/#5dd73d43-589a-4008-aae2-e01eee9cfcb8
  • Reply
  • Quote