+ Reply to Thread
Results 1 to 6 of 6

Inputting Data

  1. #1
    Registered User
    Join Date
    09-29-2010
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    4

    Inputting Data

    - I have to enter tens of thousands of number ranges

    - There are various columns and each number range includes 10 numbers (00-09, 10-19, 20-29, 30-39, 40-49, 50-59, 60-69, 70-79, 80-89, 90-99).

    - There is only one column that needs to be changed for each number range. I have the various values that need to be changed in another spreadsheet.

    - The problem is that I would have to copy it 10 times for each range (Ex1 00-09, Ex1 10-19, Ex1 20-29, Ex1 30-39, Ex1 40-49, Ex1 50-59, Ex1 60-69, Ex1 70-79, Ex1 80-89, Ex1 90-99).


    Example:

    A1 B1 C1
    Ex1 00 09
    Ex1 10 19
    Ex1 20 29
    Ex1 30 39
    Ex1 40 49
    Ex1 50 59
    Ex1 60 69
    Ex1 70 79
    EX1 80 89
    EX1 90 99

    Ex2 00 09
    Ex2 10 19
    Ex2 20 29
    Ex2 30 39
    Ex2 40 49
    Ex2 50 59
    Ex2 60 69
    Ex2 70 79
    EX2 80 89
    EX2 90 99

    Ex3 00 09
    Ex3 10 19
    Ex3 20 29
    Ex3 30 39
    Ex3 40 49
    Ex3 50 59
    Ex3 60 69
    Ex3 70 79
    EX3 80 89
    EX3 90 99

    And so on and so forth...


    How to I take a single value and duplicate it 10 times into Column A, while leaving the rest of the data (number ranges) static?

    Thanks in advance.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,848

    Re: Inputting Data

    To do 10 at a time, I'd Search and Replace Ex1 with Ex2. If you select a range and do Search and Replace it will only do it in the selected range.

    You're thinking this is great for 10 but how about 10,000.? I'd write some VBA code that did a for next loop selecting 10 rows at a time and searching and replacing the Ex#.

    To give you a better answer or a slicker way we need a sample spreadsheet. You and attach a sample by clicking on "Go Advanced" and then on the paperclip icon.

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Inputting Data

    try this first functions are in a11,b11,c11
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,848

    Re: Inputting Data

    Hey Martin,
    Great answer. You taught me something new. Thanks!

    Now lets see if 2nice reads how to do it and saves a huge amount of time with your method.

  5. #5
    Registered User
    Join Date
    09-29-2010
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Inputting Data

    I've attached an example. Thanks for your replies
    Attached Files Attached Files

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Inputting Data

    sorry what exactly do you want to extend? perhaps like this
    see columns f g h
    f2 has =INDIRECT("j"&CEILING(ROW(A11)/10,1))
    g and h have formulas in g12 h12
    drag f2 down to f12 then select f12:h12 and continue drag down
    oops lets get rid of that indirect
    use
    =INDEX(J:J,CEILING(ROW(A11)/10,1)) instead
    amended attachment
    Attached Files Attached Files
    Last edited by martindwilson; 09-30-2010 at 05:54 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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