将一列值转换为一行

时间:2022-09-20 07:39:20

So I have data like this:

我有这样的数据:

 A             B
sku     custom_option_row_sku
AGR370A AGR370A-3
        AGR370A-4
        AGR370A-5
        AGR370A-6
        AGR370A-8
        AGR370A-9
        AGR370A-10
        AGR370A-210
        AGR370A-212
AGR370B AGR370B-3
        AGR370B-4
        ...

I need to take column B and turn it into a row so that it still matches the value in column A.

我需要把B列变成一行,这样它仍然匹配a列中的值。

It would ideally end up like:

理想的结果是:

 A                 B
sku      custom_option_row_sku
AGR370A  AGR370A-3, AGR370A-4, AGR370A-5, etc.

Is this possible with a built in feature or would I need VBA for this? Thanks in advance.

有了内置的特性,这可能吗?或者我需要VBA来实现这一点吗?提前谢谢。

2 个解决方案

#1


1  

I just did it with this code using the example you gave me. If you have any issues let me know, but it worked for me. Be sure to make a backup copy just in case.

我只是用你给我的例子做的。如果你有什么问题,请告诉我,但对我很有效。一定要做好备份,以防万一。

 Sub resort()
 Dim thesheet As Worksheet
 Set thesheet = Sheets("Sheet1")
 Dim lastrow As Long
 Dim x As Long
 Dim newRow As Long
 Dim colA As String
 Dim deleterRow As Long
 lastrow = thesheet.Range("B" & Rows.Count).End(xlUp).Row

 With thesheet
 newRow = 0

 For x = 1 To lastrow
  colA = .Cells(x, 1).Value
   If colA <> "" Then
    colA = .Cells(x, 1).Value
    newRow = x
   Else
    .Cells(newRow, 2).Value = .Cells(newRow, 2).Value & ", " & .Cells(x, 2).Value
   End If

 Next
 deleterRow = 1
 For x = 1 To lastrow
  colA = .Cells(deleterRow, 1).Value
 If colA = "" Then
  .Rows(deleterRow).Delete
 Else
  deleterRow = deleterRow + 1
 End If
 Next
 End With
 End Sub

#2


0  

If it's not that much information, you don't necessarily need VBA. Just copy the data you want from the column, go to the row you want to paste in, right click -> Paste Special -> Transpose. The icon looks like this: 将一列值转换为一行

如果没有那么多的信息,就不需要VBA。从列中复制你想要的数据,到你想要粘贴的行,右击->粘贴特殊->转置。图标是这样的:

#1


1  

I just did it with this code using the example you gave me. If you have any issues let me know, but it worked for me. Be sure to make a backup copy just in case.

我只是用你给我的例子做的。如果你有什么问题,请告诉我,但对我很有效。一定要做好备份,以防万一。

 Sub resort()
 Dim thesheet As Worksheet
 Set thesheet = Sheets("Sheet1")
 Dim lastrow As Long
 Dim x As Long
 Dim newRow As Long
 Dim colA As String
 Dim deleterRow As Long
 lastrow = thesheet.Range("B" & Rows.Count).End(xlUp).Row

 With thesheet
 newRow = 0

 For x = 1 To lastrow
  colA = .Cells(x, 1).Value
   If colA <> "" Then
    colA = .Cells(x, 1).Value
    newRow = x
   Else
    .Cells(newRow, 2).Value = .Cells(newRow, 2).Value & ", " & .Cells(x, 2).Value
   End If

 Next
 deleterRow = 1
 For x = 1 To lastrow
  colA = .Cells(deleterRow, 1).Value
 If colA = "" Then
  .Rows(deleterRow).Delete
 Else
  deleterRow = deleterRow + 1
 End If
 Next
 End With
 End Sub

#2


0  

If it's not that much information, you don't necessarily need VBA. Just copy the data you want from the column, go to the row you want to paste in, right click -> Paste Special -> Transpose. The icon looks like this: 将一列值转换为一行

如果没有那么多的信息,就不需要VBA。从列中复制你想要的数据,到你想要粘贴的行,右击->粘贴特殊->转置。图标是这样的: