count until "X" number appears again

[dwrowe001]

Hi Mr. Excel'ers,

So, here's my problem. I have a list of numbers in Col A.. from A2 on down, and growing. I continually add numbers to the bottom of the list, so it's a growing list. I'm at A345 now. next number I add will be A346.

Say for example I add number 6 to A346. Then in A347 I add 8, 348 I add 10, 349 I add 17 and so on until in A355 I add 6 again. I need to know the count of numbers from A346 to A355. Then the process starts over until 6 appears again, giving the count of numbers between A355 to the next time 6 is entered.

I would like the formula to be able to entered on a different sheet then where the number list is.... is this doable? and does all this make sense??

Thanks for your help!!

Dave.

 

[Glove_Man]

i've seen this done with a LOOKUP(), i think, but here's my approach

This approach is sensitive and assumes that the different sheet has the same structure as Sheet1

But if you put this formula in any cell in Row 2 in your second sheet, and enter it with CTRL+SHFT+ENTER it will work.

=IF(COUNTIF(Sheet1!$A$2:A3,6)<2,"Not Enough 6s",IF(Sheet1!A3=6,ROW()-MAX((--(Sheet1!$A$2:A2=6))*ROW(Sheet1!$A$2:A2)),"cell not 6"))

This cell can then be copied down and will work.

 

[dwrowe001]

Thank you Glove_Man for your reply.. I was unable to get your formula to work for me?? I know I had to change some things in the formula like sheet name etc...

I wish I could upload an example file for you to look at. I think that would help.

List sheet:
A

2
5
10
12
6
9
11
15
1
10
13
3
5
13
8
14
7
4

<tbody>
</tbody>

in the table above, on the List sheet, the last number entered is 4, and that is at the bottom of my number list at position A346.

On another sheet, the Tracking sheet, I have the numbers 1 to 15 listed down from A2 down to A15. In the B column I have the counts. see table below:
# Counts

1	9
2	17
3	6
4	0
5	5
6	13
7	1
8	3
9	12
10	8
11	11
12	14
13	4
14	2
15	10

<tbody>
</tbody>

so, in the table above i have the number 1 thru 15 listed in A column and the counts listed in B. the counts tracks how many times ago the number was last entered... so, the number with a count of 0 is 4, indicating it was the last number entered on A346 on the List sheet.
the number 15 was entered 10 numbers ago on the list sheet, 14 was entered 2 numbers ago etc.....

If the next number added to the list is 14 for example, then a zero will be entered in the count column next to 14. and all other counts will increase by 1... 15 will be 11, 13 will be 5, 12 will be 15 ......4 will go to 1

hope this helps.

Thanks again for any and all help offered.

Dave

 

[Glove_Man]

Ahhh.. I slightly misunderstood....

Is VBA an option? This is very easy in VBA, but very hard using regular Excel formulas.

I could do it using a couple of extra columns of "workings" calculations.

 

[Glove_Man]

Try this instead.

=COUNT(Sheet1!$A$2:$A$500)+1-MAX(--(Sheet1!$A$2:$A$500=A2)*ROW(Sheet1!$A$2:$A$500))

Still an array formula - enter with Ctrl+Shft+Enter

You'll notice i've used a round 500 in there.... It's OK, the formula ignores empty cells. BUt you'll need to make sure the space below the entered numbers is empty. And, the formula has two weaknesses....

(1) It will stop working when the list gets beyond 500. You can change all the 500 to 1000 (or any number) if you want.
(2) if you put a number in Column A below the data, or leave rows blank, it will much things up.

 

[dwrowe001]

Thank you!! This worked perfectly, does exactly what I needed.

Do you think you could break down and explain how this formula works, so that in the future I might be able to figure out how to do this myself?

thanks again for all your help, much appreciated!

 

[Peter_SSs]

If you are interested in a formula that doesn't require the Ctrl+Shift+Enter confirmation and also doesn't matter if there were blanks within the column A numbers then you could try this. Like Glove_Man's formula, you just need to make sure that the $500 is big enough to cover wherever your data might end up.

Excel Workbook
	A	B
1	Number	Last
2	1	9
3	2	17
4	3	6
5	4	0
6	5	5
7	6	13
8	7	1
9	8	3
10	9	12
11	10	8
12	11	11
13	12	14
14	13	4
15	14	2
16	15	10
Tracking

.. or if you want to save Excel having to calculate the same thing (where the last row of data is) multiple times, you could put that part in a vacent cell like this.

Excel Workbook
	A	B	C	D
1	Number	Last		346
2	1	9
3	2	17
4	3	6
5	4	0
6	5	5
7	6	13
8	7	1
9	8	3
10	9	12
11	10	8
12	11	11
13	12	14
14	13	4
15	14	2
16	15	10
Tracking (2)

 

[Glove_Man]

=COUNT(Sheet1!$A$2:$A$500)+1-MAX(--(Sheet1!$A$2:$A$500=A2)*ROW(Sheet1!$A$2:$A$500))

The COUNT() simply counts up how many entries, of any sort, are in the range A2:A500.

The MAX() part is the tricky bit.

Sheet1!$A$2:$A$500=A2 returns an array of TRUE/FALSE responses as to whether the list equals the value specified in the second sheets A2.
Putting the -- infront of the array means the TRUE/FALSE are instead expressed as 1/0. So you end up with a list that looks like (0,0,0,1,0,0,0,1,0,1,1,0,0,0,0,0,0,1) etc...

ROW(A2:A500) just returns the row number of every cell, ie. (2,3,4,5,......,499,500)

Multiplying (0,1,0,0,1...) by (2,3,4....) returns a list that now looks like (0,0,4,0,0,0,0,0,10,0,0,13) where the zeroes are instances of numbers other than the one desired, and the numbers are the row numbers of when the wanted number is used.

The MAX() finds the largest row number where the the wanted number is used. Ergo, the most recent usage of the number.

So if there are COUNT(...) numbers in the list, and the most recent usage of the wanted number is in row MAX(...) you subtract the two to find how many extra have been used. The +1 is there because the list starts in Row 2 rather than Row 1.

Peter_SS's approach was the LOOKUP() one I could remember seeing but couldn't quite remember how to do.

 

[dwrowe001]

Glove_Man,
I really appreciate your help with the formula and then explaining it to me, thank you. Given some practice I might be able to put a formula like this together myself......maybe.

Peter_SSs, Thank you for your input, I will check it out and see how it works.

Dave

 

[Peter_SSs]

Peter_SSs, Thank you for your input, I will check it out and see how it works.

Dave

No problem. Look forward to hearing if it also works for you.