I have three worksheets: Defects, Worklog and Merged.
Merged is blank (but populated below to show expected results)
There is a key column in each which joins the data (Defects.DefectID = Worklog.ItemID).
I want to be able to quickly merge the data between Defects and Worklogs into Merged.
Data looks like this (but has many more columns and rows in reality).
Defects
Project DefectID Summary Date Name Status
NBA 2344 test 01/01/12 Peter Closed
NBA 2500 more 13/01/12 Jo Open
A&T 3442 delete 20/02/12 Sandy Closed
NBA 2877 andmore 13/01/12 Jo Closed
Worklog
ItemId Summary Name Date Time
2300 Work Jo 22/12/11 3 hrs
2344 Read Peter 01/01/12 2.5 hrs
2344 Work Peter 01/01/12 2 hrs
2344 Read Jo 02/01/12 1 hrs
2344 Read Peter 02/01/12 .5 hrs
2344 Work Peter 03/01/12 1 hrs
3442 Read Jo 20/02/12 5 mins
2877 Read Sandy 13/01/12 5 mins
2877 Work Sandy 13/01/12 5 mins
Merged
Project DefectID Summary Date Name Status ItemId Summary Name Date Time
2300 Work Jo 22/12/11 3 hrs
NBA 2344 test 01/01/12 Peter Closed 2344 Read Peter 01/01/12 2.5 hrs
NBA 2344 test 01/01/12 Peter Closed 2344 Work Peter 01/01/12 2 hrs
NBA 2344 test 01/01/12 Peter Closed 2344 Read Jo 02/01/12 1 hrs
NBA 2344 test 01/01/12 Peter Closed 2344 Read Peter 02/01/12 .5 hrs
NBA 2344 test 01/01/12 Peter Closed 2344 Work Peter 03/01/12 1 hrs
A&T 3442 delete 20/02/12 Sandy Closed 3442 Read Jo 20/02/12 5 mins
NBA 2877 andmore 13/01/12 Jo Closed 2877 Read Sandy 13/01/12 5 mins
NBA 2877 andmore 13/01/12 Jo Closed 2877 Work Sandy 13/01/12 5 mins
NBA 2500 more 13/01/12 Jo Open
The data in defects and worklogs worksheets is variable.
That is, the number of columns and rows can and will vary per worksheet.
The columns representing the key column can also vary (ie will not always be column 2 for defects and column 1 for worklogs).
It can be identified by its name ‘Defect ID’ and ‘Item ID’
There may be defects in defect worksheet that are not in worklogs worksheet.
They should be included in the merge results with blank worklog data.
There may be worklogs in the worklogs worksheet that are not in defects worksheet.
They should be included in the merge results with blank defect data.
The sort order of the results is not important.
Thanks
Bookmarks