汇总，整理和转置行转换成列 [英] Aggregate, Collate and Transpose rows into columns

问题描述

我有以下表

 Id     Letter
1001    A
1001    H
1001    H
1001    H

1001    B
1001    H
1001    H
1001    H

1001    H
1001    H
1001    H

1001    A
1001    H
1001    H
1001    H
1001    B

1001    A
1001    H
1001    H
1001    H
1001    B

1001    B
1001    H
1001    H
1001    H
1001    B

1001    H

1001    A
1001    G
1001    H
1001    H
1001    A
1001    B

1002    B
1002    H
1002    H
1002    B

1002    G
1002    H

1002    B
1002    G
1002    G
1002    H

1002    B
1002    G
1002    H
1002    H

1002    G
1002    H
1002    H

1002    H
1002    H
1002    H
1002    M
1002    N

1002    G
1002    H
1002    H
1002    M
1002    M

1002    A
1002    H
1002    H
1002    H
1002    A
1002    B

1002    B
1002    H
1002    H
1002    H

1002    B
1002    H
1002    H
1002    H
1002    A
1002    A

1002    A
1002    H
1002    H
1002    H
1002    H
1002    B

1002    H

1003    G
1003    H
1003    H
1003    N
1003    M

和我想调换它做的第一列中的每个不同的ID，并与原表中的每个空白行一个空格第二列所有字母：

And I'm trying to transpose it to make each different id in the first column and all the letters in the second column with one blank space for each blank row in the original table:

1001 AHHH BHHH HHH AHHHB AHHHB BHHHB H AGHHAB
1002 BHHB GH BGGH BGHH GHH HHHMN GHHMM AHHHAB BHHH BHHHAA AHHHHB H
1003 GHHNM

我有大约100个​​不同的ID。我试着用移调和TRIM一个公式来做到。我也试图与宏观和VLOOKUP似乎是最简单的方法，但不能找出如何

I have about 100 different id. I tried to do with a formula using TRANSPOSE and TRIM. I also tried with a macro and VLOOKUP seems to be the easiest way but can't find out how

推荐答案

您不能连接单元格区域（又名字母的）使用本机工作表函数不知道事先的范围。作为您的字符串集合成组有元素随机数，一个VBA循环的方法似乎是最好的（如果不是唯一）的方式来解决这个问题。该循环可以作出裁定沿着工作表函数就是不能执行的方式。

You cannot concatenate a range of cells (aka Letters) using native worksheet functions without knowing the scope beforehand. As your collection of strings into groups has random numbers of elements, a VBA loop approach seems the best (if not the only) way to address the issue. The loop can make determinations along the way that a worksheet function is simply incapable of performing.

点击<大骨节病>替代 + <大骨节病> F11 和Visual Basic编辑器（又名VBE）打开时，立即使用下拉菜单插入►模块（<大骨节病>替代< /骨节病> + <大骨节病> I ，<大骨节病> M ）。以下内容粘贴到名为类似的新窗格中的一个或两个的第一册 - 模块1（code）的

Tap Alt+F11 and when the Visual Basic Editor (aka VBE) opens, immediately use the pull-down menus to Insert ► Module (Alt+I,M). Paste one or both of the following into the new pane titled something like Book1 - Module1 (Code).

要连接用空格分隔字符串组：

Sub concatenate_and_transpose_to_delim_string()
    Dim rw As Long, lr As Long, pid As Long, str As String
    Dim bPutInColumns As Boolean

    With ActiveSheet
        lr = .Cells(Rows.Count, 1).End(xlUp).row
        .Cells(1, 4).Resize(1, 2) = Array("Id", "Letters")
        pid = .Cells(2, 1).Value
        For rw = 2 To lr
            If IsEmpty(.Cells(rw, 1)) Then
                str = str & Chr(32)
                If pid <> .Cells(rw + 1, 1).Value Then
                    .Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) = pid
                    .Cells(Rows.Count, 4).End(xlUp).Offset(0, 1) = str
                End If
            ElseIf pid <> .Cells(rw, 1).Value Then
                pid = .Cells(rw, 1).Value
                str = .Cells(rw, 2).Value
            Else
                str = str & .Cells(rw, 2).Value
            End If
        Next rw
        .Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) = pid
        .Cells(Rows.Count, 4).End(xlUp).Offset(0, 1) = str
    End With
End Sub

到字符串组分成列：

Sub concatenate_and_transpose_into_columns()
    Dim rw As Long, lr As Long, nr As Long, pid As Long, str As String

    With ActiveSheet
        lr = .Cells(Rows.Count, 1).End(xlUp).row
        .Cells(1, 4).Resize(1, 2) = Array("Id", "Letters")
        For rw = 2 To lr
            If IsEmpty(.Cells(rw, 1)) Then
                .Cells(nr, Columns.Count).End(xlToLeft).Offset(0, 1) = str
                str = vbNullString
            ElseIf pid <> .Cells(rw, 1).Value Then
                pid = .Cells(rw, 1).Value
                nr = .Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).row
                .Cells(nr, 4) = pid
                str = .Cells(rw, 2).Value
            Else
                str = str & .Cells(rw, 2).Value
            End If
        Next rw
        .Cells(nr, Columns.Count).End(xlToLeft).Offset(0, 1) = str
    End With
End Sub

点击<大骨节病>替代 + <大骨节病>问：返回到您的工作表。与编号在A1开始活动工作表上的样本数据，请点击<大骨节病>替代 + <大骨节病> F8 打开<强>宏对话框和运行宏。

Tap Alt+Q to return to your worksheet. With your sample data on the active worksheet starting with Id in A1, tap Alt+F8 to open the Macros dialog and Run the macro.

从concatenate_and_transpose_to_delim_string结果：

    

从concatenate_and_transpose_into_columns结果：

    

的结果将被写入起始于D2中的细胞。也许最好的，如果没有什么重要的事先存在，将被覆盖。

The results will be written into the cells starting at D2. Probably best if there was nothing important there beforehand that would be overwritten.

附录：

我原来misinter preTED您的要求和串组分割成单独列。我纠正，与更紧密地跟随你的需求描述的补充例行但保留对别人都变化参考一下。

I original misinterpreted your request and split the string groups into separate columns. I've rectified that with a supplemental routine that more closely follows your description of requirements but kept both variations for others to reference.

这篇关于汇总，整理和转置行转换成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！