通过在选定的列上匹配来合并两个多维数组，并返回一个包含所有行和列的新数组

Question

我大部分时间都在vba工作，现在开始转向Google sheets了。

我一直在尝试编写一个函数，该函数匹配选定列上的两个多维数组，并根据有匹配的匹配和没有匹配的第一个数组中的未匹配行返回一个新的合并数组

到目前为止，我已经能够获得匹配行only的结果，但也没有弄清楚如何获得不匹配的行

在现实生活中，arr和brr可以有数百行和数十列，但维度不同

我有过

function test() {
  arr =[
       [Client Name, Header1, Header2, Id], 
       [Name1, info1, different-info1, id-1], 
       [Name2, info2, different-info2, esi-6], 
       [Name3, info3, different-info3, id-3], 
       [Name4, info4, different-info4, esi-1], 
       [Name5, , different-info5, id-5], 
       [Name6, info6, different-info6, esi-3], 
       [Name7, info7, different-info7, id-7], 
       [Name8, info8, different-info8, id-8], 
       [Name9, info9, different-info9, esi-5]
       ]

  brr =[ 
       [ID, Company-Name, File Number, Info, Different Info], 
       [esi-1, Acme-1, f123456, , different-sting1], 
       [esi-2, Acme-2, f123457, string1, different-sting2], 
       [esi-3, Acme-3, f123458, string2, different-sting3], 
       [esi-4, Acme-4, f123459, string3, different-sting4], 
       [esi-5, Acme-5, f123460, string4, ], 
       [esi-6, Acme-6, f123461, string5, ]
       ]

  //Header to Match on
  const headerMatch = "Id"
  
  //Index of matched header for arr
  const aIndex = arr[0].map(v => v.toLowerCase()).indexOf(headerMatch.toLowerCase());
  
  //Index of matched header for brr
  const bIndex = brr[0].map(v => v.toLowerCase()).indexOf(headerMatch.toLowerCase());
  
  //The array of merged data from arr and brr matched on "Id"
  const result = arr.map(a => brr.filter(b => b[bIndex].toLowerCase() === a[aIndex].toLowerCase()).map(b => [...a,b[0],b[2],b[1]])).flatMap(a => a); 
  
  return result
}

result =
[[Client Name, Header1, Header2, Id, ID, File Number, Company-Name], 
[Name2, info2, different-info2, esi-6, esi-6, f123461, Acme-6], 
[Name4, info4, different-info4, esi-1, esi-1, f123456, Acme-1], 
[Name6, info6, different-info6, esi-3, esi-3, f123458, Acme-3], 
[Name9, info9, different-info9, esi-5, esi-5, f123460, Acme-5]]

Needed Result =
[[Client Name, Header1, Header2, Id, ID, File Number, Company-Name], 
[Name1, info1, different-info1, id-1, , , ], 
[Name2, info2, different-info2, esi-6, esi-6, f123461, Acme-6], 
[Name3, info3, different-info3, id-3, , , ], 
[Name4, info4, different-info4, esi-1, esi-6, f123461, Acme-6], 
[Name5, , different-info5, id-5, , , ], 
[Name6, info6, different-info6, esi-3, esi-3, f123458, Acme-3], 
[Name7, info7, different-info7, id-7, , , ], 
[Name8, info8, different-info8, id-8, , , ], 
[Name9, info9, different-info9, esi-5, esi-5, f123460, Acme-5]]

我希望保留这个方法，因为您可以选择返回的内容，它是非常通用的

您可以返回两个数组[...a,...b]中的所有列，也可以只选择希望返回[...a,b[0],b[2],b[1]]的列

我张贴图片是因为我认为它们更清楚地表明了我的意思

arr arr

brr brr

结果(我得到的) result

结果所需的result Needed

使用data https://docs.google.com/spreadsheets/d/1j2vv4I3qeZgs0sCdguxY3clQdyBprG12cV9VDBPHxLc/edit?usp=sharing的谷歌工作表

感谢您的帮助

Answer 1

您可以收集第二个数组数据及其在对象中的键，并映射第一个数组。

​

function leftJoin(a, b, key, columns) {
    key = key.toLowerCase();
  
    const
        aIndex = a[0].findIndex(s => s.toLowerCase() === key),
        bIndex = b[0].findIndex(s => s.toLowerCase() === key),
        temp = b.slice(1).reduce((r, items, i) => (r[items[bIndex]] = items, r), {});
        
    return a.map((items, i) => [
        ...items,
        ...columns.map(
            j => (i
                ? temp[items[aIndex]] || Array(b[0].length).fill('')
                : b[0])[j]                
        )
    ]);
}

const
    a = [['Client Name', 'Header1', 'Header2', 'Id'], ['Name1', 'info1', 'different-info1', 'id-1'], ['Name2', 'info2', 'different-info2', 'esi-6'], ['Name3', 'info3', 'different-info3', 'id-3'], ['Name4', 'info4', 'different-info4', 'esi-1'], ['Name5', undefined, 'different-info5', 'id-5'], ['Name6', 'info6', 'different-info6', 'esi-3'], ['Name7', 'info7', 'different-info7', 'id-7'], ['Name8', 'info8', 'different-info8', 'id-8'], ['Name9', 'info9', 'different-info9', 'esi-5']],
    b = [['ID', 'Company-Name', 'File Number', 'Info', 'Different Info'], ['esi-1', 'Acme-1', 'f123456', undefined, 'different-sting1'], ['esi-2', 'Acme-2', 'f123457', 'string1', 'different-sting2'], ['esi-3', 'Acme-3', 'f123458', 'string2', 'different-sting3'], ['esi-4', 'Acme-4', 'f123459', 'string3', 'different-sting4'], ['esi-5', 'Acme-5', 'f123460', 'string4', undefined], ['esi-6', 'Acme-6', 'f123461', 'string5', undefined]];    

console.log(leftJoin(a, b, 'id', [0, 2, 1]));

.as-console-wrapper { max-height: 100% !important; top: 0; }

​