CODEWITHSUNDEEP
vbaexcel
Roman Ivanov
Roman Ivanov

Reputation: 59

VBA(EXCEL) extract information from different rows with various criteria

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.

File example i deal with

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"

Example what i want the data to look like

Outcome

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

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

#######EDIT

I have established another way of doing but still struggle with concatenation formula. In this picture example https://i.sstatic.net/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

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

Upvotes: 1

Views: 398

Answers (2)

John Muggins
John Muggins

Reputation: 1198

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.

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

Upvotes: 0

Nathan_Sav
Nathan_Sav

Reputation: 8531

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

enter image description here

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

Upvotes: 1

Related Questions