“移调"指的是“移调".复制其他数据时，将具有ID字段的某些列排成行 [英] "Transposing" some columns with ID fields into rows while copying the other data

问题描述

假设我在Excel中继承了一个表，该表用于处理流程和个人(在其他数千行中)看起来与此类似.

Suppose I inherited a table in Excel that dealt with processes and individuals, which (among thousands of other rows) looks similar to this.

ID             | Name        | Quality1   | Quality2   | ... | QualityN   |
...
234,014,828,423  James         Low          Hot           .    Blue
212,552,211      Mark          Low          Cold          .    Red
845              Amy           High         Hot           .    White
...

我打算稍后在Access中使用此数据，作为一个参考表，其中每个ID号都是不同的，并且有相应的数据.显然，第一步是使用Excel中的文本转列"工具分解ID类别.这给我们留下了看起来像这样的东西.

I intend to use this data in Access later, as a reference table where each ID number is distinct and has data to go along with it. The first step is obviously to use the text to column tool in Excel to break down the ID category. This leaves us with something that looks like this.

ID |   |   |   | Name        | Quality1   | Quality2   | ... | QualityN   |
... 
234 014 828 423  James         Low          Hot           .    Blue
212 552 211      Mark          Low          Cold          .    Red
845              Amy           High         Hot           .    White
...

接下来的部分让我陷入困境.使我最终获得所需结果的过程是什么(仅使用Excel，Access和关联的VBA)?

The next part leaves me stuck however. What is the process required (using only Excel, Access, and the associated VBA) that allows me to end up with my desired results?

ID             | Name        | Quality1   | Quality2   | ... | QualityN   |
... 
234              James         Low          Hot           .    Blue
014              James         Low          Hot           .    Blue
828              James         Low          Hot           .    Blue
423              James         Low          Hot           .    Blue
212              Mark          Low          Cold          .    Red
552              Mark          Low          Cold          .    Red
211              Mark          Low          Cold          .    Red
845              Amy           High         Hot           .    White
...

我的直觉是告诉我使用数字表，其范围从0到9999，然后到Access中的数据逐位使用JOIN，但这是代码和时间密集型的，而且非常残酷且不灵活.有没有一种更优雅的方法可以帮助我制定解决方案?

My intuition is telling me to use a number table ranging from 0 to 9999 and then to JOIN over the data bit by bit in Access, but that is code and time intensive, as well as incredibly brutish and inflexible. Is there a more elegant method out there for me to craft my solution?

推荐答案

如果您想在Excel进入Access之前使用Excel进行此操作，那么一个简单的嵌套for循环可以抓取数据，然后放入另一个床单吗?

If you'd like to do this with Excel before it gets to Access, What about a simple nested for loop that grabs the data, and puts in in another sheet?

这个小功能可以做到这一点:

This little function will do just that:

Option Explicit

Function Consolidate()

    Dim x As Long, y As Long, z As Long
    Dim OutputRow As Long, OutputCol As Long
    Dim MaxRow As Long, MaxIdCol As Long, MaxCol As Long
    Dim HomeSheet As String, NewSheet As String

    'Initialize
    MaxRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row 'Last row with an ID
    MaxIdCol = Cells(1, 1).End(xlToRight).Column - 1 ' Last column with an ID
    MaxCol = Cells(1, Cells.Columns.Count).End(xlToLeft).Column 'Last column overall
    HomeSheet = ActiveSheet.Name 'Where we start from
    NewSheet = Sheets.Add.Name 'Place to put new stuff
    OutputRow = 0 'Counter for where we're putting data

    'Loop over each row
    For x = 1 To MaxRow
        'Loop over each ID column in that row
        For y = 1 To MaxIdCol
            'Is there an ID in this cell?
            If Sheets(HomeSheet).Cells(x, y) <> "" Then
                'Reset loop variables
                OutputRow = OutputRow + 1
                OutputCol = 1
                'Drop the ID in
                Sheets(NewSheet).Cells(OutputRow, OutputCol) = Sheets(HomeSheet).Cells(x, y)
                'copy over the other values
                For z = MaxIdCol + 1 To MaxCol
                    OutputCol = OutputCol + 1
                    Sheets(NewSheet).Cells(OutputRow, OutputCol) = Sheets(HomeSheet).Cells(x, z)
                Next z
            End If
        Next y
    Next x

End Function

这篇关于“移调"指的是“移调".复制其他数据时，将具有ID字段的某些列排成行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！