DEV Community

Cover image for #28 — Combine Two Tables With Different Column Headers
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

#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.

Top comments (2)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Please free to download it and experience the potential of esProc Desktop⬇️
🍀SPL download address: scudata.com/download-Desktop
🍀Plugin Installation Method: c.scudata.com/article/1652061135502
🍀References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524
🍀SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...

Some comments may only be visible to logged-in visitors. Sign in to view all comments.