[Solved] Merge two datasets based on consecutive UIs

[Veekerz28]

Hi,
I have several datasets with figures (hectare values) from 2010 and 2018 that need comparing. Each hectare value is linked to a unique cell identifier(e.g. J32), but my problem is that the cells with a zero hectare value have been excluded from the data. So I can't line up each set of values from 2010 and 2018 as there are cell identifiers missing. Obviously if a particular cell value only appears in either 2010 or 2018 then the other year's value is zero but how do I arrange the data in OOCalc so that I can make the comparisons easily? I hope this is clear - I'm struggling to word it!

[FJCC]

Can you upload a small file that demonstrates the data layout? To upload a file, click Post Reply and look for the Upload Attachment tab just below he box where you type a response.

[MrProgrammer]

Hi, and welcome to the forum.

I'm struggling to word it!

Attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). It is likely you will have to do this eventually anyway because I have found that people asking this type of question usually need specific formulas for their spreadsheet. They don't understand Calc well enough to follow an overview of how to proceed and can't figure out how to apply the solution in a similar topic to their circumstance.

[Veekerz28]

Thanks for speedy replies. I've gone to bed as it's late here but my data is very simply laid out - it's basically two columns: the UID and the Hectares. Something like the following:

2010 table:
UID. Hectares
J28. 3.28
J29. 0.27
J32. 1.46

2018 table:
UID. Hectares
J28. 0.14
J31. 3.2
J32. 4.29

As mentioned, where a UID appears in one year's data but is missing from the other, I really need to add it into the dataset where it's missing and apply a zero hectare count, so that the same UIDs appear for each year.

[Veekerz28]

I will upload a couple of files in the morning. Thankyou

[Villeroy]

1. Merge all sheets in one sheet with the year number in an extra column:
Year UID Hectares
2018 J33 5.23

2. Select the merged table and call Data>PivotTable>Create...
Drag Year to column fields
Drag UID to row fields
Drag Hectares to data fields, double-click the data field and change "Sum" to "Count".
Click OK


A macro which performs step 1.: viewtopic.php?f=21&t=93099

[Veekerz28]

Thanks for your response - I will have a go at what you recommend; meanwhile, here are two files I want to compare. PageName is the unique cell ID, and Haz54 is the hectare figure that I need to compare for each cell.

[FJCC]

Here are the data combined into one file and used to make a Pivot Table, as suggested by Villeroy. You can right click on the table and select Edit Layout to see how I set it up. I set the function for HaZ54 to SUM so you can see how many hectares there are each year. You can change that to COUNT if you want to confirm that there is only one value, at most, for each year.
It is almost always better to have all of the data on one sheet rather than using formulas to gather data from different sheets or files.

[Veekerz28]

That looks fantastic! Thanks all for input. I will leave this thread open temporarily just in case I get really stuck.