逗号分隔的列表成匹配的列配对 [英] Comma separated list into matched columns pairings

问题描述

我以为我是一名体面的 Sheets 骑师，但这里有一个硬汉.我想只用一个公式把左边变成右边

示例也位于:

I thought I was a decent Sheets jockey, but here's a toughie. I'd like to turn the left into the right using just a formula

Sample also at: https://docs.google.com/spreadsheets/d/1JgDZOV_K7SbOrCb27Z5K2XUBcrtq99GTCa3-BM2pOD0

The closest I found was this (Separating Comma List into Cells with Formula) but it doesn't also do the matching.

解决方案

alternative by @Matt:King:

=ARRAYFORMULA(QUERY(VLOOKUP(SEQUENCE(COUNTA(A2:A)*
 COLUMNS(SPLIT(B2:B, ",")), 1, 0)/ COLUMNS(SPLIT(B2:B, ","))+2,
 {ROW(A:A), A:A, TRIM(SPLIT(B:B, ","))}, MOD(SEQUENCE(COUNTA(A2:A)*
 COLUMNS(SPLIT(B2:B, ",")), 1, 0), COLUMNS(SPLIT(B2:B, ",")))*{0, 1}+{2, 3}),
 "where Col2 is not NULL"))

这篇关于逗号分隔的列表成匹配的列配对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！