+ Reply to Thread
Results 1 to 4 of 4

Generating Macro to create multiple duplicate cells based on an input reference

  1. #1
    Registered User
    Join Date
    10-04-2013
    Location
    Ann Arbor, Michigan
    MS-Off Ver
    Excel 2011
    Posts
    2

    Generating Macro to create multiple duplicate cells based on an input reference

    Hi!
    I am new to excel and need help generating a macro that will save me several hours of inputing the data manually!

    I collected data on death rate of flies when exposed to toxins and recorded the number of dead flies over an hour in 5 minute intervals.
    I have a spreadsheet with 1000 rows. Each row represents a sample size of 10 flies and their death rate over the 12 recorded times (5 to 60 minutes). Instead of looking at death curves over the hour range, i now want to change my data to separate the 10 flies in each row to include the time of death for each individual fly in the sample. This means that i will need to generate 10 different "time to death" output numbers based on the survival data i currently have for each row. Here is an example of what i am trying to do:

    T1, T2, T3, T4, T5, T6, T7, T8, T9, T10, T11, T12 <-----this indicated the time variable
    5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60 <-----this is the value of each variable. For example T1 is 5 minutes.
    2, 2, 3, 4, 4, 4, 4, 5, 7, 7, 7, 10 <---these are the number of flies that were dead at the recorded times
    5, 5, 15, 20, 40, 45, 45, 60, 60, 60 <----these are the output values that i need for this ONE row.[/B]

    The last row clearly accounts for the time of death for all 10 flies in the sample size and separates them into individual samples. For example, at T1 (or 5 minutes) 2 flies were dead, so my output file should have two 5minute values out of the 10 because 2 different flies in the sample died within 5 minutes. I will then just need to transpose the values from horizontal to vertical to give me 10,000 rows with "time of death" values. Ah, that's a lot of flies!
    Any help will be greatly appreciated!
    thanks
    Attached Files Attached Files
    Last edited by Jomian; 10-05-2013 at 12:32 AM. Reason: added data attachment!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Generating Macro to create multiple duplicate cells based on an input reference

    Hi and welcome to the forum

    This sounds like an interesting exercise However, I would suggest that you upload a sample workbook, showing your examples of your data, and what your expected outcome would look like.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-04-2013
    Location
    Ann Arbor, Michigan
    MS-Off Ver
    Excel 2011
    Posts
    2

    Re: Generating Macro to create multiple duplicate cells based on an input reference

    Thanks for welcoming me, FDibbins. I've attached the first 14 rows of my data. One thing that i forgot to mention was that some individuals were not affected by the toxin and survived, so their "time of death" value can be "0" or "NA" depending on how the macro file works!
    I appreciate your response

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Generating Macro to create multiple duplicate cells based on an input reference

    I had to fiddle this.

    1st, I created a helper "table" that would do the comparing, and then pull out the times.
    Then I used the offset() function to pull the data down.

    Take a look atthe attached and see if this is what you want? (some of my "extracted" times didnt match yours)
    Attached Files Attached Files

+ 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. Macro to create a template based on user input
    By redman742 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-27-2012, 10:59 PM
  2. Create mutiples Cells based on input number
    By perrierf in forum Excel General
    Replies: 3
    Last Post: 10-28-2011, 08:11 AM
  3. Create macro to fill multiple cells from input in one cell
    By codilaine in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-29-2011, 03:56 AM
  4. reference to a range of cells as input for a macro
    By thosecars82 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2009, 04:54 PM
  5. Generating dynamic charts based on the user input
    By shivan4u in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-13-2006, 08:43 AM

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