+ Reply to Thread
Results 1 to 6 of 6

formula that returns the contents of every fourth row, except when that row is empty

  1. #1
    Registered User
    Join Date
    01-22-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    formula that returns the contents of every fourth row, except when that row is empty

    Here's what I'd like to do (I've tied myself in logical knots trying to figure out how, but someone may be able to help):

    The sheet I've got has headings, then four pieces of information for each customer that are mostly duplicates - ie, cells b2, b3, b4 and b5 are all the same customer's first name.
    Therefore I want a formula that will not show anything except rows 1, 2, and every 4th one thereafter (1,2,6,10,14 etc). This could be in a new sheet or in a different part of the same sheet.

    I need this doing for definite, the second bit would be nice to have but i can work around it:
    I want certain colums to behave differently - in column L, instead of just the cell we'd normally show (2,6, etc), I'd like that cell and the cell below it, concatenated. And in column R i'd like just the cell below the one that would normally be shown.

    Many thanks in advance, hopefully that was clear, if not then feel free to ask questions

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: formula that returns the contents of every fourth row, except when that row is em

    Sure, why not post up some sample data that shows examples of your data and your desired result. Would be clearer, yes?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-22-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: formula that returns the contents of every fourth row, except when that row is em

    yes, that would be easier, here it is

    in this example, lines 2-5 are one customer, 6-9 would be another, etc.

    And what I've put in line 7 is the result I'd like to get from the customer above.

    Thanks
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-22-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: formula that returns the contents of every fourth row, except when that row is em

    Actually, for now just a formula that returns every fourth cell to its left would be fine - ie, b1=a4, b2=a8 etc
    (i've tried potting in a few of these and dragging the results but it keeps making up its own strange patterns rather than just using every fourth cell)

    again, hope this makes sense, etc

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: formula that returns the contents of every fourth row, except when that row is em

    Pretty straightforward. This exactly duplicates your results. Drop some more data into the Sheet1 and then look at sheet2.
    Attached Files Attached Files

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: formula that returns the contents of every fourth row, except when that row is em

    And here's a slightly different flavor of formula for Sheet2 cell L2, you can use it if you like it better, copy it down...

    =IF(OR($A2="",$A2=0),"",INDEX(Sheet1!$B$2:$R$50&" "&Sheet1!$B$3:$R$51,MATCH($A2,Sheet1!$A$2:$A$50,0),MATCH(L$1,Sheet1!$B$1:$R$1,0)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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