聚合，整理和将行转换成列 [英] Aggregate, Collate and Transpose rows into columns

查看：108
本文介绍了聚合，整理和将行转换成列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有下表

  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和第二列中的所有字母在原始表格中的每个空白行都有一个空格：

  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。我尝试使用TRANSPOSE和TRIM的公式。我也试过一个宏，VLOOKUP似乎是最简单的方法，但是找不到如何

解决方案

你不能连接一个使用原始工作表函数的单元格范围（也称为 Letters ）而不事先知道范围。由于您将字符串集合到组中具有随机数元素，因此VBA循环方法似乎是解决问题的最佳方法（如果不是唯一的）。循环可以确定工作表函数根本无法执行。



点击 Alt + F11 当Visual Basic编辑器（又名VBE）打开时，立即使用下拉菜单插入►模块（ Alt + I ， M ）。将一个或两个以下内容粘贴到新窗格中，名为类似 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 
对于rw = 2 to lr 
如果IsEmpty（.Cells（rw，1））然后
 str = str& Chr（32）
如果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 
  pre> 
 
 
 要将字符串组拆分成列： 
 code> 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 =。单元格（Rows.Count，1）.End（xlUp）.row 
 .Cells（1，4）.Resize（1，2）= Array（Id，Letters）
对于rw = 2 To lr 
如果IsEmpty（.Cells（rw，1））然后
 .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 Sub 
  
点击 Alt  + < kbd> Q 返回到您的工作表。使用A1中的 Id 开始的活动工作表上的样本数据，点击 Alt  +  F8 打开宏对话框和运行宏。
 
 
 
 来自concatenate_and_transpose_to_delim_string的结果： 
 
 
 
  
 
 
 
 来自concatenate_and_transpose_into_columns的结果： 
 
 
 
  
 
 
 
结果将写入从D2开始的单元格。可能是最好的，如果没有什么重要的事先被覆盖。
 
 
 
 附录： 
 
 
 
我原来错误地解释了您的请求，并将字符串组拆分为单独的列。我已经纠正了一个补充程序，更紧密地遵循您的要求描述，但保留两个变体供其他人参考。
 
I have the following table
 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

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

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
 解决方案 
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.



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).



To concatenate the string groups delimited by a space:
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

To split the string groups into columns:
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

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.



Results from concatenate_and_transpose_to_delim_string:



    



Results from concatenate_and_transpose_into_columns:



    



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



Addendum:



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屋！
查看全文
相关文章
Office最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆