# Analyse data contained in Excel file

I have been using computers for about 16 years, never used excel or similar software, maybe once. So my excel knowledge is 0. Maybe what I'm about to ask is easy maybe not. The first part of the question is:

I have a big excel file, which I'm editing in Gnumeric, and I also have kingsoft office, which looks like this

I'm interrested in cloumn D, E, F, G, H, I. Each rank represents a sequence.

3,4,6,12,16,19 is a sequence. 1,6,21,24,38,42 is another sequence.

I want to compare these sequences and see if there's a sequence repeated more than once. Order doesn't matter. for instance 3,4,6,12,16,19 is the same sequence as 19,4,6,12,16,3, it's a duplicate.

I want to represent them in a chart which highlights the duplicates and if possible the dates when the duplicate sequence was created. The dates are in coloumn B and C.

I'm running Xubuntu 14.04 64 bit.

• I don't care about the tool used, maybe I have to download another software or use a web service or maybe it could be down with Gnumeric.
• I'd prefer if the final result including the chart can be exported to something like XML because I still have to sort it, using python, it's the second part of the question, I won't ask about it now, I don't want to make the question more complicated. I just want the result to be in a file that can be easily manipulated using programming languages.
• The tool used must be free.
• I prefer not to use microsoft software running on Wine, either Linux software or web services.
• I might ask the second part later, here or on programmers.SE, as it's more about programming – Lynob Apr 4 '14 at 12:25
• I'm confused. Do you really use microsoft-excel? If not, please remove the tag. – Martin Thoma Apr 4 '14 at 12:39
• @moose, I'm not using excel but the file I'm working with was created in excel – Lynob Apr 4 '14 at 12:42
• You could export it to CSV and use Python to do the rest. See docs.python.org/2/library/csv.html – Martin Thoma Apr 4 '14 at 12:46

Since you are going to end up in python anyway why not just read the Excel file directly using python in the first place, and do whatever processing you want there? I use xlrd for python all the time and it works great.

Not xubuntu specific but should get close enough

• yes that what I thought before asking this question but I asked anyway thinking that the task I have to do could be done from excel or whatever without using python. And I've been told to use csv which means numpy + matplotlib – Lynob Apr 4 '14 at 19:54
• Ok, so I'm getting off topic but I guess it's only a comment so maybe it's OK. I guess I don't totally understand the requirements. If you have to end up with CSV then I'd still use python for the half dozen lines of processing it would take to find the duplicates then just write it out as CSV. If you need to end up with an Excel that is formatted I would do the same thing but create the file with xlwt or maybe just to the sorting with python then put the data back. – Dennis Apr 4 '14 at 20:22

Once you need from program - exporting data(table) to CSV (which is just comma separated lines - in general)
Most table processors can export to CSV and import CSV.

According Gnumeric docs (below table is sticky note)

Files in the file formats marked as Save/Open can be opened or saved with the
Open, Save, and Save As menu items in the File menu. Files in the file formats
marked as Import/Export can be imported or exported with the items on the Import


After exporting to CSV data can be processed with the script - generating another CSV file, which can be imported back to the program.

• Ok I'll see how it works when I get to my laptop. Im using the android app now – Lynob Apr 4 '14 at 12:57

You can get along with Excel without programming skills!

1. Make 6 new columns, e.g. AA1...AF1

2. Sort the six values D, E, F, G, H, I: Make AA1 the minimum with =SMALL(D1:I1,1), AB1 the second minimum with =SMALL(D1:I1,2) and so on.

3. Then make AG1 the concatenated sequence with = AA1 & "-" & AB1 & "-" & ...

4. After that you can sort the Excel sheet by the value AG1

5. Find the dupicates is easy with the formula in colum AH, eg. for AH40: =IF(AG39<>AG40;"";"duplicate")

Hope you got it. Have fun!