+ Reply to Thread
Results 1 to 7 of 7

Macro to copy rows, duplicate and analyse using different criteria

  1. #1
    Registered User
    Join Date
    07-16-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003
    Posts
    14

    Post Macro to copy rows, duplicate and analyse using different criteria

    Hi,
    I am from a clinical lab. I have a situation where a set of analysis results for a patient has to be interpreted based on the Range cutoff given. I need to compare and figure out which set of cutoff will provide better clinical interpretation. This would require me to compare huge amount of patient data.

    I have attached an excel file "Cutoff comparison" for your perusal. In it there are three worksheets: "Raw data", "Compare" & "Cutoff".

    "Raw data" worksheet contain data obtained directly from the analyzing instrument. Each patient sample is analyzed for various parameters/analyte. I have named them for convenience as P1, P2 etc. I have added 4 rows of data, but it can be more than 100 rows at a time.

    In "Cutoff" worksheet, I have specified a set of two upper and lower cutoff limits (Cutoff limit 1 & 2) based on two applicable ranges for each of the parameters (P1, P2,P3,...).

    For my patient result comparison, in my "Compare" worksheet, I would like to copy each ROW from the "Raw data" sheet and paste it in "Compare" worksheet. Also I need to create a duplicate ROW with the same data value below it. The duplicate ROW cell gets a BG colour changed to "Grey" or any other colour and the serial number (S.No) also gets renamed for identification, in this case "a" was suffixed to it.

    The values in each of the duplicate rows are then compared with either one of the cutoff limits specified and any cell having value above or below the cutoff limits gets highlighted (here font colour changed to "RED"). Like the upper Row of raw data is compared with Cutoff limit 1 and the duplicate lower raw data is compared with Cutoff limit 2.
    I have currently compared the values manually.

    I need a macro/any other means to execute this action in "Compare" worksheet. Can anyone suggest how to go about it?

    I tried recording the macro, I could record copy and paste of raw data; but comparing was not possible. further I cannot do it for "n" number of rows.

    Any help is much appreciated.

    Thanks in advance

    Regards

    Daavis.
    Attached Files Attached Files

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Macro to copy rows, duplicate and analyse using different criteria

    Maybe :

    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Registered User
    Join Date
    07-16-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Macro to copy rows, duplicate and analyse using different criteria

    Hi karedog:
    thanks for compiling the code. Sorry could not reply to your help earlier.
    The code works as i had wanted.....but a new problem has come up. I will be getting new raw data every time and when i run the macro the compare sheet is getting overwritten over the existing data.
    Can the new raw data append to the existing compare data below? That way I will have multiple days of data compiled onto one sheet for analysis.
    IMPOSSIBLE is hidden 'I' a'M' 'POSSIBLE'

  4. #4
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Macro to copy rows, duplicate and analyse using different criteria

    You are welcome. This revised code will not overwrite existing data :

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-16-2010
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Macro to copy rows, duplicate and analyse using different criteria

    Hi karedog:
    Thanks for the revised code. it works like a charm.

  6. #6
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Macro to copy rows, duplicate and analyse using different criteria

    You are welcome. Thanks for marking the thread as solved and for the rep.points.


    Regards

  7. #7
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Macro to copy rows, duplicate and analyse using different criteria

    You are welcome. Thanks for marking the thread as solved and for the rep.points.


    Regards

+ 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. Replies: 2
    Last Post: 10-10-2015, 02:22 AM
  2. VBA Macro to copy rows that meet certain criteria
    By todd.rossi82 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-29-2014, 12:38 PM
  3. Excel Duplicate Cell Search and further analyse Data using VBA
    By deepindersingh_leo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-09-2014, 07:38 AM
  4. macro to copy rows based on criteria
    By arnab0711 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-22-2011, 11:10 AM
  5. Macro to copy rows based on a given criteria
    By Roop in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-13-2011, 04:16 PM
  6. [SOLVED] Macro to copy duplicate rows (not delete)
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-19-2005, 08:06 AM
  7. Replies: 2
    Last Post: 04-18-2005, 09: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