Filling up Table on basis of matching data - please help me for macro

[computron]

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</SPAN> <TBODY> </TBODY>	Power Products</SPAN> <TBODY> </TBODY>
ABCOM SOLUTION LLC</SPAN> <TBODY> </TBODY>	Projectors</SPAN> <TBODY> </TBODY>
AVI Infosys</SPAN> <TBODY> </TBODY>	Webcam / Security / Network Cameras</SPAN> <TBODY> </TBODY>
Despec</SPAN> <TBODY> </TBODY>	Consumables & Supplies</SPAN> <TBODY> </TBODY>
ACCTON</SPAN> <TBODY> </TBODY>	Networking</SPAN> <TBODY> </TBODY>
ACCTON</SPAN> <TBODY> </TBODY>	Webcam / Security / Network Cameras</SPAN> <TBODY> </TBODY>
ACCTON</SPAN> <TBODY> </TBODY>	Accessories</SPAN> <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</SPAN> <TBODY> </TBODY>
ACCTON</SPAN>	Networking</SPAN>	SMC	6/19/30
ACCTON</SPAN>	Webcam / Security / Network Cameras	SMC	6/19/30
ACCTON</SPAN>	Accessories	SMC	6/19/30

<TBODY>
</TBODY>

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

please let me know if u need more information, please help me to create this macro.

thank you in advance.

regards,
computron

 

[PCL]

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

 

[computron]

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

 

[Oeldere]

You changed the whole formula from PCL?

See below.

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

 

[computron]

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

 

[PCL]

In case you really want a macro

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

 

[PCL]

All previous answers are assuming data spreads from A1 to ...
according to your display

 

[computron]

i am getting compile error:

Expected: Base or Compare or Explicit or Private

 

[PCL]

Sorry, typo mistake
change

Option ExplicitSub FillData()

to

Option Explicit
Sub FillData()

 

[computron]

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