+ Reply to Thread
Results 1 to 5 of 5

Apples and Oranges Counting Mystery using Excel ?

  1. #1
    Registered User
    Join Date
    10-17-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    22

    Apples and Oranges Counting Mystery using Excel ?

    Hi everyone,

    I've got a little problem, which I'm hoping someone can help me with please, using Excel.

    I have two columns of data - A & B. Each cell can only be either 1 or 0. 1 means that cell contains that particular fruit.

    The 1st column is 'Apples', and the 2nd column is 'Oranges'.

    I have 54,771 rows in this spreadsheet.

    The total number of Apples is 27,347, and the number of Oranges is 27,423

    Now, here's the interesting bit - I want to enter the number of CONSECUTIVE Apples and Oranges at the end of each 'run' working down the columns, using 2 new columns C & D.

    Column C displays the number of apples in that run each time the number of consecutive apples comes to an end, and column D displays the number of oranges in that run, when the number of previous consecutive oranges comes to an end.

    You can see in the attachment, Cell C15 = 13, because there are 13 apples in a row.

    Similarly cell D36 = 21, because there are 21 oranges in a row.

    Next Cell C55 = 19 because there are 19 apples in the next run

    And finally cell D79 = 24 because there 24 oranges in that run.

    I could work my way down the list manually like this, but I'm hoping that someone can write a formula to scan the entire 54,000+rows and fill in the numbers according to the above method.

    Your help would be most gratefully received.

    Thanks,

    Malcolm.

    Apples and Oranges.xlsx

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Apples and Oranges Counting Mystery using Excel ?

    Forewarning: this is a processing intensive formula. Other members may be able to provide better managed solutions.

    In C2, paste this formula:

    Please Login or Register  to view this content.
    In D2, paste this formula:

    Please Login or Register  to view this content.
    Drag both down to fill. This works with the example provided, but only after allowing a few minutes of processing time (at least on my end).

    Hope this helps!
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    10-17-2012
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Apples and Oranges Counting Mystery using Excel ?

    Hi Mcmahobt,

    These formulae work perfectly.

    Thank you so much for taking the time to help me.

    Rep Added.

    Cheers,

    Malcolm.

  4. #4
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Apples and Oranges Counting Mystery using Excel ?

    Happy to help, thanks for the rep

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Apples and Oranges Counting Mystery using Excel ?

    I got the same results with this formula in C2 copied to D2 and down both columns

    =IF(AND(A2=1,A3=0),SUM(A$2:A2)-SUM(C$1:C1),"")
    Audere est facere

+ 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. If Row=Apples then Move row to "apples" worksheet
    By whzkd in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-23-2011, 01:29 AM
  2. Apples and Oranges = Time and Production
    By ComcoDG in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-06-2008, 06:00 PM
  3. Apples, Bananas, Oranges -- Can Excel Spit Out the Highest?
    By AnnieHall25 in forum Excel General
    Replies: 2
    Last Post: 05-02-2007, 08:24 AM
  4. [SOLVED] Who buys the apples
    By Benj in forum Excel General
    Replies: 2
    Last Post: 07-13-2005, 07:05 AM
  5. [SOLVED] Apples or pears
    By gregork in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-19-2005, 09:06 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