Merge two Calc csv files using unique id

Discuss the spreadsheet application

Merge two Calc csv files using unique id

Postby dru_nasty » Tue Feb 12, 2019 4:58 am

I have two tables I need to merge together.
I have a calc table called "blog" with the following columns:
author | title | post
some sample rows under those columns would look like:
1 | This is a title | Post content here
1,3 | Another title | More post content

The author column has identifier numbers in it, that reference another table called "authors" that looks like this:
id | name
1 | Ty Webb
2 | Al Czervik
3 | Danny Noonan

What i would like, is to change the id numbers in the author column in my "blog" file to be the names from the "authors" file.
Is there some way to do this easily as I have about 200 unique authors.
Ideally I'd like my "blog" file to end up looking like this:
author | title | post
Ty Webb | This is a title | Post content here
Ty Webb, Danny Noonan | Another title | More post content
Open Office 4 on MacOS 10.12.6
dru_nasty
 
Posts: 3
Joined: Tue Feb 12, 2019 4:48 am

Re: Merge two calc csv files using unique id

Postby dru_nasty » Tue Feb 12, 2019 4:44 pm

Had a reply/fix by Mr. Programmer but the post deleted?
Open Office 4 on MacOS 10.12.6
dru_nasty
 
Posts: 3
Joined: Tue Feb 12, 2019 4:48 am

Re: Merge two calc csv files using unique id

Postby MrProgrammer » Tue Feb 12, 2019 5:03 pm

dru_nasty wrote:What i would like, is to change the id numbers in the author column in my "blog" file to be the names from the "authors" file.
Correction:
201902120900.ods
(12.83 KiB) Downloaded 6 times

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
[Tutorial] Mac FAQ
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3618
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Merge two calc csv files using unique id

Postby dru_nasty » Tue Feb 12, 2019 5:46 pm

Could you help me understand what is going on in this file? It's much appreciated and I'd like to understand myself.
Open Office 4 on MacOS 10.12.6
dru_nasty
 
Posts: 3
Joined: Tue Feb 12, 2019 4:48 am

Re: Merge two Calc csv files using unique id

Postby MrProgrammer » Wed Feb 13, 2019 10:49 pm

dru_nasty wrote:Could you help me understand what is going on in this file?
That's too vague for me to help much but I will make a guess. You should have explained exactly what formula you don't understand. First, study the Ten Concepts tutorial until you become familiar with the basic spreadsheet concepts. Then read about the SUBSTITUTE function and about Data → Multiple Operations in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum.

Try typing some values in IDs.D1, for example 2 then 3 then 2,3 then 3,2 and observe what happens to the values in column C. The MULTIPLE.OPERATIONS formulas are just a way to automate the process. Open this attachment to learn the standard way to use it. In sheet After select cells A2:B4 then Data → Multiple Operations → Formulas → $IDs.$C$1 → Column Input Cell → $IDs.$D$1 → OK.
201902131437.ods
(13.16 KiB) Downloaded 4 times

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3618
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests