Duplicate Lines in a spreadsheet

markster · May 24th, 2002, 05:11 AM

I have a spreadsheet which is linked to an oracle application. Unfortunately for some reason it puts in loads of duplicate lines so for instance for 1 record it might have 6 lines. As the spreadsheet is 4000 lines long ot is a real pain to keep manually deleting the duplicate entries so I can use pivot tables etc etc. Does anyone have a solution?? Thanks

eliW · May 24th, 2002, 05:35 AM

Quote:

On 2002-05-24 04:11, markster wrote:
I have a spreadsheet which is linked to an oracle application. Unfortunately for some reason it puts in loads of duplicate lines so for instance for 1 record it might have 6 lines. As the spreadsheet is 4000 lines long ot is a real pain to keep manually deleting the duplicate entries so I can use pivot tables etc etc. Does anyone have a solution?? Thanks

hi markster,
From Menu enter: Data | Filter | Advaced Filter
Choose: "Copy to another location" and "Unique records only"
Confirm and you'll get only unique records.

Eli

[ This Message was edited by: eliW on 2002-05-24 04:36 ]

sje · May 24th, 2002, 05:36 AM

I don't know how you are linking your Excel spreadsheet to your Oracle application, but is it possible for you to use some sort of SELECT DISTINCT statement in your Oracle application to filter out those duplicate rows before they get into Excel?

Ian Mac · May 24th, 2002, 05:38 AM

Quote:

On 2002-05-24 04:11, markster wrote:
I have a spreadsheet which is linked to an oracle application. Unfortunately for some reason it puts in loads of duplicate lines so for instance for 1 record it might have 6 lines. As the spreadsheet is 4000 lines long ot is a real pain to keep manually deleting the duplicate entries so I can use pivot tables etc etc. Does anyone have a solution?? Thanks

Right-Click on your data.
Click Edit Query.
Click all the Next> buttons
Before you Finish the Query wizard.
change Return Data to Microsoft Excel
to View data or Edit query in Microsoft Query.
Goto View>Query Properties.
Check the Unique Values Only box.
Close MS Query.

If you've still got dupes then they aren't really dupes.

Hope this helps.

markster · May 24th, 2002, 05:42 AM

I don't know how the connection is made and the person who set it up here has left and no one else in our IT department has a clue!!. Thanks for the replies and I'll try eliW's method. Thanks eliW your answer seems logical to me.
markster

Ian Mac · May 24th, 2002, 05:52 AM

Quote:

On 2002-05-24 04:42, markster wrote:
I don't know how the connection is made and the person who set it up here has left and no one else in our IT department has a clue!!. Thanks for the replies and I'll try eliW's method. Thanks eliW your answer seems logical to me.
markster

If it still has the ODBC connection there, my method will bring in the data directly AND can be refreshed.

markster · May 24th, 2002, 06:02 AM

Thanks guys problem sorted. It's saved me so much time - first time I've used these boards - definitely not the last !! Thanks again.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 24th, 2002, 05:11 AM	#1
markster Board Regular Join Date: May 2002 Location: London Posts: 388	I have a spreadsheet which is linked to an oracle application. Unfortunately for some reason it puts in loads of duplicate lines so for instance for 1 record it might have 6 lines. As the spreadsheet is 4000 lines long ot is a real pain to keep manually deleting the duplicate entries so I can use pivot tables etc etc. Does anyone have a solution?? Thanks

May 24th, 2002, 05:36 AM	#3
sje New Member Join Date: May 2002 Location: London, England Posts: 4	I don't know how you are linking your Excel spreadsheet to your Oracle application, but is it possible for you to use some sort of SELECT DISTINCT statement in your Oracle application to filter out those duplicate rows before they get into Excel?

May 24th, 2002, 05:42 AM	#5
markster Board Regular Join Date: May 2002 Location: London Posts: 388	I don't know how the connection is made and the person who set it up here has left and no one else in our IT department has a clue!!. Thanks for the replies and I'll try eliW's method. Thanks eliW your answer seems logical to me. markster

May 24th, 2002, 06:02 AM	#7
markster Board Regular Join Date: May 2002 Location: London Posts: 388	Thanks guys problem sorted. It's saved me so much time - first time I've used these boards - definitely not the last !! Thanks again.