+ Reply to Thread
Results 1 to 10 of 10

looping within a a subset of data within a range?

  1. #1
    Registered User
    Join Date
    12-05-2014
    Location
    Shropshire, England
    MS-Off Ver
    2007
    Posts
    8

    looping within a a subset of data within a range?

    Hi,

    Hopefully this will make sense! I'm looking for some vba code that look at all rows of data in a sheet, but loop within groups of that data.

    For example in the below, I have some data with a value of 0 in ColumnC and what I want to do is to copy the value in the cell above to replace the 0, but that I want that to only happen within each particular group. E.g. I don't want the first record (record 10) in group 4 to be copied from record 09 of group 3 as it crosses groups.

    ColumnA ColumnB ColumnC
    Record 01 group 1 124
    Record 02 group 1 0
    Record 03 group 1 432
    Record 04 group 2 123
    Record 05 group 2 753
    Record 06 group 2 0
    Record 07 group 2 938
    Record 08 group 3 563
    Record 09 group 3 324
    Record 10 group 4 0
    Record 11 group 4 234

    Any help would be great!

    Thanks,

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: looping within a a subset of data within a range?

    =if(and(c2=0,b1=b2),c1,c2)
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    12-05-2014
    Location
    Shropshire, England
    MS-Off Ver
    2007
    Posts
    8

    Re: looping within a a subset of data within a range?

    Hi,

    That's given me a start, thanks. I'm not sure it's giving me exactly what I need though as it's using specific cell values, but it's doing what I want manually.

    I need to loop it through the whole worksheet range with a macro though, I'm not really sure what VBA code would be required to adapt the formula?

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: looping within a a subset of data within a range?

    Hi groexcel
    Try this attachment
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: looping within a a subset of data within a range?

    FWIW:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    12-05-2014
    Location
    Shropshire, England
    MS-Off Ver
    2007
    Posts
    8

    Re: looping within a a subset of data within a range?

    thanks everyone, on the basis of the first reply I worked on the formula, so this is doing what i want:

    =IF(AND(A2="",A1="",C1=C2),S2+Z1,IF(AND(A2<>"",A1="",C1=C2),S2+Z1,S2))

    I'm looking for something similar to YasserKhalil & John's output, but I'm not sure how to transpose my formula into that method. I wondered if you could help with that final step please?

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: looping within a a subset of data within a range?

    Maybe:

    Can you attach a sample file? Show which column would contain your formula, and provide values for Columns S and Z.

  8. #8
    Registered User
    Join Date
    12-05-2014
    Location
    Shropshire, England
    MS-Off Ver
    2007
    Posts
    8

    Re: looping within a a subset of data within a range?

    Hi,

    Sure, thanks for taking a look. I've attached some sample data. To be honest most of the columns are irrelevant, so that's why there's nothing in most of them! I've pasted my working formula into column Z, but that's what I want to automate rather than manually copying a formula in and dragging it down. As a overview, if columnA is empty then I want to consolidate the values in columnS down to the 'next' columnA row containing a value (but only within the group listed in column). If columnA has a value and columnS has a value then it can stay unchanged. Hopefully that explains what I'm looking to do, columnZ has the correct data.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: looping within a a subset of data within a range?

    Try:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    12-05-2014
    Location
    Shropshire, England
    MS-Off Ver
    2007
    Posts
    8

    Re: looping within a a subset of data within a range?

    Hi John,

    That's great, thanks! I've updated my code and it checks out fine. I know i'm definitely pushing my luck now, but I've discovered I need to also perform a sort of running total to re-sequence columnD values when I remove the 'blank' rows in ColumnA. I need to update column D with values, 1, 2, 3, 4, 5, etc. resetting on each group after I've removed the blank lines. For example, if I remove one of those empty tray lines which has a columnD value of 1, the next record's columnD value might be 2 or 3, but I need it to start at 1 again and increment until it hits the next group.

    I appreciate you've been really helpful, so no problems if you're too busy.

+ 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. [SOLVED] defining a range subset based on a primary range for use in Median and Mode functions
    By Araise in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-06-2013, 06:39 PM
  2. Making a dynamic range from a subset of data from datasource in a pivot table
    By Dag Lyster in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-03-2012, 03:49 AM
  3. Named range - subset
    By jlt199 in forum Excel General
    Replies: 7
    Last Post: 08-17-2010, 08:48 AM
  4. Averaging a subset of data within a range
    By willner1227 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-26-2010, 04:40 AM
  5. [SOLVED] How to enter symbols for subset or element of a subset in Excel?
    By rwcita in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2006, 05:30 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