use a simple perl script:

#!/usr/bin/perl

while () {

chomp;

($ID,$data) = (/^(\S+)\s+(\S+.+)/);

@list = split (/\s+/,$data);

sort @list;

%seen = ();

@unique_list = grep { ! $seen{$_}++ } @list ;

print "$ID\t" . join (" ",@unique_list) ."\n";

}

Input:

UGT1A3r 7364 54575 54575

UGT1A4r 54575 54490 54576

UGT1A8r 7363 54575 7363 54576

UGT1A9r 54490

UMPK 51727

UMPK2 51727

Output:

UGT1A3r 7364 54575

UGT1A4r 54575 54490 54576

UGT1A8r 7363 54575 54576

UGT1A9r 54490

UMPK 51727

UMPK2 51727

not sure if this is what you want, but check out this video which shows how to mark duplicates in excel 2007:

http://www.youtube.com/watch?v=j7yIhwBqYzM

use a simple perl script:

#!/usr/bin/perl

while () {

chomp;

($ID,$data) = (/^(\S+)\s+(\S+.+)/);

@list = split (/\s+/,$data);

sort @list;

%seen = ();

@unique_list = grep { ! $seen{$_}++ } @list ;

print "$ID\t" . join (" ",@unique_list) ."\n";

}

Input:

UGT1A3r 7364 54575 54575

UGT1A4r 54575 54490 54576

UGT1A8r 7363 54575 7363 54576

UGT1A9r 54490

UMPK 51727

UMPK2 51727

Output:

UGT1A3r 7364 54575

UGT1A4r 54575 54490 54576

UGT1A8r 7363 54575 54576

UGT1A9r 54490

UMPK 51727

UMPK2 51727

paste as transposed format. Then use if formula to lookup and mark the dups. Then sort out those marked dups, delete them. Done!

Some of the mentioned methods are still semi-manual (love the perl approach, you will need to export your excel to a text file and provide that as input for the script). If I understand it correctly, you got those records in distinct rows (UGT1A3r, UGT1A4r etc) of variable lengths. If you still want to keep your data in Excel, you might use a VisualBasic loop/recursive call to compare each nonempty cell, row by row, against every other nonempty cell in that row/in every row (your request is not very clearly formulated). If you can parse the data into an SQL database, you can use SELECT DISTINCT on the table containing the transcripts.

Thanks Michael Mueller ... but I tried with this its not working and not beneficial for me because it leaves the duplicates in the sheet...

@ Yuwen Li , If I marked the duplicates and removed those then it removes all the duplicates does not keep even the unique values. I just want unique value there in records and want the subsequent duplicates to be removed.

@ Paul T Tamas, my data is in txt format for editing purpose I open it through the excel as it makes easier. But yes two yrs ago I have used the SQL queries I will try with DISTINCT function. Thanks a lot

I use usually this simple method in excel:

=if (a1=a2,"remove","OK")

Then you filter the column!

Is that clear? I can send you an example if you wish.

Good luck :)

The easiest way to remove duplicates without involving any formula/script is

1. transpose your data into this way

UGT1A3r UGT1A4r UGT1A8r UGT1A9r UMPK UMPK2

7364 54575 7363 54490 51727 51727

54575 54490 54575

54575 54576 7363

54576

2. select first column, then click "remove duplicates" button from DATA menu, select continue with current selection, press OK. DONE!

The only problem is you have to do it column-by-column!!!

Another way I can think of is Pivot Table... but again have to do it column-by-column...

Dong Tong

Thanks it works but as you told need to do column by column ..... it takes much time and I have about 4000 column not 100 to do...

or PivotTable

You might try ASAP utilities for Excel (http://www.asap-utilities.com). The Excel add-in is free for Home users, Students and Charitable Organizations. Under the select submenu (1. option) you will find a possibility to automatically select cells with duplicates, Under the 'Columns & Rows* submenu (1st option), you will find the possibility to automatically select, hide or delete rows or columns with duplicates in a chosen range. I found the collection quite useful.

Reply:

I am afraid you need to write some script to speed-up the process. If you do not want to write the script or install plugins as suggested by Gunter, the only option that I can think off is Pivot Table, but again, this will spend you time to do it because the layout of the input table to PivotTable is different.

To do pivot table, you need to arrange your data into 2 columns, like this:

title value

UGT1A3r 7364

UGT1A3r 54575

UGT1A3r 54575

UGT1A4r 54575

UGT1A4r 54490

UGT1A4r 54576

UGT1A8r 7363

UGT1A8r 54575

UGT1A8r 7363

UGT1A8r 54576

UGT1A9r 54490

click pivot table button from INSERT menu, put TITLE on the row label, VALUE on the column label, and VALUE on the Values. Choose to display Values field as AVERAGE.

Good luck.

You could do that with R with the following script

{

#your file named essai.txt in text format with tab as separators

tab

Use filters in Excel. You can then remove unwanted data.

Ankit.

Hi, did you fix your problem? If not, then I'd do it using a Visual Basic script, which has the advantage of running within Excel. If you're interested I can send you the script.

Best

Mathias

I don't know your duplicate data is each column or row and or both,

if it is on column, the first, you must sort column then run if(a1=a2,"",a2)

and this formula run for each column.

Best

B.Ghermezcheshmeh

In excel==>"data tools"==>"remove duplicates" exist. You also can determine how detect duplication.

For all the people who highlight the "remove duplicates" tool from excel, The intial post indicated that the name could appear in different lines anf the removed tool won't allow that. There are three posts (mine, those with the perl script and those by Mathias Lorieux) that present solutions to the proposed problems.

Rather than write other replies, read the previous ones.

Simply select your data set then go to data ribbon and click on remove duplicates

job done

@Ashish Awasthi only works if data is on single column and excel version 2007 and above

the idea of Ashish Awasthi was right and i should say that excel has made for items that is not based for programming ,so if anybody want to use for database it is not good.

Hi,

In your example data you've got duplicates within the same row AND between rows (eg #54575). Do you want to remove only duplicates within each row or also eliminate them between rows?

Mathias I wanted to rremove duplicates from within each row not between... but now I succeed to do this one

Thank you very much evryone for support and nice ideas....

OK! Happy to hear that you fixed your problem! How did you finally do it?

Perl ...

Perl is probably the most elegant way! However, if you want to do everything within Excel this could be code to use. You need to 1/ rename the sheet that contains your data as "Transcripts", 2/ Create a new worksheet and name it "Output", 3/ go to the Developer tab in Excel, then "Editor", 4/ go to "Insert/Module" Menu, 5/ Paste the following code, and 6/change the values of the first four parameters in the macro to your real values.

To run the code, simply press F5 of go to "Run/Run macro" Menu.

Sub Remove_Duplicated_Transcripts()

First_Row = 1

Last_Row = 4000

First_Column = 1

Last_Column = 16

'Put data in an array for faster execution

ThisWorkbook.Sheets("Transcripts").Activate

My_Gene_Data = ThisWorkbook.Sheets("Transcripts").Range(Cells(First_Row, First_Column), Cells(Last_Row + 1, Last_Column + 1)).Value

ReDim My_Output(First_Row To Last_Row, First_Column To Last_Column)

For i = First_Row To Last_Row

For j = First_Column + 1 To Last_Column

For k = j + 1 To Last_Column

If My_Gene_Data(i, k) = My_Gene_Data(i, j) Then

For l = k To Last_Column - 1

My_Gene_Data(i, l) = My_Gene_Data(i, l + 1)

Next l

My_Gene_Data(i, Last_Column) = vbNullString

End If

Next k

Next j

Next i

ThisWorkbook.Sheets("Output").Activate

[A1].Resize(Last_Row, Last_Column) = My_Gene_Data

End Sub

wow, this could may helpful i will surely try.... thank you Mathias

U can simply right a code in excel too

If A2 has the thing to be checked for duplicates say in column A only.

then in B2 right

=if(countif(A:A,A2)>1,"",A2)

And drag this formula to the end of column A.

This will only give an output in B column if the value in corresponding cell in column A is unique.

moreover this formula can be modifed to change the range to be tested

1. If u want to check duplicates in A as well as B column. simply modify the formula

in C2 write

=if(countif(A:B,A2)>1,"",A2)

2. if u want to check duplicates for A coumn in column B whther present or not

in c2 write

=if(countif(B:B,A2)>=1,A2,"")

THis will give output only those values of A which are also in B column

A sample excel file for all 3 cases above

Just let us know if it works as expected!

Manually it could be on excel sheet first of all select whole column or row you want to find out duplicates and then click on HOME-->Style-->Conditional Farmatting-->select NEW RULES -----> FORMATE ONLY UNIQUE OR DUPLICATE VALUES -----> FORMATE-----> Select color of your choice and Font REGULAR. Click OK and OK.

Hi

If you do not familiar with programming, simply you can export your data to an MS. Access table. then you must run "Find Duplicate Query" to find duplicate entry in any column.