“扁平"多行包含不同的数据，但在同一行中具有共同的引用 [英] "Flatten" multiple rows containing different data but with a common reference into a single row

问题描述

我已经尝试在Stackoverflow和Google上搜索此问题的答案，但尚未找到它.我认为部分问题是我不确定要尝试使用的关键字是什么.

I've tried searching Stackoverflow and Google for an answer to this but haven't found it yet. I think part of my problem is I'm not sure what the keyword(s) for what I'm trying to do would be.

我的数据如下所示:

ID    Var1    Var2    Name
01    0001    0002    Bill
01    0001    0002    Jim
01    0001    0002    Sally
02    0003    0004    Sam
02    0003    0004    Kyle

您将看到我有多个具有相同ID以及相同Var1和Var2的行，但是每一行都有一个唯一的名称.我想平整"行，以便每个ID仅包含一行，并且每一行具有与容纳所有数据所需数量一样多的名称"列.

You'll see that I have multiple rows with the same ID and same Var1 and Var2 but each row has a unique name. I want to "flatten" the rows so there is only a single row for each ID and each row has as many "Name" columns as are necessary to fit all of the data.

赞:

ID    Var1    Var2    Name1    Name2    Name3
01    0001    0002    Bill     Jim      Sally
02    0003    0004    Sam      Kyle

有人知道如何做到这一点或称之为什么吗?

Does anyone know how to do this or what it's called?

谢谢！

根据评论进行更新:我的数据源是.csv文件，我正在尝试使用Excel对其进行操作.Excel宏或VBA解决方案将是最佳选择.不幸的是，我的SQL非常基础，因此学习应用SQL解决方案会浪费时间.

Update based on comments: My data source is .csv file and I'm trying to manipulate it with Excel. Excel macros or VBA solutions would be best. Unfortunately my SQL is very elementary so learning to apply an SQL solution would be time-prohibitive.

推荐答案

使用变体数组和字典对象，这样的事情非常快

Something like this is very quick using variant arrays and a dictionary object

代码将输出从 A1:Dx 转储到 F1

更新:固定名称数字

Sub ReCut()
Dim X
Dim Y
Dim C

Dim lngRow As Long
Dim lngCol As Collection
Dim lngCnt1 As Long
Dim lngCnt As Long
Dim objDic As Object

Set objDic = CreateObject("scripting.dictionary")
X = Range([a1], Cells(Rows.Count, "C").End(xlUp)).Value2
Y = X

ReDim Y(1 To UBound(Y), 1 To 100)

For lngCnt1 = 1 To (UBound(Y, 2) - 3)
Y(1, lngCnt1) = "Name" & lngCnt1
Next

For lngRow = 1 To UBound(X, 1)
    If objDic.exists(X(lngRow, 1) & X(lngRow, 2) & X(lngRow, 3)) Then
'find first blank entry in relevant array row
        C = Split(Join(Application.Index(Y, lngCnt), "| "), "|")
        Y(lngCnt, Application.Match(" ", C, 0)) = X(lngRow, 4)
    Else
        lngCnt = lngCnt + 1
        Y(lngCnt, 1) = X(lngRow, 1)
        Y(lngCnt, 2) = X(lngRow, 2)
        Y(lngCnt, 3) = X(lngRow, 3)
        Y(lngCnt, 4) = X(lngRow, 4)
        objDic.Add X(lngRow, 1) & X(lngRow, 2) & X(lngRow, 3), lngCnt
    End If
Next

[f1].Resize(UBound(Y, 1), UBound(Y, 2)) = Y

End Sub

这篇关于“扁平"多行包含不同的数据，但在同一行中具有共同的引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！