#### computron

##### New Member
Hi,

I have one excel workbook with two worksheet, first worksheet name is REPORT & second name is DATA, i want to copy data from DATA Worksheet as per matching condition to REPORT worksheet.

for e.g.

REPORT Worksheet Format.

Supplier Name
Category
3M
Acer
Alba
APC
Aopen
STAC
 Advance Est for Lighting & Electricals

<TBODY>
</TBODY>
 Power Products

<TBODY>
</TBODY>
 ABCOM SOLUTION LLC

<TBODY>
</TBODY>
 Projectors

<TBODY>
</TBODY>
 AVI Infosys

<TBODY>
</TBODY>
 Webcam / Security / Network Cameras

<TBODY>
</TBODY>
 Despec

<TBODY>
</TBODY>
 Consumables & Supplies

<TBODY>
</TBODY>
 ACCTON

<TBODY>
</TBODY>
 Networking

<TBODY>
</TBODY>
 ACCTON

<TBODY>
</TBODY>
 Webcam / Security / Network Cameras

<TBODY>
</TBODY>
 ACCTON

<TBODY>
</TBODY>
 Accessories

<TBODY>
</TBODY>

<TBODY>
</TBODY>

DATA WORKSHEET FORMAT

Supplier Name
Category
Brand
Updated Frequency
Advance Est for Lighting & Electricals</SPAN>
Power Products</SPAN>
STAC
 6/19/30

<TBODY>
</TBODY>
ACCTON</SPAN>
Networking</SPAN>
SMC6/19/30
ACCTON</SPAN>
Webcam / Security / Network CamerasSMC6/19/30
ACCTON</SPAN>
AccessoriesSMC6/19/30

<TBODY>
</TBODY>

i wanted to copy updated frequency data in to REPORT WORKSHEET according to supplier & category matching & under the correct brand.

regards,
computron

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
A formula can make the deal
Underneath 3M in the row put
=SUMPRODUCT((Data!\$A\$2:\$A\$5=\$A2)*(Data!\$B\$2:\$B\$5=\$B2)*(Data!\$C\$2:\$C\$5=C\$1)*(Data!\$D\$2:\$D\$5))
Copy and drag as needed

i wanted to inform you that in DATA worksheet total row data is 652 not 5, when i replaced 5 with 652 in above formula, & try underneath of 3M, its giving #VALUE! ERROR

You changed the whole formula from PCL?

See below.

Code:
``=SUMPRODUCT((Data!\$A\$2:\$A\$[COLOR=#ff0000]652[/COLOR]=\$A2)*(Data!\$B\$2:\$B\$[COLOR=#ff0000]652[/COLOR]=\$B2)*(Data!\$C\$2:\$C\$[COLOR=#ff0000]652[/COLOR]=C\$1)*(Data!\$D\$2:\$D\$[COLOR=#ff0000]652[/COLOR]))``

If not, you also get an error.

It is one of the conditions for using sumproduct.

Yes i did same like you have shown here, giving same #VALUE! ERROR

In case you really want a macro
Code:
``````Option ExplicitSub FillData()
Dim LastRow1  As Long, LastCol1  As Long
Dim LastRow2  As Long
Dim FormulaStg1 As String
Dim FormulaStg2 As String
Dim EmptyStg As String
EmptyStg = """"
Sheets("Report").Activate
LastRow1 = Range("A" & Rows.Count).End(xlUp).Row
LastCol1 = Cells(1, Columns.Count).End(xlToLeft).Column
With Sheets("Data")
LastRow2 = .Range("A" & Rows.Count).End(xlUp).Row
End With
FormulaStg1 = " SUMPRODUCT((Data!\$A\$2:\$A\$" & LastRow2 & "=\$A2)*(Data!\$B\$2:\$B\$" & LastRow2 & "=\$B2)*(Data!\$C\$2:\$C\$" & LastRow2 & "=C\$1)*(Data!\$D\$2:\$D\$" & LastRow2 & "))"
FormulaStg2 = "=if(" & FormulaStg1 & " = 0," & """""" & "," & FormulaStg1 & ")"
Range("C2").Formula = FormulaStg2
Range("C2").Copy
With Range(Cells(2, "C"), Cells(LastRow1, LastCol1))
.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
.Value = .Value
End With
End Sub``````

Last edited:

i am getting compile error:

Expected: Base or Compare or Explicit or Private

Sorry, typo mistake
change

Code:
``Option ExplicitSub FillData()``
to
Code:
``````Option Explicit
Sub FillData()``````

Last edited:
its working now but in each & every cell result is coming #VALUE!

Replies
15
Views
926
Replies
13
Views
1K
Replies
2
Views
1K
Replies
18
Views
30K
Replies
0
Views
928

1,206,808
Messages
6,074,983
Members
446,109
Latest member
ab3g1nner

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back