Excel/Open Office expert

[Kombi]

Hello all,

I am looking for an excel/Open Office whizz kid/student/expert who could do some work for me on a monster spreadsheet. Basically I need a formula drawn up so that I can filter down results depending on multiple criterias. Anybody know of anyone with the required knowledge?

Nicolas

[wookey]

Probably lots of people. Try asking on the Libreoffice users list (first one here: http://www.libreoffice.org/get-help/mailing-lists/ ). I guess you'd find someone there interested in a bit of paid fiddling. Make clear if it needs to work on both Libreoffice and Excel, as they have different macro models so the need for compatibility affects the approach one takes (essentially avoiding macros).

[clivejo]

Did you find someone to make your spreadsheet?

A bit more of an explanation of what you want might be handy!

[Kombi]

No, not yet, but I forgot to ask on Wookey's suggested link...

I'll try to explain what it is I need.

I have a spreadsheet with over 5000 lines in it. Each line is a serial number. Each column is a code used during the build. I need to be able to extract all the serials numbers which match the codes that I have selected. The problem is that the same code may be in column H for one serial number and in column L for another one. There is also another complication. In some cases two codes are combined into a third one. So let's say that you are after code 550. Code 550 can be found on its own; but if code 551 was also used during the build then both codes are replaced with 553. 
I hope I have made myself understood.

[clivejo]

I first thoughts are to use a database rather than a spreadsheet, and use SQL.  But I dont understand your needs so, I think Ill have to pass! 

Maybe you could provide a sample serial code?

[Countrypaul]

Hi Kombi,

not sure I've totally understood  your description, but have you looked at using autofilter and setting some custom filters? I used to be an MSCP specialising in Excel, but that was some time ago (15-20 yrs!)

Paul

[pontiff]

No, not yet, but I forgot to ask on Wookey's suggested link...

I'll try to explain what it is I need.

I have a spreadsheet with over 5000 lines in it. Each line is a serial number. Each column is a code used during the build. I need to be able to extract all the serials numbers which match the codes that I have selected. The problem is that the same code may be in column H for one serial number and in column L for another one. There is also another complication. In some cases two codes are combined into a third one. So let's say that you are after code 550. Code 550 can be found on its own; but if code 551 was also used during the build then both codes are replaced with 553.  
I hope I have made myself understood.

Hi Kombi,

Do the codes need to be in different columns or is it just that they were inputted in this way?
How many different codes are there? Are they all numbers or are there any letters involved?
How big is the spread sheet? Could it be emailed so i could look at it?

If each column had a code heading I could probably move the codes into the right columns then you could filter vertically.

[azps]

Not sure I've understood exactly what you're after, but I'll take a crack.

Add an extra column to the end of the spreadsheet. (Let's say it's column CZ for argument's sake)
Set the formula in that, to derive the thing you want to filter on.

So, for cell CZ2 (assuming row 2 is the first row of data), the formula might look something like:

Code:

=if(L2="",H2,L2)

which would take the value from column L wherever there is a value there; and the value from column H otherwise. You could also extend the IFs and do lookups into other sheets, to handle the cases where some values get replaced by others.

Then filter on your new column CZ.

[Kombi]

Hello both,

sorry for late reply but I was away...

Pontiff, no problem to e-mail the spreadsheet. Just confirm which format (excel or Open Office). It has about 5200 lines and 56 columns.
All the code are 3 digit numbers with one exception which is 3 digits and a letter.I have started to try to put each identical code in the same column but can only do it manually and for each code so it is a very painful task.

Azps,
I am not sure I understand your formula correctly. I have tried to write a similar thing but have miserably failed. the problem is that I need to filter on several codes, not just one. So, on each line, the formula needs to look for several codes. These codes  can be in a different column for each line.

[pontiff]

Excel all the way!

[wookey]

This is probably about 17 times easier to do in perl or python. Is it absolutely vital that the info remains in sheet form and the tha snwers are calculated withint the sheet? Or would a separate script processing it to give the answers be sufficient?

Sadly I'm _waay_ too busy to volunteer to help right now.

[Fintray]

Kombi

If you could email me the Excel version of the spreadsheet with a detailed note of your requirements I will have a look at it.

Fintray