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
Bookmarks