Ask Your Question
0

Completing table using information from other spreadsheet [closed]

asked 2014-07-10 09:18:10 +0200

jouslain gravatar image

Dear everybody,

I'm working about listing of published scientific article for my organization now, and I have to complete the table that look like this one.

image description

I'd like to fill the 'AQ' column with the values called Impact Factor (which is something like 'ranking' for each scientific journal) and those values can be found in my other spreadsheet which look alike these

image description

For example, I'd like to fill the value '0.589' (Impact Factor, from the first row of the second spreadsheety) in every the 'AQ' columns (of the first spreadsheet) whose 'AF' (ISSN, a coding number for each scientific journal) is 0001-5512

If this is confusing, I'm sorry but I find hard to explain

Thanks a lot

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-28 23:13:23.232995

Comments

1

In principle that's a standard task. You may use VLOOKUP() or a combination of MATCH() with INDEX() or OFFSET(). The problem I see is that you haven't a ISSN in your main sheet that could match an entry in the lookup table. You will have to fix that first.

So, please tell us: Is the SECOND part assured to consist of 4 characters? Is the first part assured to consist of decimal digits, and are these derived from a 4-digit part by solely suppressing leading zeros? (How and why was the information crippled as it is?)

Another question: Is 'my other spreadsheet' another sheet of the same Calc document or is it contained in a second document?

BTW Demonstrating a solution is much easier if an actual Calc document demonstrating the problem is attached.

Lupp gravatar imageLupp ( 2014-07-10 11:26:52 +0200 )edit

Sorry for not providing adequete information

First of all, 'my other spreadsheet is originally another .ods file, but now I have copy&past it to be another sheet in the same file.

Thanks for your advice, I tried VLOOKUP but still getting error

May I asked you for a favor?

The VLOOKUP script I typed shown here

[IMG]http://i1328.photobucket.com/albums/w525/siravac/Screenshot-2_zps60608e0e.png[/IMG]

The spreadsheet providing values for Impact Factor is here

[IMG]http://i1328.photobucket.com/albums/w525/siravac/Screenshot-1_zpsca61cd75.png[/iMG]

I also upload the problemed file here

http://www.4shared.com/file/PfCOaeQ2b...

Thank you

jouslain gravatar imagejouslain ( 2014-07-10 17:02:38 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2014-07-11 00:23:59 +0200

RHPV gravatar image

updated 2014-07-11 16:17:44 +0200

You are using the wrong LibreOffice program. What you have there is not a spreadsheet, but a database.

I can already see one problem you are going to run into. Your ISSNs in the table are not the same format. Select your ISSN column in the table that has the (xxxx-xxxx) format. If you remove the "-" by using the find/replace function in Calc I think the ISSN numbers in both your tables are the same.

Open LibreOffice Base and then copy your excel files to two separate tables using this youtube guide. https://www.youtube.com/watch?v=5N3fJ...

In this case your ISSN is the primary key for your table containing the journals. It is a unique number. For the other table you can create a ID with auto-incrementing key. Make sure your ISSN columns in both tables are set to VARCHAR in the tables properties.

WIth relations you can now link the tables via the ISSN. https://www.youtube.com/watch?v=UHkCb...

In "queries" you can add a query with columns from both your tables. If you use the wizard I think you will be just fine. Good luck!

rhpv

edit: some correcton in the answer regarding primary keys

edit flag offensive delete link more

Comments

To remove the dash (the "-"): Select ISSN column Press control+h to open find/replace. Go to "more options" and make sure "only selection" is selected. In find type: - In replace type: (Nothing!, so not this, just nothing) Click replace all. Done!

RHPV gravatar imageRHPV ( 2014-07-11 00:35:28 +0200 )edit
1

answered 2014-07-10 19:22:25 +0200

Lupp gravatar image

Couldn't get your file, no "script" found.

I prepared an example for using VLOOKUP() in such a case and included a bit of additional advice. See attachment. ask36709VlookupHelp001.ods

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-07-10 09:18:10 +0200

Seen: 316 times

Last updated: Jul 11 '14