+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    03-07-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    10

    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. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-07-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    10

    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.

    For example, I will start with:
    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.
    Last edited by squidg; 12-08-2016 at 10:45 AM.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

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

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

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    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
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Registered User
    Join Date
    03-07-2011
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    10

    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. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,137

    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

+ 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. Code/Macro that takes fluctuating info in one column and makes static in another column.
    By Sonny Crockett in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-18-2013, 08:36 PM
  2. Vba to subtract current date from date in column G and put static diff in column H
    By jtyoder in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-31-2013, 02:55 PM
  3. Help for adding static date in column B on import/pasting of data in Column A
    By Parijaat in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-18-2013, 05:34 AM
  4. Keeping Row or Column static
    By Panic in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-09-2008, 08:37 PM
  5. Static left hand side column and static header row.. how?
    By glic in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-20-2007, 01:45 AM
  6. Static Column
    By andycmmartin in forum Excel General
    Replies: 2
    Last Post: 10-18-2006, 10:04 AM
  7. [SOLVED] How to keep one column or row static while others move?
    By Help with Excel in forum Excel General
    Replies: 1
    Last Post: 01-10-2006, 07:15 PM
  8. Macro with non-static column length (name last row?)
    By Joe D in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2005, 08:05 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