#28 — Combine Two Tables With Different Column Headers

Problem description & analysis:

Here are two Excel “named ranges”, which are Events1 and Events2 respectively. Starting from the 2nd row, both can be regarded as a table with column headers and some of their headers are the same.

original table 1
original table 2
Task: Reference names of the two “named ranges” and combine the two tables; display a field that does not exist under the current range name as empty.

desired table

Solution:

Use SPL XLL to do this:

=spl("=?1.to(3,).($[Events1]|~.m(1,2,3,0,4))|?2.to(3,).($[Events2]|~.m(1,0,0,2,3))",Events1,Events2)
Enter fullscreen mode
Exit fullscreen mode

As shown in the picture below:

result table with code entered
Explanation:

to(3,) function gets members from the 3rd to the last. m()function gets multiple members according to their positions; 0 means null. ~ is the current member, and $[] represents a string.


Discover more from Coursity

Subscribe to get the latest posts sent to your email.

Leave a Comment

Your email address will not be published. Required fields are marked *

Discover more from Coursity

Subscribe now to keep reading and get access to the full archive.

Continue reading

Scroll to Top
Overlaxed's sub brand, cloudy storage, specializes in cloud storage. In other words, port 9000 provides backward compatibility for users who are familiar with using portainer through that port. Privacy, cookie & data policy.