# Duplicate Lines

1. ## 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. ## 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

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

Copy N1 down

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Mrbanner" <mrbanner@swiftdsl.com.au> wrote in message
> 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. ## 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. ## 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. ## 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. ## Re: Duplicate Lines

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Mrbanner" <mrbanner@swiftdsl.com.au> wrote in message
> 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))))
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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