基于唯一文本匹配将多行中的文本合并到一个单元格中？ [英] Combine text from multiple rows into one cell based on unique text match?

问题描述

我有这样的表格：

基于这个集合，我需要每个唯一SKU前缀的摘要记录，例如本例中的PW0007和PW0008）。以下是一个例子：

为防万一这难以阅读，下面是一个变体的例子以上的输出（G1）：

sku = PW0007BG-3x6-M，color =米色，size = 3x6，finish =亚光，外观=木材，应用程序= Wall或Floor | sku = PW0007BK-3x6-M，颜色=黑色，尺寸= 3x6，表面处理=哑光，外观=木材，应用=墙面或地面| sku = PW0007RD-4x8-P，颜色=红色，尺寸= 4x8，表面处理=抛光，look = Wood，application = Wall或Floor

还有一点是我将SKU字段打散到不同部分的单独表单上，这样我就可以轻松做一个UNIQUE（）并获得前缀PW0007和PW0008，如果这是最好的方法。它可以获得每个独特的SKU前缀的变体，并将它们分开，这是我无法想象的。也许某种SUMPRODUCT？

我想通过一个公式来实现这一点，但我更倾向于认为我需要某种脚本，它是一个整体'nother为我的蜡球。

解决方案

您不需要脚本;电子表格公式的组合就足够了。为了简单起见，我假设你的数据在A-F列中，输出将在同一张纸的G-H中。

首先，生成一个唯一的前缀列表。假设它们是SKU的前6个字符，则公式（放置在G2中）将是

  = sort（unique（filter （A2：A，6），len（A2：A））））
  

可选，但在这种情况下有意义）。

然后在下一列中，在单元格H2中使用这个较长的公式，这里用换行符表示。

  = join（|，arrayformula（sku =& filter（A2：A，left（A2：A，6）= G2）$ （b2，B，left（A2：A，6）= G2）
&，size =& filter（C2：C，left A2：A，6）= G2）
&，finish =& filter（D2：D，left（A2：A，6）= G2）
&，look = & filter（E2：E，left（A2：A，6）= G2）
&，application =& filter（E2：E，left（A2：A，6）= G2）
））
  

尽管长度很短，但非常简单：按SKU前缀过滤每列，预先描述它，然后连接结果。最后一步是加入所有这些结果。

H2中的公式需要拖到H列。

I have a table like so:

Based on this set, I need "summary records" for each unique SKU prefix, e.g. PW0007 and PW0008 in this example). Here's an example:

Just in case this is difficult to read, here's an example of one of the variation outputs (G1) above:

sku=PW0007BG-3x6-M,color=Beige,size=3x6,finish=Matte,look=Wood,application=Wall or Floor|sku=PW0007BK-3x6-M,color=Black,size=3x6,finish=Matte,look=Wood,application=Wall or Floor|sku=PW0007RD-4x8-P,color=Red,size=4x8,finish=Polished,look=Wood,application=Wall or Floor

One more point is that I have the SKU field broken out into its various parts on a separate sheet, such that I can easily do a UNIQUE() and get the Prefix PW0007 and PW0008 if that's the best approach. It's getting the variations for each unique SKU prefix and separating them that I can't begin to imagine. Maybe some kind of SUMPRODUCT?

I'd like to dream this is possible via a formula, but I'm more inclined to think I'll need some kind of script which is a whole 'nother ball of wax for me.

解决方案

You don't need a script for this; a combination of spreadsheet formulas suffices. For the same of simplicity I assume that your data is in columns A-F and the output will be in G-H of the same sheet.

First, generate a list of unique prefixes. Assuming they are the first 6 characters of SKU, the formula (placed in G2) would be

=sort(unique(filter(left(A2:A, 6), len(A2:A))))

(sort is optional, but makes sense to do in this context). The filter removes empty lines from consideration.

Then in the next column, use this longish formula in cell H2, presented here with line breaks.

=join("|", arrayformula("sku=" & filter(A2:A, left(A2:A, 6) = G2)  
    & ",color=" & filter(B2:B, left(A2:A, 6) = G2)
    & ",size=" & filter(C2:C, left(A2:A, 6) = G2)
    & ",finish=" & filter(D2:D, left(A2:A, 6) = G2)
    & ",look=" & filter(E2:E, left(A2:A,6) = G2)
    & ",application=" & filter(E2:E, left(A2:A,6) = G2)
))

Despite the length, it's pretty straightforward: filter each column by the SKU prefix, prepend its description, and concatenate the results. The final step is to join all such results.

The formula from H2 needs to be dragged down the H column.

这篇关于基于唯一文本匹配将多行中的文本合并到一个单元格中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！