1

I am new users of Calc. I would like to highlight different cells that have different Id. My aim is to copy the hole row of each of them. Here is an example what I would like to do.

Id Name Age  Affiliate
1  X     23  DD
2  Y     33  DD
3  G     46  SS
4  Z     19  TY
5  W     80  CE

and my list is

id
6 
2
3
5
7

After that I would like to reorder them according to the 1st table. I also tried to do it with R without success

0

One of the functions allowing to combine tables and lists in LibreOffice is Vlookup - vertical lookup. What it does is to take a value, like the number 6 in your list, search in the first column of the table and return one of the values from the corresponding rows in the table.

Thus Vlookup definition includes: - the text or value to search for (single value) - the range of cells (table) where to search that value - the function only looks into the first column of that range - the number of the column desired as a result - a logical value that defines if the original table is sorted (best left to 0)

The range of cells containing the table should be defined through absolute references (using the "$" sign in front of the cell references). As an example, I have used the same formula on columns F, G and H, but changed the recovered column number:

Vlook example and formula

R gives a lot more flexibility and speed for the same operations but on short lists and with the data at hand Vlookup is pretty powerful. In R, merge.data.frame is one of the functions that can be used for combining tables (data frames) by using a given column. If the table is in a data frame called tb and the list is another data frame called ids, merging the two would be done by:

>merged <- merge.data.frame(ids, tb, by.x="id", by.y="Id", sort=F)
> merged
id Name Age Affiliate
1  2    Y  33        DD
2  3    G  46        SS
3  5    W  80        CE

The names of the columns used to merge the tables are "id" and "Id".

  • Thank you a lot. It absolutely right. I looked more in R and I could manage by using match. Concerning Calc. I tried to use COUNTIF and conditional formating. After that, I learned the function Vlookup but I could not figure it out. But here you well explain it. Now I'm aware a third method. Thank you again – user3037937 Sep 12 '14 at 16:51
  • You're very welcome. If only the Vlookup function was more intuitive ... If then answer is good, you can mark it as a good answer so that the question does not appear any more in the "unanswered" section.Thank you. – Cosmin Saveanu Sep 12 '14 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.