generate ID variable using multiple conditions

[Alvina]

Hi!
I'm a beginner at VBA and I need some help.

I'm using three data sets with district data. Some district have the same name so I want to apply an ID number instead of using the district names. I have a numeric ID in one of the data sets. I now want to insert that ID in the two data sets without ID using the two conditions "if district name = district name AND province name = province name" then new variable = ID. How do I do that?!

Example:
First data set with ID (Districts with ID, some with the same name (two a:s) )

[TABLE="width: 100"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Dist[/TD]
[TD]Prov[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]a[/TD]
[TD]h[/TD]
[/TR]
</tbody>[/TABLE]

Second data set without ID and multiple observation per district

[TABLE="width: 100"]
<tbody>[TR]
[TD]x[/TD]
[TD]Dist[/TD]
[TD]Prov[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]c[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD]h[/TD]
[/TR]
</tbody>[/TABLE]

I did the following:

For i = 2 to 5
For j = 2 to 5
x = 0

if sheet1.cells(i,2) = sheet2.cells(j,2) AND sheet1.cells(i,3) = sheet2.cells(j,3) THEN

x = x + sheet1.cells(i,1)

End if

Next

sheet2.cells(j,1)=x

It is not working!

Please help me out!
Thank you

 

[Andrew Poulsom]

Welcome to MrExcel.

Why do you need a macro for that? Formula in E2 copied down:

Excel 2010
	A	B	C	D	E	F	G
1	ID	Dist	Prov		x	Dist	Prov
2	1	a	e		1	a	e
3	2	b	e		1	a	e
4	3	c	e		3	c	e
5	4	a	h		4	a	h
Sheet1

Cell Formulas
Range	Formula
E2	=INDEX(A$2:A$5,MATCH(1,INDEX((B$2:B$5=F2)*(C$2:C$5=G2),),FALSE))