Script/Formula for Comparing/Editing 2 Spreadsheets

[MoraisGT]

Hi guys!

I work with libreoffice and I have two .csv files containing information like: person name, email adress, login etc.

 

One of these files was pulled from an AD machine and needs to be updated with the information from the other file.

 

There are more than 200 records and it would be extremely time consuming comparing/updating the files manually, one record after another.

 

Do you know any formula/script that would allow this to happen: If "x" email from file 1 exists on file 2, then update file 2 with the entire row from file 1. Meaning if the email "john@domain.com" exists on both files than update file 2's information with the row from file 1.

 

Thank's in advance.

 

-MoraisGT

[Enderman]

i know programs that can do that for text files, but idk about spreadsheets

[MoraisGT]

7 minutes ago, Enderman said:

i know programs that can do that for text files, but idk about spreadsheets

But those programs would only compare lines with one string correct?

 

Because if I copy the information from one of these files to a text file it would look like something like this:

 

10076     john      john.stevens    john.stevens@domain.com

 

 

It wouldn't work like this would it?

[RedWulf]

1 minute ago, Enderman said:

i know programs that can do that for text files, but idk about spreadsheets

csv is a text file essentially unless you mean it is only string/column based. Comma separated value format, which is how many programs store spread sheets and it is nearly readable. Change some syntax(commas) and some parameters to this and it would be csv. 

2 minutes ago, MoraisGT said:

 

10076     john      john.stevens    john.stevens@domain.com

I work in excel usually, but I imagine libre has some options

see if this topic helps. There are some more complex data base programs and some programming option but that seems excessive for this task. 

http://stackoverflow.com/questions/6709225/merging-data-in-open-libreoffice-calc

 

[Enderman]

23 minutes ago, MoraisGT said:

But those programs would only compare lines with one string correct?

 

Because if I copy the information from one of these files to a text file it would look like something like this:

 

10076     john      john.stevens    john.stevens@domain.com

 

 

It wouldn't work like this would it?

those programs would show the differences between multiple files

[MoraisGT]

19 minutes ago, RedWulf said:

csv is a text file essentially unless you mean it is only string/column based. Comma separated value format, which is how many programs store spread sheets and it is nearly readable. Change some syntax(commas) and some parameters to this and it would be csv. 

I work in excel usually, but I imagine libre has some options

see if this topic helps. There are some more complex data base programs and some programming option but that seems excessive for this task. 

http://stackoverflow.com/questions/6709225/merging-data-in-open-libreoffice-calc

 

With a slow read I'm starting to understand what that function does, I think it might work for my case, although it's going to be a bit tricky because, the two files are not organized in the same way and do not have the same columns. I'll have to think about how I'm going to do this

 

But the info in the link you provided definitely helped

 

[MoraisGT]

6 minutes ago, Enderman said:

those programs would show the differences between multiple files

I see, that would be a little easier, but I would still need to edit each updated record manually....that would take ages

[RedWulf]

1 minute ago, MoraisGT said:

With a slow read I'm starting to understand what that function does, I think it might work for my case, although it's going to be a bit tricky because, the two files are not organized in the same way and do not have the same columns. I'll have to think about how I'm going to do this

 

But the info in the link you provided definitely helped

 

I imagine its not the last time you'll need to consolidate spreadsheets, so learning the concept and pattern should serve you well. 

There are some other threads and blogs explaining it all over google, best of luck to you

[MoraisGT]

10 minutes ago, RedWulf said:

I imagine its not the last time you'll need to consolidate spreadsheets, so learning the concept and pattern should serve you well. 

There are some other threads and blogs explaining it all over google, best of luck to you

One cannot have too much knowledge

 

 

From the link you gave me I came up with this: =VLOOKUP(A2,$Sheet2.$D$2:$D$140,1,0)

 

That function from what understand is doing the following:

Search the content of cell A2 (of current spredsheet) from cells D2 to D140 of the second spreadsheet and if it matches then return that value. Correct?

 

It's basically confirming if the name of a person on spreadsheet 1 is also on spreadsheet 2 right?