如何根据内容将列中的数据分组为行 [英] how to group data from a column into rows based on content

问题描述

在 Excel 中，我有一列文件名.每个文件名作为唯一的前缀，我想将列中具有匹配前缀的所有单元格转置为唯一的行.没有固定数量的具有匹配前缀的条目，因为我已经看到了具有该条件的解决方案.

In Excel, I have a column of filenames. Each filename as a unique prefix and I would like to transpose all cells in the column with matching prefix into a unique row. There is not a fixed number of entries with matching prefixes, as I have seen a solution with that condition.

例如，我在这样的列中有数据:

For example, I have data in a column like this:

<头>

col1	col2	col3
Apple_1
Apple_2
Apple_3
男孩_1
母牛_1
Cow_2

我想将它分组并转置成这样的行:

and I want to group and transpose it into rows like this:

<头>

col1	col2	col3
Apple_1	Apple_2	Apple_3
男孩_1
母牛_1	Cow_2

我想我可以在 VBA 中做到这一点，但希望有某种方法可以在没有自定义编码的情况下做到这一点.

I think I can do it in VBA, but was hoping there is some way to do this without custom coding.

编辑 - 基本上，这就是我需要在 VBA 宏中执行的操作:

EDIT - Basically, this is what I need to do in a VBA macro:

FoundCnt = 0
CurrentCell = A1
Get the string of chars up until the _ and store in CurrentPrefix 
Do {
    NextCell = CurrentCell +1
    Get the chars in NextCell up until the _  
    store in NextPrefix
    If CurrentPrefix == NextPrefix {
        move content of NextCell up 1 and to the right to B FoundCnt + 1
        FoundCnt++
        delete Row containing NextCell
    } else {
        FoundCnt = 0
    }
    CurrentCell = NextCell
}  Until NextCell == ""

推荐答案

请试试这个 - 它与将单列转换为具有固定列数的 2d 范围的公式没有太大区别，您只需将序列的输出，行数等于唯一前缀数，列数等于共享相同前缀的最大条目数.然后你必须检查当前输出列是否小于匹配当前前缀的条目数:

Plz try this - it isn't very different from the formula for converting a single column into a 2d range with a fixed number of columns, you just hang the output on a Sequence with number of rows equal to number of unique prefixes and number of columns equal to max number of entries sharing the same prefix. Then you have to check that the current output column is less than the number of entries matching the current prefix:

=LET(range,A2:INDEX(A:A,COUNTA(A:A)),
uniques,UNIQUE(LEFT(range,FIND("_",range))),
cols,MAX(COUNTIF(range,uniques&"*")),
seq,SEQUENCE(COUNTA(uniques),cols,0),
IF(MOD(seq,cols)<COUNTIF(range,uniques&"*"),INDEX(range,MATCH(INDEX(uniques,INT(seq/cols)+1)&"*",range,0)+MOD(seq,cols)),""))

这篇关于如何根据内容将列中的数据分组为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！