+ Reply to Thread
Results 1 to 11 of 11

Take information from a cell, insert 3 lines below and enter 3 standard items

  1. #1
    Registered User
    Join Date
    12-04-2020
    Location
    uk
    MS-Off Ver
    professional 2010
    Posts
    6

    Take information from a cell, insert 3 lines below and enter 3 standard items

    Hi all,

    im sorry to ask something which may seem simple, however I was hoping someone may be able to help me with the automation of a tedious task?

    I have a spread sheet of assets and I would like to insert 3 items that relate to each asset below.

    for example.

    Col A

    Tank 1
    Tank 2
    Tank 3
    Tank 4


    I would like to insert additional items that are associated to each tank.

    Cover, Valve, Feed line, Hose

    so my list would then look like:

    Tank 1
    Tank 1 Hose
    Tank 1 Feed Line
    Tank 1 Valve
    Tank 1 Cover
    Tank 2
    Tank 2 Hose
    Tank 2 Feed Line
    Tank 2 Valve
    Tank 2 Cover

    etc...

    Is there anyone who can help me with this?

    I have over 600 tanks :-(

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Take information from a cell, insert 3 lines below and enter 3 standard items

    Your example shows each row being expanded to 5 rows, with 4 items added and leaving the top row for each tank unchanged.

    I'm sure you have other data associated with each tank, so it would help us if you attached a sample Excel workbook. The yellow banner at the top of the screen explains how to do this.

    Hope this helps.

    Pete

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Take information from a cell, insert 3 lines below and enter 3 standard items

    Hi, lap9387,

    three lines for 4 items?

    My guess by now
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Registered User
    Join Date
    12-04-2020
    Location
    uk
    MS-Off Ver
    professional 2010
    Posts
    6

    Re: Take information from a cell, insert 3 lines below and enter 3 standard items

    Thank you Pete,

    I will upload a sample workbook now, however the list only shows Tanks 1 through 600..

    A sample spreadsheet is attached below:.


    Thanks in advance,

    les
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-04-2020
    Location
    uk
    MS-Off Ver
    professional 2010
    Posts
    6

    Re: Take information from a cell, insert 3 lines below and enter 3 standard items

    Quote Originally Posted by HaHoBe View Post
    Hi, lap9387,

    three lines for 4 items?

    My guess by now
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Holger, apologies; I should have said 4 lines.

    thank you for this

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Take information from a cell, insert 3 lines below and enter 3 standard items

    I've set up your simple scenario in the attached file. I listed your tanks in column A, starting with A2, and then used these formulae:

    C2: =IF(ROWS($1:1)>COUNTA(A:A)*5,"",INDEX(A:A,INT((ROWS($1:1)-1)/5)+2))

    D2: =IF(C2="","",IF(MOD(ROWS($1:1)-1,5)=0,"",CHOOSE(MOD(ROWS($1:1)-1,5),"Hose","Feed Line","Valve","Cover")))

    which can then be copied down as far as you need them (copied to row 29 in the example file).

    Hope this helps.

    Pete
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,389

    Re: Take information from a cell, insert 3 lines below and enter 3 standard items

    Code inserts four lines below each original Tank number and adds the four suffixes.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-04-2020
    Location
    uk
    MS-Off Ver
    professional 2010
    Posts
    6

    Re: Take information from a cell, insert 3 lines below and enter 3 standard items

    Quote Originally Posted by Ochimus View Post
    Code inserts four lines below each original Tank number and adds the four suffixes.

    Please Login or Register  to view this content.
    Absolutley fantastic.

    thank you Ochimus!

  9. #9
    Registered User
    Join Date
    12-04-2020
    Location
    uk
    MS-Off Ver
    professional 2010
    Posts
    6

    Re: Take information from a cell, insert 3 lines below and enter 3 standard items

    Quote Originally Posted by Pete_UK View Post
    I've set up your simple scenario in the attached file. I listed your tanks in column A, starting with A2, and then used these formulae:

    C2: =IF(ROWS($1:1)>COUNTA(A:A)*5,"",INDEX(A:A,INT((ROWS($1:1)-1)/5)+2))

    D2: =IF(C2="","",IF(MOD(ROWS($1:1)-1,5)=0,"",CHOOSE(MOD(ROWS($1:1)-1,5),"Hose","Feed Line","Valve","Cover")))

    which can then be copied down as far as you need them (copied to row 29 in the example file).

    Hope this helps.

    Pete
    This is brilliant Pete,

    Thank you so much

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: Take information from a cell, insert 3 lines below and enter 3 standard items

    Glad to help.

    You now have 3 proposed solutions, so if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  11. #11
    Registered User
    Join Date
    12-04-2020
    Location
    uk
    MS-Off Ver
    professional 2010
    Posts
    6

    Re: Take information from a cell, insert 3 lines below and enter 3 standard items

    Thanks Pete.

    I have marked as solved and left thanks

    Than kyou again

+ 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. Insert new rows and copy over information based on number of lines in cell
    By rysg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-25-2017, 05:47 PM
  2. Enter information from previous cell when information is inserted
    By freyn1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-01-2015, 03:29 AM
  3. Allowing Multiple Lines Per Cell Without Alt+Enter
    By nicole3579 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-14-2014, 02:55 PM
  4. Transfer cell information into a new free cell and clean after pressing enter
    By juliangun4ev in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-12-2013, 03:57 AM
  5. How do I make someone enter information in a cell
    By Vicky T in forum Excel General
    Replies: 1
    Last Post: 06-07-2006, 12:50 PM
  6. [SOLVED] Exporting to txt file, how to insert a "Enter" between lines
    By fidller in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-17-2005, 01:05 AM
  7. [SOLVED] how do I enter multiple lines in a cell in Excel
    By asowles in forum Excel General
    Replies: 2
    Last Post: 02-03-2005, 04:06 PM

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