Ask Your Question
0

Alphabeticaly pairing two tables that don't have the same number of entries/rows

asked 2019-11-02 17:47:10 +0100

Tobruk gravatar image

updated 2019-11-02 17:47:34 +0100

I have two sets of data, each with entries that complement each other (i.e. entry from one dataset is a translation of the entry in the second dataset). The entries both start with the source word, so it is possible to alphabetically sort and pair them together.

However, some entries do not have their complementary entry in the other dataset, which means that even if I alphabetically sort both of them, they will not be completely mirroring each other.

Screenshot illustrating how the two dataset are not mirroring each other completely

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-11-06 23:45:21 +0100

Tobruk gravatar image

The solution that I post here might require finding equivalent functions in Libre Calc (the functions below are from Excel), but it does work.

1) Create 2 helper columns containing the shared term split out from each of the two columns. Here is an example that uses data in column A and the separator being ^ instead of a bullet

=LEFT(A1,FIND("^",A1)-1)

2) Find the matches uses index(match()). This assumes your data columns are A and C and your helper columns are B and D.

=INDEX(C:C,MATCH(B1,$D$1:$D$5,0))

3) (Optional) Non-matches will show as #N/A. If you want you can handle that by surrounding the index(match()) with the following to make the non-matches blank cells

=IFERROR(<formula from 2>, "")

Possible Error Handling: It looks like your data may contain whitespace. If the index(match()) isn't returning a bunch of matches then it is likely due to mismatching whitespace. To handle this you can surround the formulas from part 1 in the following to remove both leading and trailing whitespace

=TRIM(<formula from 1>)

(Solution provided by FleetAdmiralFader link to OP)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-02 17:47:10 +0100

Seen: 18 times

Last updated: Nov 06