Help to get Cell value with multiple conditions

[nhnn1986]

Hi all

I need help to take value of Cell that:
with j = 2 to lastrow
if Cell(J;G).value = Cell(A2:A).value and Cell(i;B).value = "R101" then copy value in Cell(i;C) to Hj
if Cell(J;G).value = Cell(A2:A).value and Cell(i;B).value = "R102" then copy value in Cell(i;C) to Ij
if Cell(J;G).value = Cell(A2:A).value and Cell(i;B).value = "R103" then copy value in Cell(i;C) to Jj

This mean I want after run code, the result is:
H2 = 0, I2 = 0, J2 = 0
H3 = 5000, I3 = 6000, J3 = 7000
H4 = 10, I4 = 5, J4 = 20
..........

thanks./.

A	B	C	D	E	F	G	H	I	J
(1)	(2)	(3)	(4)	(5)	(6)	(7)	(8)	(9)	(10)
xxxx	R101	100				zzz
xxxx	R102	200				zzzz
xxxx	R103	500				yyyy
yyyy	R101	10				xxxx
yyyy	R102	5				.....
yyyy	R103	20
zzzz	R101	5000
zzzz	R102	6000
zzzz	R103	7000
.....	.....	....

<tbody>
</tbody>

 

[Fluff]

How about

Excel 2013 32 bit
	A	B	C	D	E	F	G	H	I	J
2	xxxx	R101	100				zzz	0	0	0
3	xxxx	R102	200				zzzz	5000	6000	7000
4	xxxx	R103	500				yyyy	10	5	20
5	yyyy	R101	10				xxxx	100	200	500
6	yyyy	R102	5				.....
7	yyyy	R103	20
8	zzzz	R101	5000
9	zzzz	R102	6000
10	zzzz	R103	7000
11	.....	.....	....
Sheet1

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(($A$2:$A$10=$G2)*($B$2:$B$10="R101"),$C$2:$C$10)
I2	=SUMPRODUCT(($A$2:$A$10=$G2)*($B$2:$B$10="R102"),$C$2:$C$10)
J2	=SUMPRODUCT(($A$2:$A$10=$G2)*($B$2:$B$10="R103"),$C$2:$C$10)

 

[nhnn1986]

Thanks @Fluff a lot

But my data obout 5.000 rows defen on each month, so file is bigger size. Could you help me do that by VBA code and clear range (H2:J) when run code?

 

[Fluff]

Do you have a header in A1:J1?

 

[nhnn1986]

Do you have a header in A1:J1?

Yes, the header is: (1), (2) .... as you see

 

[Fluff]

How about

Sub LookupData()
   Dim rng As Range
   
   Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
   With Range("H2", Range("H" & Rows.Count).End(xlUp))
      .FormulaR1C1 = "=SUMPRODUCT((" & rng.Address(, , xlR1C1) & "=rc[-1])*(" & rng.Offset(, 1).Address(, , xlR1C1) & "=""R101"")," & rng.Offset(, 2).Address(, , xlR1C1) & ")"
      .Offset(, 1).FormulaR1C1 = "=SUMPRODUCT((" & rng.Address(, , xlR1C1) & "=rc[-2])*(" & rng.Offset(, 1).Address(, , xlR1C1) & "=""R102"")," & rng.Offset(, 2).Address(, , xlR1C1) & ")"
      .Offset(, 2).FormulaR1C1 = "=SUMPRODUCT((" & rng.Address(, , xlR1C1) & "=rc[-3])*(" & rng.Offset(, 1).Address(, , xlR1C1) & "=""R103"")," & rng.Offset(, 2).Address(, , xlR1C1) & ")"
      .Resize(, 3).Value = .Resize(, 3).Value
   End With
End Sub

 

[Fluff]

Forgot to clear the range first

Sub LookupData()
   Dim rng As Range
   
   Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
   With Range("H2", Range("H" & Rows.Count).End(xlUp))
      .Resize(, 3).ClearContents
      .FormulaR1C1 = "=SUMPRODUCT((" & rng.Address(, , xlR1C1) & "=rc[-1])*(" & rng.Offset(, 1).Address(, , xlR1C1) & "=""R101"")," & rng.Offset(, 2).Address(, , xlR1C1) & ")"
      .Offset(, 1).FormulaR1C1 = "=SUMPRODUCT((" & rng.Address(, , xlR1C1) & "=rc[-2])*(" & rng.Offset(, 1).Address(, , xlR1C1) & "=""R102"")," & rng.Offset(, 2).Address(, , xlR1C1) & ")"
      .Offset(, 2).FormulaR1C1 = "=SUMPRODUCT((" & rng.Address(, , xlR1C1) & "=rc[-3])*(" & rng.Offset(, 1).Address(, , xlR1C1) & "=""R103"")," & rng.Offset(, 2).Address(, , xlR1C1) & ")"
      .Resize(, 3).Value = .Resize(, 3).Value
   End With
End Sub

 

[nhnn1986]

Sorry but code working wrong, it's deleted hearder and all value H, I, J =0

 

[Fluff]

In that case make this change

With Range("H2", Range("G" & Rows.Count).End(xlUp).Offset(,1))

 

[Fluff]

Cross posted http://www.vbaexpress.com/forum/sho...py-cell-value-with-two-conditions-by-VBA-code

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.