To consolidate master data from different worksheets

[christeen]

Hi,

I have a problem here. I don't have idea how to duplicate records based on my master data kept in different work sheets. Can anyone pls help?
Kindly refer to my attached file.

Thanks in advanced.

note: output result pls refer to sheet "result(summary)"

[Gopika Suraj]

Sub result()

Application.ScreenUpdating = False
rwcnt = Sheets("masterdata1").UsedRange.Rows.Count
rwcnt1 = Sheets("masterdata2").UsedRange.Rows.Count



For i = 2 To rwcnt

Sheets("masterdata1").Activate
nm = Cells(i, 3)
crs_code = Cells(i, 2)
rom_num = Cells(i, 4)

Sheets("masterdata2").Activate

flag = 0
For j = 2 To rwcnt1

If Cells(j, 1) = nm Then
flag = 1
dat = Cells(j, 2)
desc = Cells(j, 3)

Sheets("Summarysheet").Activate
start_rw = ActiveSheet.UsedRange.Rows.Count + 1
Cells(start_rw, 1) = crs_code
Cells(start_rw, 2) = nm
Cells(start_rw, 5) = rom_num
Cells(start_rw, 3) = dat
Cells(start_rw, 4) = desc

End If

Sheets("masterdata2").Activate

Next j

If flag = 0 Then
Sheets("Summarysheet").Activate
start_rw = ActiveSheet.UsedRange.Rows.Count + 1
Cells(start_rw, 1) = crs_code
Cells(start_rw, 2) = nm
Cells(start_rw, 5) = rom_num
Cells(start_rw, 3) = "not found"
Cells(start_rw, 4) = "not found"
End If

Next i
End Sub

Hi this code i coded as per your attached file. You can change this and use accordingly. I hope it will help.