+ Reply to Thread
Results 1 to 6 of 6

Creating a rule or formula that add more data to cells

  1. 05-12-2015, 11:45 PM #1
    Roders
    Roders is offline
    Registered User
    Join Date
    05-12-2015
    Location
    Sydney
    MS-Off Ver
    Office 2013
    Posts
    2

    Creating a rule or formula that add more data to cells

    Hello All,

    First time user of such a website. Im an excel newb so the problem im trying to solve is way beyond my smarts.

    Im not sure what im looking for as well. Is it a formula, a rule, a macro ?
    It's Some type of hierarchy needed in the data. And would love to be pointed in the right direction. To do this manually would be a pain because there's 10,000 lines.

    I hope I make sense of it.

    Attached is sample data.

    So if I Imported new card data similar to the sample, into a spreadsheet with this rule or formula. I want it to populate the cells and the columns to the right.

    In column B I want it to auto fill -001 to all of the data cells.
    In column C, are in sets of 100's starting at "-001" through to "-100"
    In column D, are in set of 10's for each 100, starting at "-001" through to "-010"


    Regards
    Rod
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-20-2015, 03:26 PM #2
    Doc.AElstein
    Doc.AElstein is offline
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Creating a rule or formula that add more data to cells

    Hi Rod,
    . Can you either
    . - post two screenshots ( see notes for how to do that in my signature – The BB Code option is the relevant one in the downloaded Add-In )
    . or
    . – attach 2 files.

    . These should show a BEFORE and AFTER . Reduce your data considerably to say max 40 rows. The data should be representative of all possible scenarios and data types.
    . The BEFORE should just have the data in it.
    . The AFTER should look exactly as you want it to look like based on the actual sample data
    Alan
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )
    Reply With QuoteRegister To Reply
  3. 05-21-2015, 12:39 AM #3
    Roders
    Roders is offline
    Registered User
    Join Date
    05-12-2015
    Location
    Sydney
    MS-Off Ver
    Office 2013
    Posts
    2

    Re: Creating a rule or formula that add more data to cells

    Thank you Alan,

    So it doesn't matter what data I add/import to column A. Names letters numbers whatever..As long as the other columns spit the following batching sequence.

    I want columns B to spit out "-001" only
    I want column C to spit out "-001-001" but the last set of -001 will be in sets of 100
    I want column D to spit out "-001-001-001" but the last set of -001 will be in sets of 10


    I have 10,000 card numbers 10 cards in a bundle, 100 bundles in a box.


    The only formula i was able to scrap up was =CONCATENATE(A3,"-001") but its very time consuming, as I have to set up all the cells with this formula.
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  4. 05-21-2015, 02:19 AM #4
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Creating a rule or formula that add more data to cells

    see attached if it suits what you after

    though...if you had this copied out you don't really need the formula?
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  5. 05-21-2015, 04:02 AM #5
    Doc.AElstein
    Doc.AElstein is offline
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Creating a rule or formula that add more data to cells

    Hi Rod,
    . If humdingaling has it sorted for you then great !
    . I could still try an alternative or maybe a VBA code if you are interested, but It would still be useful to have a clear picture of BEFORE and AFTER, so I can see exactly what you want. Again please I would want two files, or screenshots. (But please do not post images – they cannot be copied easily into a Spreadsheet… Here for example is a screen shot using the BB code tool in my signature..so the BEFORE you supply would look a bit like this with a few more rows to show some more typically data types

    Using Excel 2007
    -
    A
    B
    C
    D
    1
    Lots of 100
    Lots of 10
    2
    Card File No
    		 Pallet ID
    (Outer) Box ID
    Inner Box ID
    3
    		 U1405L U1405L-001 U1405L-001-001 U1405L-001-001-001
    4
    		 U1405L U1405L-001 U1405L-001-001 U1405L-001-001-001
    5
    		 U1405L U1405L-001 U1405L-001-001 U1405L-001-001-002
    6
    		 U1405L U1405L-001 U1405L-001-001 U1405L-001-001-004
    7
    		 U1405L U1405L-001 U1405L-001-001 U1405L-001-001-004
    8
    		 U1405L U1405L-001 U1405L-001-001 U1405L-001-001-005
    9
    		 U1405L U1405L-001 U1405L-001-001 U1405L-001-001-006
    10
    		 U1405L U1405L-001 U1405L-001-001 U1405L-001-001-007
    11
    		 U1405L U1405L-001 U1405L-001-044 U1405L-001-044-001
    980873_20150505T150028

    ………………………………..

    . Alternatively 2 Files please (Or one File with two sheets a BEFORE and AFTER sheet ) Reduced data to about 40 rows
    . So please If you want me to try then
    A BEFORE with data reduced to about 40 rows but showing all possible data types.

    An AFTER hand written by you showing exactly how it should look as a result of a Formula or any macro if a VBA alternative is of interest to you based on the exact sample data you give in the BEFORE.

    Alan
    Reply With QuoteRegister To Reply
  6. 05-21-2015, 11:27 AM #6
    mahju
    mahju is offline
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2010 plus
    Posts
    730

    Re: Creating a rule or formula that add more data to cells

    Hi

    Try this

    For Lots of 100

    Please Login or Register  to view this content.
    and 

    Please Login or Register  to view this content.
    Copied down

    For Lots of 10

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Copied down

    Hope this help
    Regards

    Edited: As I see your original file, you may need to change the format of cells from text to general where you are going to enter these formulae

    Thanks
    Last edited by mahju; 05-24-2015 at 01:10 PM.
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Creating a formatting rule, help?
    By BillTD in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-26-2015, 01:35 PM
  2. [SOLVED] Creating a formula to populate data in particular cells based on conditions in other cells
    By jamietofs in forum Excel General
    Replies: 5
    Last Post: 10-15-2012, 06:33 PM
  3. Using a formula to determine which cells to format rule type in conditional formatting
    By pfournier80 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-04-2012, 07:22 PM
  4. Need help in creating rule
    By windows9x in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-15-2006, 01:25 PM
  5. Creating a Rule in Excel
    By Shobrien in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-26-2005, 01:20 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1