+ Reply to Thread
Results 1 to 11 of 11

Duplicate or copy selected data from worksheet to another if condition is met

  1. #1
    Registered User
    Join Date
    05-22-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    10

    Duplicate or copy selected data from worksheet to another if condition is met

    Hi

    i need help with a formula that will duplicate selected data from a master worksheet into another worksheet IF a condition is met. example:

    Master worksheet contains job title, name, branch, date started
    if the person is from "RMB" branch (the condition) then the following data will be copied into the "RMB" worksheet - job title, name and date started.
    and if the person is from "HR" branch then the following data will be copied into the "HR" worksheet - job title, name and date started.

    can someone please point me into what formula would be the best for me to use?

    Sample.xlsx

    thanks

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Duplicate or copy selected data from worksheet to another if condition is met

    Perhaps something like this?
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    05-22-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Duplicate or copy selected data from worksheet to another if condition is met

    Thanks very much that's exactly what i'm after!(but instead of the officer being a number it would be a name)
    could you please explain to me how this was done!
    Last edited by ramonjr17; 05-22-2013 at 02:23 AM. Reason: officer = name

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Duplicate or copy selected data from worksheet to another if condition is met

    I'll try...You see my English does not help me..

    First we create a (Named)List of ALL the officers(column M in Master sheet).

    For knowing the Branch Name we create a helper and hidden column(G), in eatch Branch sheet.

    Then we use a simple INDEX & MATCH, looking for the results in the first Branch. iF FINDS NO RESULTS THERE, THE first iferror, DRIVES THE FORMULA TO SEARCH IN THE SECOND Branch and if also no results there the second IFERROR, helps the formula to give an empty cell and not an error.

    Hope this helps you.

  5. #5
    Registered User
    Join Date
    05-22-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Duplicate or copy selected data from worksheet to another if condition is met

    i had more time to look at this and sorry fotis looks like this actually isn't it.
    I don't think i explained it correctly, ill try again.

    what I need is a formula that will simultanously copy what the user is typing into the master sheet (while also duplicating what already exists ) into another sheet, that other sheet where it will be copied is determined on a value entered into a cell in the master sheet, for example - on the newly attached document, the value or condition cell is F7:F20 (Type), if the data entered inbetween these cells e.g. FRUIT the data within that row will be copied over into FRUIT sheet, if DRINK, it will copy row into DRINK Sheet.

    If the fruit/drink entered already exist, it will still make a copy of the data entered but it will make a new entry, it will not update previous entry e.g.
    fruits sheet contains ORANGE and has 50 in stock
    new user inputs ORANGE and now 200 stock

    on the master and fruit sheet it will have both entries of 200 and 50.

    in other words - all separate entries copied.

    i'm sure it's a very simple formula but i can't seem to find the one or combination that i need! im very new with this so any help would be greatly appreciated.

    new attachment: Attachment 237694

    thanks
    Last edited by ramonjr17; 05-23-2013 at 02:30 AM. Reason: add new attachment

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Duplicate or copy selected data from worksheet to another if condition is met

    Hi

    i get this message when i try to open your new file.

    ..Invalid Attachment specified. If you followed a valid link, please notify the administrator
    Can you pls try again?

  7. #7
    Registered User
    Join Date
    05-22-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Duplicate or copy selected data from worksheet to another if condition is met

    here you go:

    Sample_v2.xlsx

    please let me know if it doesnt work

  8. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Duplicate or copy selected data from worksheet to another if condition is met

    Hoping that i got your goal try this ARRAY formula

    =IFERROR(INDEX(Master!D$7:D$20;SMALL(IF(Master!$F$7:$F$20=$E$4;ROW(Master!D$7:D$20)-6);ROW(Master!D1)));"")

    Is this what you mean?
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-22-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Duplicate or copy selected data from worksheet to another if condition is met

    Thanks Fotis this is exactly what i need.
    im trying to workout the formula! if you could please help me understand!

    =IFERROR(INDEX(Master!D$7:D$20;SMALL(IF(Master!$F$7:$F$20=$E$4;ROW(Master!D$7:D$20)-6);ROW(Master!D1)));"")

    above i've bold and underlined what i dont understand, the "SMALL" "ROW" "-6" and "D1"

    Thanks so much
    Last edited by ramonjr17; 06-03-2013 at 10:27 PM.

  10. #10
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Duplicate or copy selected data from worksheet to another if condition is met

    ....Thanks Fotis this is exactly what i need.
    im trying to workout the formula! if you could please help me understand!

    I'll try....

    SMALL(IF(Master!$F$7:$F$20=$E$4

    This part of the formula looking for values in range Master!$F$7:$F$20, that match with value in E4.

    ROW(Master!D$7:D$20)-6)

    Using this we "say" to the formula in which row must start looking. As your data start in row 7, we use the -6.

    If for example your data start in row 35...we should use -34 etc.....

    ROW(Master!D1))

    This part of the formula "says": Give me the first value that mach. As you copy down this becomes D2 ,D3, D4 ETC...giving us the second the third etc....values that match.

  11. #11
    Registered User
    Join Date
    05-22-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Duplicate or copy selected data from worksheet to another if condition is met

    great explanation, thanks very much!
    excel spreadsheet completed with your help!

+ 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