VBA(EXCEL)从具有不同标准的不同行中提取信息

I have an excel file which contain information of the composite in 1 row and in below rows information of the components of this composite. Number of component rows below a composite are varied between 2 - 20 and there can be many composites in a file.

我有一个excel文件，其中包含了在1行和以下行中复合组件的信息。复合文件下面的组件行数在2 - 20之间变化，一个文件中可以有许多复合文件。

My question is: is it possible somehow to define how many rows are in the components and to extract information from each component in to one cell(concatenate). Problem I face is that number of rows are different each time and there can be multiple composites in the file containing components. So i do not know how to stop my loop and start a new composite aggregation.

我的问题是:是否可能以某种方式定义组件中有多少行，并从每个组件中提取信息到一个单元(连接)中。我所面临的问题是每次的行数都不同，并且在包含组件的文件中可以有多个复合。所以我不知道如何停止循环并启动一个新的复合聚合。

Maybe there are ways to loop from Request1(ColumnA) and assign "Request1" as a text to every empty column below until it reaches Request2, after that is finished to concatenate based on Request"n"

也许有一些方法可以对Request1(ColumnA)进行循环，并将“Request1”作为文本分配给下面的每个空列，直到它到达Request2，然后根据请求“n”进行连接

Example what i want the data to look like

例如，我希望数据是什么样子的

~~~~~~~~~~~~EDIT~~~~~~~~~~~~~~~~~~~~ I might have over complicated my question

~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~编辑~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~我可能有复杂的问题

I was just looking to concatenate information from different set of rows(for simplicity just 1 consistent cell from every row) in to 1 cell(for example last cell in the first column) for each specific composite(which contains components) My problem is I do not know how to stop the concatenation and start a new one when i am working with a new composite(new set of rows).

我只是想从不同的行连接信息(为简单起见1一致的细胞从每一行)在1细胞(例如在第一列的最后单元)为每个特定的组合(包含组件)我的问题是我不知道如何停止连接并开始一个新的工作当我用一个新的复合(新行)。

So as an example from the first picture, I would like to have "Request 1 Green Yellow White" (cells: A1, F1, F2,F3) populated in cell J1, and "Request 2 Amber Red White Blue" (cells: A4,F4,F5,F6,F7)populated i cell J4

因此，作为第一张图片的示例，我想要“请求1个绿色的黄白色”(单元格:A1, F1, F2,F3)，填充在cell J1中，“请求2个琥珀红色的白蓝色”(单元格:A4,F4,F5,F6,F7)填充I细胞J4。

#######EDIT

I have established another way of doing but still struggle with concatenation formula. In this picture example https://i.stack.imgur.com/iQdNu.jpg

我已经建立了另一种方法，但仍然在纠结于连接公式。在这张图片示例中，https://i.stack.imgur.com/iQdNu.jpg

If my table stars from row 2

如果我的桌子是从第二排开始的

=IF(A2="",J1,A2) - by putting this in column J and dragging down i will get his Request 1 Request 1 Request 1 Request 2 Request 2 Request 2 Request 2

=IF(A2="" "，J1,A2) -通过将其放入第J列并拖拽，我将获得他的请求1请求1请求1请求1请求2请求2请求2请求2请求2

Then deleting duplicates i will be left only with Request 1 Request 2 Then I can concatenate columns i want going by Request 1 or request 2 criteria(index match), but I cant figure out how to do it...

然后删除重复的我将只剩下请求1请求2然后我可以按照请求1或请求2标准(索引匹配)连接列，但是我不知道怎么做……

2 个解决方案

#1

You can use array formula to work out the start and end rows, like =SMALL(IF($A$2:$A$20<>"",ROW($A$2:$A$20)),ROW()) to find the next populated cell in A1:A20, where this would be in the cell G1. So in G1, I have a fixed 1, then in G2 down, I have =H1+1, then in each H filled down I have =SMALL(IF($A$2:$A$20<>"",ROW($A$2:$A$20)),ROW()) this gives the following

您可以使用数组公式计算开始和结束行，比如=SMALL(如果($ $2:$ 20<>")，行($ 2:$ 20))，ROW()来查找A1:A20中的下一个填充单元格，在G1中。所以在G1中，我有一个固定的1，然后在G2中，我有=H1+1，然后在每个H中我有=SMALL($2:$ 20<>"" ""行($ 2:$ 20)行()这就给出了下面的结果

Unfortunately we cant do the concat using what we have in Excel, so this will help with your loop start and ends. Number of products, is the difference in the 2

不幸的是，我们不能使用Excel中的concat，因此这将有助于循环的开始和结束。产品的数量，是2的差值

#2

If I am reading your question correctly, then the following code may help. You want to be able to add element rows beneath categories rows, and when you do that it changes the row number for every row beneath the new row. This code will show you that it doesn't matter which row the category is on because you can find it's row number any time, and also the number of elements beneath it.

如果我正确地阅读了你的问题，那么下面的代码可能会有所帮助。您希望能够在类别行下面添加元素行，当您这样做时，它将更改新行下面每一行的行号。这段代码将向您显示类别所在的行并不重要，因为您可以随时找到它的行号，以及它下面的元素的数量。

The trick is to add a word in col A of each category that will not be found in any element A value. For example, A1 might read "Category: Apples"," and there may be ten element rows under "Category: Apples" And then under those rows another category in col A will be "Category: Bananas." The code below looks for the value "Category:" in col A and gets the row number of each category line and how many elements are under it. With a little math you can figure out where to insert a new line for a new element row or what row to concatenate. And you won't need to hard code the rows numbers of the category. Just run this simple code and it will give you those row numbers to get and concatenate all rows beneath any category.

诀窍是在每个类别的col a中添加一个在任何元素a值中都找不到的词。例如，A1可能是“类别:苹果”，“类别:苹果”下可能有10个元素行，然后在这些行下面，col A中的另一个类别将是“类别:香蕉”。下面的代码查找col A中的值“Category:”，并获取每一行的行号以及它下面有多少元素。通过一些数学运算，您可以知道在哪里为新元素行插入新行，或者连接什么行。你不需要硬编码类别的行号。只要运行这个简单的代码，它就会给你这些行号来获取和连接任何类别下的所有行。

Sub findCategoryRows()
Dim lastRowColA As Long, myArray() As Variant
Dim rowOfCategoryNameArray, nameOfCategoryNameArray, categoryCounter As Long


    lastRowColA = ActiveSheet.Range("A65536").End(xlUp).Row
    myArray = Range("A1:A" & lastRowColA)

    categoryCounter = 1
    ReDim rowOfCategoryNameArray(1 To 1)
    ReDim nameOfCategoryNameArray(1 To 1)
    For i = 1 To UBound(myArray)
        If InStr(1, Range("A" & i).Value, "Category: ") Then
            rowOfCategoryNameArray(categoryCounter) = i
            nameOfCategoryNameArray(categoryCounter) = Range("A" & i).Value
            categoryCounter = categoryCounter + 1
            ReDim Preserve rowOfCategoryNameArray(1 To categoryCounter)
            ReDim Preserve nameOfCategoryNameArray(1 To categoryCounter)
        End If
    Next i

    For i = 1 To UBound(rowOfCategoryNameArray) - 1
        If i <> UBound(rowOfCategoryNameArray) - 1 Then
            Debug.Print nameOfCategoryNameArray(i) & " has " & (rowOfCategoryNameArray(i + 1) - rowOfCategoryNameArray(i)) - 1 & " element rows under it."
        Else
             Debug.Print nameOfCategoryNameArray(i) & " has " & (lastRowColA - rowOfCategoryNameArray(i)) & " element rows under it."
        End If
    Next i


End Sub