# Adding x number of rows based on a fixed criteria

1. ## Adding x number of rows based on a fixed criteria

Hi guys,

I came across this challenge I would love to crack with your help. Attached a screenshot.

Each family has N number of kids, these are defined in the separate sheets and should be displayed bellow the family name as additional rows. The identifier is the family name for which we know that a separate sheet exists from where we need to coppy all available rows.

Excel_challenge.PNG

Any idea for an effective solution ?

Thank you
Kirk

2. ## Re: Adding x number of rows based on a fixed criteria

If all corresponding families are in tab name ending with " family"
try below code:
HTML Code:
``````Option Explicit
Sub family()
Dim lr&, k&, cell As Range, cellb As Range, ws As Worksheet, fName As String, arr(1 To 100000, 1 To 3)
With Worksheets("Participants")
lr = .Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In .Range("A2:A" & lr)
For Each ws In Sheets
If InStr(1, ws.Name, " family") Then
fName = Trim(Left(ws.Name, InStr(1, ws.Name, " family") - 1))
If ws.Name <> .Name And cell = fName Then
k = k + 1
arr(k, 1) = fName
For Each cellb In ws.Range("A1:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
k = k + 1
arr(k, 2) = cellb.Value
arr(k, 3) = cellb.Offset(0, 1).Value
Next
End If
End If
Next
Next
' for testing, paste results from cell B2. USe A2, if you want to overwrite current data
.Range("B2:C10000").ClearContents
.Range("B2").Resize(k, 3).Value = arr
End With
End Sub``````

3. ## Re: Adding x number of rows based on a fixed criteria

You are really on to something! I tried it out and it works perfectly for specific columns (A+B).

I would need to apply it for the entire rows, as the final dataset I am planning to work with is much larger (A:BN). To illustrate I expanded the example. Would you know how to proceed with the full rows?

Excel_challenge2.PNG

4. ## Re: Adding x number of rows based on a fixed criteria

Could you attach a dummy worksheet with full columns/rows?

5. ## Re: Adding x number of rows based on a fixed criteria

Here it is :-)
Thanks for checking!

6. ## Re: Adding x number of rows based on a fixed criteria

Is it a tough one ? :-)

7. ## Re: Adding x number of rows based on a fixed criteria

Could someone pls have a look? Thank you :-)

8. ## Re: Adding x number of rows based on a fixed criteria

This proposal employs Get & Transform (AKA Power Query).
The data ranges on each sheet are converted into tables.
The following Advanced Editor code is applied:
``Please Login or Register  to view this content.``
Note that AFTER the table is placed on the OutputTable sheet it is sorted ascending based on the Family Name column.
Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)