Thread: Best method to merge two sets of data to show unique items

I have a situation where a file is tracked monthly and items change from month to month; however, I need to compile the data and show either totals or differences between month to month. I'm currently doing this manually and wanted to know if there's a simpler way to do this in Excel.

On the sample file attached (data also shown below). There are 3 sheets: Jan, Feb and Total. In this example, Jan and Feb are manual files and the Total tab is what needs to be derived. Note that Item B does not exist in Feb and Item D does not exist in Jan:

January 2012		
		
Starting Number		100
	Item A	29
	Item B	35
	Item C	17
	Total Items	81
Difference		19

February 2012		
		
Starting Number		127
	Item A	26
	Item C	26
	Item D	39
	Total Items	91
Difference		36

Total		
		
Starting Number		227
	Item A	55
	Item B	35
	Item C	43
	Item D	39
	Total Items	172
Difference		55

I've played with an intersect formula (because actual file is more complex, for example, the items would be broken out by sub values in different columns; therefore, requiring an intersect), which will show items that exist in both, but have been unable to find a solution when there isn't a match and new items exist on one or the other.

Any help would be greatly appreciated.

Is your attachment in the same format as your real file? If not,its better you attach the sample file which reflects the real file.

Don't know it's the best way of merging data but this is one way.

Run macro "Combine_data". Will work for any number of sheets asuming they all have the same layout as in your example and that the "Total" sheet is the last sheet.

Alf