Ask Your Question
0

How do I sort and merge data?

asked 2015-07-22 08:49:44 +0200

sveno gravatar image

updated 2015-07-22 09:41:07 +0200

Lets say I have a table:

NAME            ADDRESS
apple           tree
pear            land
orange(fruit)  mountain

And another table/sheet:

NAME    CODE
pear    4556
apple   7776
orange  3321

And I want to bring the 3 different columns together in one sheet:

NAME    CODE    ADDRESS
apple   7776    tree
pear    4556    land
orange  3321    mountain

How would I do it? Can I also highlight mis-matched rows or better yet, use a wildcard so it will find "orange" from "orange(fruit)"? Thank you!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2015-07-22 09:54:25 +0200

sveno gravatar image

updated 2015-07-22 10:08:04 +0200

You can do it with INDEX and MATCH formulas.

The third column can be merged into the second example sheet with:

=INDEX(Sheet1.B$1:B$3;MATCH(A1&".*";Sheet1.A$1:A$3;0))

INDEX will pick and show the cell you want (from the ADDRESS row)

MATCH will look for the correct row where A1 (pear* in this case) is in the first sheet.

You need to have regular expressions enabled in Tools->Options for the wildcard ".*" to work!

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-07-22 08:49:44 +0200

Seen: 120 times

Last updated: Jul 22 '15