Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Get Values and report blank cells

    Hello,

    On sheet1 i have the complete data and on sheet2 in Column 'C' & 'F' have values in them. I want these two cells conditions to lookup in Sheet1 and get values for Column 'A','B' & 'D','E' & 'G','H' on sheet2. Once the values are copied to sheet2 search the sheet2 for blank cells and report blank cells, exclude the Column 'H' if it has blank cells.
    I have attached the spreadsheet for reference.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Making the assumption that the data is unique for each row, I used this formula in Sheet2!A1 and filled it down and across.
    Once that's done, assuming thee are no legit 0s in the original data, you could suppress the appearances of 0s in Sheet2.

    =SUMPRODUCT(($C1=Sheet1!$C$1:$C$5)*($F1=Sheet1!$F$ 1:$F$5)*Sheet1!$A$1:$A$5)

    If those assumptions are bad, another approach would have to be used in A1 and filled accordingly.

    =IF(0=SUMPRODUCT(($C1=Sheet1!$C$1:$C$5)*($F1=Sheet 1!$F$1:$F$5)*ROW($1:$5)),"",INDEX(Sheet1!A$1:A$5,S UMPRODUCT(($C1=Sheet1!$C$1:$C$5)*($F1=Sheet1!$F$1: $F$5)*ROW($1:$5))))


    This were my quick and dirty solutions.
    Last edited by kweaver; 2013-11-20 at 11:12.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    To copy across the columns you don't want to lock on Column A:
    =SUMPRODUCT(($C1=Sheet1!$C$1:$C$5)*($F1=Sheet1!$F$ 1:$F$5)*Sheet1!A$1:A$5)

    Steve

  4. #4
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by kweaver View Post
    =IF(0=SUMPRODUCT(($C1=Sheet1!$C$1:$C$5)*($F1=Sheet 1!$F$1:$F$5)*ROW($1:$5)),"",INDEX(Sheet1!A$1:A$5,S UMPRODUCT(($C1=Sheet1!$C$1:$C$5)*($F1=Sheet1!$F$1: $F$5)*ROW($1:$5))))
    This were my quick and dirty solutions.
    Thanks kweaver for solution, is it possible to automate the process of filling the formula thru macro, why because it will set values instead of formula in the cells and i don't have to drag it to col 'A'B', 'D''E' & 'G''H'.

    Thanks in advance.
    Last edited by carmine; 2013-11-21 at 00:58.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If I understand what you want and the formula gives the correct results, this should work (note I changed the null string to leave the cells blank per the original request):

    Code:
    Sub FillValuesBlanks()
      With Columns("A:H")
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = _
          "=IF(0=SUMPRODUCT((RC3=Sheet1!R1C3:R5C3)*(RC6=Sheet1!R1C6:R5C6)*ROW(R1:R5)),NA()," & _
          "INDEX(Sheet1!R1C:R5C,SUMPRODUCT((RC3=Sheet1!R1C3:R5C3)*(RC6=Sheet1!R1C6:R5C6)*ROW(R1:R5))))"
          .Copy
        .PasteSpecial Paste:=xlPasteValues
        .SpecialCells(xlCellTypeConstants, 16).ClearContents
      End With
      Application.CutCopyMode = False
    End Sub
    The code can be adapted as needed.
    Steve

  6. #6
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Steve for supporting.

    Well, it gives Run-time error : No cells were found on this .SpecialCells(xlCellTypeConstants, 16).ClearContents.
    On sheet1 2471 rows and on sheet2 2467 rows are there, 4 records are more on sheet1 is that effecting.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    That means that there were no blanks in the data. The error can be ignored in the code. Just add the lines around the errored line:
    Code:
        On Error Resume Next
        .SpecialCells(xlCellTypeConstants, 16).ClearContents
         On Error GoTo 0
    Steve

  8. #8
    Lounger
    Join Date
    Nov 2013
    Posts
    40
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    That means that there were no blanks in the data. The error can be ignored in the code. Just add the lines around the errored line:
    Steve
    Hi Steve,
    This is making me crazy, at end of data rows it appends zero's '0' .Please have look to image uploaded.

    Thanks
    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •