+ Reply to Thread
Results 1 to 6 of 6

Duplicate Lines

  1. #1
    Mrbanner
    Guest

    Duplicate Lines

    Hi All,
    Just have a question
    i am running a report daily that will have many items on there the same
    on a daliy basis.
    to stop me from checking over orders twice i am wanting to import a new
    sheet into the spreadsheet paste the new data and then
    do a fuction or something that if sheet1 D2 and H2 = the same as one of
    my lines in the sheet2 (1-8000) it will display what is typed into
    Sheet 1 N


  2. #2
    Max
    Guest

    Re: Duplicate Lines

    One guess .. maybe something along these lines

    In Sheet2, copy & paste in the formula bar for say, N1:

    =INDEX(Sheet1!$N$1:$N$8000,
    MATCH(1,($D$1:$D$8000=Sheet1!D1)*($H$1:$H$8000=Sheet1!H1),0))

    Then array-enter the formula by pressing CTRL+SHIFT+ENTER
    (instead of just pressing ENTER)

    Entered correctly, Excel will insert curly braces { ... } around the entire
    formula (Don't type-in the braces !)

    Copy N1 down

    Adapt to suit ..
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --
    "Mrbanner" <[email protected]> wrote in message
    news:[email protected]...
    > Hi All,
    > Just have a question
    > i am running a report daily that will have many items on there the same
    > on a daliy basis.
    > to stop me from checking over orders twice i am wanting to import a new
    > sheet into the spreadsheet paste the new data and then
    > do a fuction or something that if sheet1 D2 and H2 = the same as one of
    > my lines in the sheet2 (1-8000) it will display what is typed into
    > Sheet 1 N
    >




  3. #3
    Mrbanner
    Guest

    Re: Duplicate Lines

    Thanks Formula searches the way I need
    But for some reason it is not Inputting the Correct Information into AG
    Cells
    =INDEX(Sheet1!$AG$1:$AG$8000,MATCH(1,($D$1:$D$8000=Sheet1!D1)*($J$1:$J$8000=Sheet1!J1),0))
    With the data I have it will not be in the same order all the time and
    items and lines will be removed and added. I think this code is for if
    the line stay the same

    For example
    On Sheet 1
    D= Account Number
    J= Product Code
    AG= Notes

    Sheet 2 is the same
    D= Account Number
    J= Product Code
    AG= Notes

    But the information inside the cells will mostly be different
    And in different order.
    What I needs that if Sheet1 (D & J) upto 8000 lines = (Sheet2 (D & J)
    upto 8000 lines then Sheet 2 (AG) = Sheet 1 (AG)

    Sorry hard to explain
    In a nut shell
    Sheet1 (D&J) LINE 5)
    Sheet2 (D&J) LINE 800)
    Both Match I have notes typed In AG(Sheet1)
    Now I need these notes to be shown in Sheet2(line 800) now?


  4. #4
    Max
    Guest

    Re: Duplicate Lines

    "Mrbanner" wrote:
    ....
    > Sheet1 (D&J) LINE 5)
    > Sheet2 (D&J) LINE 800)
    > Both Match I have notes typed In AG(Sheet1)
    > Now I need these notes to be shown in Sheet2(line 800) now?


    Think this orientation should now be correct ..

    In Sheet2,

    Put in AG2, and array-enter:
    =INDEX(Sheet1!$AG$2:$AG$8000,
    MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$J$2:$J$8000=J2),0))
    Copy AG2 down

    And perhaps better with error-traps to return blanks ("") for non-matching
    lines, etc, we could put instead in AG2, array-enter, and fill down:

    =IF(OR(D2="",J2=""),"",
    IF(ISNA(MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$J$2:$J$8000=J2),0)),"",
    INDEX(Sheet1!$AG$2:$AG$8000,
    MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$J$2:$J$8000=J2),0))))
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --



  5. #5
    Mrbanner
    Guest

    Re: Duplicate Lines

    thanks mate workz great
    =IF(OR(D2="",J2=""),"",
    IF(ISNA(MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$J$2:$J$8000=J2),0)),"",

    INDEX(Sheet1!$AG$2:$AG$8000,
    MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$J$2:$J$8000=J2),0))))


  6. #6
    Max
    Guest

    Re: Duplicate Lines

    You're welcome !
    --
    Rgds
    Max
    xl 97
    ---
    Singapore, GMT+8
    xdemechanik
    http://savefile.com/projects/236895
    --
    "Mrbanner" <[email protected]> wrote in message
    news:[email protected]...
    > thanks mate workz great
    > =IF(OR(D2="",J2=""),"",
    > IF(ISNA(MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$J$2:$J$8000=J2),0)),"",
    >
    > INDEX(Sheet1!$AG$2:$AG$8000,
    > MATCH(1,(Sheet1!$D$2:$D$8000=D2)*(Sheet1!$J$2:$J$8000=J2),0))))
    >




+ 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