[Solved] Merge two datasets based on consecutive UIs

Discuss the spreadsheet application

[Solved] Merge two datasets based on consecutive UIs

Postby Veekerz28 » Sun Jan 05, 2020 4:15 pm

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! :?
Last edited by Veekerz28 on Mon Jan 06, 2020 12:27 pm, edited 2 times in total.
OpenOffice 4.1.2
Windows 10
Veekerz28
 
Posts: 5
Joined: Sun Jan 05, 2020 3:57 pm
Location: Victoria, Australia

Re: Merge two datasets based on consecutive UIs with missing

Postby FJCC » Sun Jan 05, 2020 4:40 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7784
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Merge two datasets based on consecutive UIs with missing

Postby MrProgrammer » Sun Jan 05, 2020 4:44 pm

Hi, and welcome to the forum.

Veekerz28 wrote: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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3962
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Merge two datasets based on consecutive UIs with missing

Postby Veekerz28 » Sun Jan 05, 2020 5:00 pm

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.
OpenOffice 4.1.2
Windows 10
Veekerz28
 
Posts: 5
Joined: Sun Jan 05, 2020 3:57 pm
Location: Victoria, Australia

Re: Merge two datasets based on consecutive UIs with missing

Postby Veekerz28 » Sun Jan 05, 2020 5:10 pm

I will upload a couple of files in the morning. Thankyou
OpenOffice 4.1.2
Windows 10
Veekerz28
 
Posts: 5
Joined: Sun Jan 05, 2020 3:57 pm
Location: Victoria, Australia

Re: Merge two datasets based on consecutive UIs with missing

Postby Villeroy » Sun Jan 05, 2020 6:36 pm

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Merge two datasets based on consecutive UIs with missing

Postby Veekerz28 » Mon Jan 06, 2020 3:40 am

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.
Attachments
z5418_CEA1.xls
(41.5 KiB) Downloaded 15 times
z5410_CEA1.xls
(40 KiB) Downloaded 15 times
OpenOffice 4.1.2
Windows 10
Veekerz28
 
Posts: 5
Joined: Sun Jan 05, 2020 3:57 pm
Location: Victoria, Australia

Re: Merge two datasets based on consecutive UIs with missing

Postby FJCC » Mon Jan 06, 2020 4:06 am

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.
Attachments
Ha_2018_2010.ods
(57.56 KiB) Downloaded 16 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7784
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Merge two datasets based on consecutive UIs with missing

Postby Veekerz28 » Mon Jan 06, 2020 4:20 am

That looks fantastic! Thanks all for input. I will leave this thread open temporarily just in case I get really stuck.
OpenOffice 4.1.2
Windows 10
Veekerz28
 
Posts: 5
Joined: Sun Jan 05, 2020 3:57 pm
Location: Victoria, Australia


Return to Calc

Who is online

Users browsing this forum: FJCC and 20 guests