+ Reply to Thread
Results 1 to 8 of 8

Adding x number of rows based on a fixed criteria

  1. #1
    Registered User
    Join Date
    12-20-2018
    Location
    Pilsen
    MS-Off Ver
    MS Excel 2016
    Posts
    9

    Question 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. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,382

    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. #3
    Registered User
    Join Date
    12-20-2018
    Location
    Pilsen
    MS-Off Ver
    MS Excel 2016
    Posts
    9

    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
    Last edited by Kirkis669; 06-23-2022 at 01:55 AM.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,382

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

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

  5. #5
    Registered User
    Join Date
    12-20-2018
    Location
    Pilsen
    MS-Off Ver
    MS Excel 2016
    Posts
    9

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

    Here it is :-)
    Thanks for checking!
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-20-2018
    Location
    Pilsen
    MS-Off Ver
    MS Excel 2016
    Posts
    9

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

    Is it a tough one ? :-)

  7. #7
    Registered User
    Join Date
    12-20-2018
    Location
    Pilsen
    MS-Off Ver
    MS Excel 2016
    Posts
    9

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

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

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,097

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adding rows based on multiple column criteria
    By PitBoo in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-16-2015, 06:53 PM
  2. [SOLVED] Code for adding rows based on number
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2014, 05:30 PM
  3. [SOLVED] Define a range of fixed number of columns, but unlimited number of rows
    By ccopac in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-22-2012, 02:23 PM
  4. [SOLVED] Adding 0's in front of string to equal a fixed number of characters
    By tdyl1969 in forum Excel General
    Replies: 3
    Last Post: 05-10-2012, 11:26 AM
  5. Deleting rows based on fixed criteria
    By crapmind in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-15-2007, 04:28 PM
  6. [SOLVED] Adding a fixed number from the row above with some conditions.
    By Natalie in forum Excel General
    Replies: 1
    Last Post: 08-17-2006, 03:30 AM
  7. Extracting rows based on fixed criteria
    By EdMac in forum Excel General
    Replies: 6
    Last Post: 07-06-2006, 02:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1