# Static A Column, +1 to B Column every 447 rows

1. ## Static A Column, +1 to B Column every 447 rows

I have a complicated excel question which has been frying my brain for hours...
I have 447 rows of text in column A, I want to increase the number in column B by 1 every 447 rows and then repeat until the number reaches 250.

For example:
Apple, 1
Pinapple, 1
Cheese, 1
Apple, 2
Pinapple, 2
Cheese, 2
Apple, 3
Pinapple, 3
Cheese, 3

Any ideas?

2. ## Re: Static A Column, +1 to B Column every 447 rows

Do you mean you have 447 repeating "fruits"?

Perhaps try:

=Countif(A\$2:A2,A2)

copied down where A2 contains first entry.

3. ## Re: Static A Column, +1 to B Column every 447 rows

Thanks NBVC!

I'm not quite sure if the Formula doesn't do what I need it to do or if it's my lack of knowledge on how to implement it.

To explain better...

I have 447 rows of text in column A (447 different fruit names).
In column B, I want to increase the number by 1 on each fruit until it reaches 250.

Apple, 1
Pineapple, 1
Banana, 1

Then I need the Formula to continue looping column A until column B reaches 250.

Apple, 1
Pineapple, 1
Banana, 1
Apple, 2
Pineapple, 2
Banana, 2
...
...
...
Apple, 250
Pineapple, 250
Banana, 250

In the end, I will have 111750 rows (447*250)

I hope this makes more sense.

4. ## Re: Static A Column, +1 to B Column every 447 rows

Have you tested my formula? Does it give incorrect results?

5. ## Re: Static A Column, +1 to B Column every 447 rows

This is a very simple thing
Enter COUNTIF formula in B2 and copy down
=COUNTIF(A\$2:A2,A2)
 v A B 1 2 Apple 1 3 Pinapple 1 4 Cheese 1 5 Apple 2 6 Pinapple 2 7 Cheese 2 8 Apple 3 9 Pinapple 3 10 Cheese 3

6. ## Re: Static A Column, +1 to B Column every 447 rows

Many thanks guys!
I thought there may have been a way to automate the scrolling process rather then scrolling down 111750 rows manually but your Formula did the trick.
I am very grateful!

7. ## Re: Static A Column, +1 to B Column every 447 rows

Assuming you only have your fruits listed once in cells A1:A447, a simple way to achieve this is to use these two formulae in the cells stated:

A448: =A1

B448: =B1+1

then copy down as far as you need to.

Hope this helps.

Pete

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