# Thread: Get Values and report blank cells

1. ## 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.

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

3. 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. Originally Posted by kweaver
=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'.

5. 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. 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. 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. Originally Posted by sdckapr
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

#### Posting Permissions

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