1
vote
2answers
59 views

How to get VLOOKUP to return the *last* match?

I'm used to working with VLOOKUP but this time I have a challenge. I don't want the first matching value, but the last. How? (I'm working with LibreOffice Calc but an MS Excel solution ought to be ...
0
votes
0answers
15 views

How do I change the EOL character when importing a CSV file into Open Office Calc?

I've got a CSV file that has enclosed newline (\n and \r, mixed) characters in its data. Because the consumer of that file is not able to handle that correctly (out of my control) we decided to use ...
6
votes
2answers
234 views

OpenOffice Calc: reduce file size when saving as Excel

I'm working on a spreadsheet together with another person, sending new versions forth and back by email. It contains nothing fancy. While he uses Windows I'm on Ubuntu 12.04.4 LTS. The first version ...
1
vote
2answers
42 views

What's the best way to cut out all text before or after a matching string with excel or open office?

I have a column of naughty data, that needs to be standardized. Its a single column, so I'll just include a snippet of it it here: pop_density_2010 7002854600000000000854.6/sq mi (329.97/km2) ...
0
votes
1answer
145 views

How do I sum across a list of columns defined in an extra column or cell in excel

Suppose columns A, B and C contain numbers. I want to sum everything in any of these columns if they are listed in an extra column or cell. The columns to be summed can be anywhere in the spreadsheet. ...
0
votes
0answers
30 views

how can I merge in master record details to each row in excel or openoffice?

Say I have sheet 1 with: company1 90230 CA company2 80014 CO company3 15090 PA And sheet 2 with: company1 Bob Smith company1 Sue Smith company3 John Doe How can I produce sheet 3 with: company1 ...
0
votes
1answer
72 views

Are spreadsheet programs inherently unsuitable for “one row=one case” data?

Often when I'm working with large data sets, I structure the data in a way where every row equals one specific case. For example, in a psychological experiment, I input the data so that all data ...
0
votes
0answers
52 views

Macro to manage sport ranking and calendar?

I need to write a macro (or a different easy way) to manage ranking and calendar for curling tournament. The event will follow the Shenkel system first match determined by general draw after that ...
0
votes
1answer
69 views

Conditonal sum in Excel

I would like to write the formula (in OpenOffice, but I imagine it won't be any different than Excel) to perform the following calculation: -> Go through the values in column B from B1 to B30 and add ...
0
votes
1answer
402 views

How can I use extremely large numbers in my spreadsheet programs?

If I try to calculate large exponents in LibreOffice Calc or OpenOffice.org Calc, the results come out to be erroneous. See these two examples where I calculate 13^18, Using LibreOffice Calc Using ...
1
vote
1answer
177 views

Getting date of last change as a cell value in Open Office Calc

I have a few Open Office spreadsheet files (let us call them source files), each containing the financial info of a particular office of ours. I also have a summary spreadsheet, that reads some key ...
0
votes
3answers
2k views

How to change the hh:mm:ss format in spreadsheets

Currently, I have a data set which is "mm:ss" and needs to stay / be calculated that way. The problem is, Microsoft Excel, Google Spreadsheets and OpenCalc all interpret "mm:ss" as "hh:mm" and to add ...
1
vote
1answer
905 views

Spreadsheet VLOOKUP: Search in second column and return first

In Excel or OpenOffice calc I have | Foo | Bar | +-----+-----+ | A | 1 | | B | 2 | Is it possible to use VLOOKUP to search a value in the Bar column and return the value in the Foo column?
2
votes
2answers
413 views

Modify value of a cell using the old value as part of the new one?

I have a table in which row 2 has tons of columns. Each of those columns in row 2 has a varying value in the range of 1-1000000 represented by X in the example below. How can I add modify the cells ...
3
votes
2answers
984 views

Excel/Calc/Whatever: Flip my axes - ie reflect my table across the diagonal

Lets say I have a table and I want it flipped across the diagonal. So instead of X by Y its Y by X... Before: A B C RED 1 2 3 BLUE 4 5 6 ...
1
vote
2answers
11k views

Pull row of data from one place in spreadsheet to another

I have a list of names and their assorted properties. I am trying to find a way to quickly move an entire row's worth of data into a different sheet so I can manipulate it. Currently I can ...
13
votes
10answers
22k views

Export Excel graphs as vector graphics files (e.g. SVGs)?

How can I export a graph generated in Excel as an editable vector graphics file, e.g. a SVG? When I right click on a graph in Excel the 'Save as Picture...' option only gives non-vector formats (like ...
0
votes
2answers
593 views

Spreadsheets: How do I perform functions on a set/array described by a row of (count, value) pairs?

I have excel 2000, google docs, and the latest open office calc available to me. I'm after a solution in any of those, ideally. I have a multiset of data described by rows of (count, value) pairs. ...
1
vote
1answer
864 views

Calc - How to combine rows from multiple sheets into 1 main sheet

I have several statements separating my various bank accounts in Calc, they all use the same columns and have a Date column which I sort them by. I want to create a 'Overview' sheet which contains ...
0
votes
1answer
903 views

how to find cells that have a certain text in open office's calc (excel might work also)

I need to calculate averages on my expenses spreadsheet, so I figured out I could do something like this: - get all cells that have the text "electric bill" - get the cell at the right of that one, ...
3
votes
1answer
1k views

How to set 2 conditions / criterias for VLOOKUP / LOOKUP / etc in OpenOffice Calc (or Excel)

I have this spreadsheet that started as a silly aid for a game (Mafia Wars 2), but grew into a tricky spreadsheet question. In the game your character have 9 "slots" for weapons and armors, 1 for ...
2
votes
1answer
327 views

Are there nonlinear optimization implementation for OpenOffice Calc?

Are there nonlinear optimization implementation for OpenOffice Calc? I found one: http://extensions.services.openoffice.org/project/NLPSolver . But the methods provided there are evolutionary ...
1
vote
2answers
345 views

Advanced spreadsheet auto-formatting

Is it possible to autoformat spreadsheet like this IF A1 = C1 then A1:E1 cells background becomes light gray I use OO Calc, but it's ok if you know how to do it in MS Excel or Google spreadsheet
1
vote
1answer
4k views

How to conditionally highlight cells in OpenOffice Calc?

I have a big chunk of data in an OpenOffice spreadsheet. To make real information standout more, I would like cells with an absolute value over a certain threshold to be highlighted in red. Is there ...
3
votes
2answers
4k views

Calculations in terms of degrees minutes seconds in excel , libreoffice/openoffice or any other free spreadsheet

My dad's gotten into astrology, so he needs to do calculations for it - and most books use the Degrees Minutes Seconds nonmenclature - is there any way to set excel or open office calc to handle ...
1
vote
1answer
448 views

How can I open a subset of a large (35MB) .xlsx file?

I have Ubuntu 10.04 running on a Dell Optiplex with 4GB of memory and two 3.16GHz processors. I received a 35MB spreadsheet. It opened in Gnumeric after 5 minutes with errors, and it hasn't opened ...
0
votes
1answer
61 views

How to list people below the average in Excel/OpenOffice?

I have a spreadsheeet that shows a list of people and how many appointments have been booked for each of them in the past week. It looks a bit like this: I want to make a list underneath that ...
0
votes
3answers
3k views

Export spreadsheet and import as table into mysql

What's the easiest way to export a table from either MS-Excel or OpenOffice calc and import it into a mysql database, where: The table will be imported as a table in Mysql The headers of the table ...
0
votes
1answer
1k views

Remove last n chars from a column in a CSV?

Is there a way in microsoft excel or open office calc to remove the last n chars from a field. Lets say all fields in one column are 31 chars long and I want to remove the last 6 chars. Is there ...
3
votes
2answers
422 views

Days in year between certain dates

In OpenOffice (or Excel), I need to calculate the exact number of days that a certain date range falls within a certain year. Eg. the date range 2009/1/1 to 2010/11/01 has 365 days in 2009 and 305 ...
1
vote
4answers
115 views

What is the least-error-prone way to share CSV data?

One of my jobs is to upload product data to the company store. The data is initially pulled from the company database by my supervisor, and she puts it into an Excel spreadsheet and exports as CSV. I ...
2
votes
2answers
2k views

Database access through odbc driver and excel

Is it possible to access a database via ODBC and access this in MS Excel or OpenOffice, then exporting the database into a flat file? The program I am accessing data only allows an ODBC connection ...
2
votes
1answer
102 views

Repeat rows at top of printed page

In Excel, under Page Setup, there is an option for "Print Titles", where you can specify a number of rows to repeat at the top of the printed page, as well as a number of columns to repeat at the left ...
0
votes
3answers
1k views

Excel: How to sum values in a column based on multiple criteria

I have a list of criteria in row 20, like this: A20: "My Company, Inc." B20: "Client Expenses" C20: "Travel" The data is below, like this: B40: "Client Expenses" C40: "My Company, Inc." D40: ...
0
votes
4answers
912 views

Difference between CSV in Excel and CSV in OpenOffice.org

There are different implementations of Comma Separated Values; two of them are Microsoft Excel and OpenOffice. Is there any difference between these CSV implementations? For example both can open ...
1
vote
1answer
2k views

Excel or open office calc how to remove last white space? [closed]

Excel or open office calc how to remove last white space?
3
votes
1answer
3k views

Ubuntu: Convert OpenOffice Calc to Excel workbook using CLI

I need to create an automated report in a spreadsheet format. Unfortunately, There seems to be an easy way to create these reports using OpenOffice Calc, but upper managements wants them in MS Excel ...
3
votes
2answers
920 views

Prevent Excel from updating absolute references when target is moved

I would like to prevent OpenOffice Calc or Microsoft Excel from updating absolute references when their target cells are moved. For example, if cell B2 is "=$A$1" and you cut A1 and paste it in A2, ...
3
votes
3answers
5k views

How to create diagram in spreadsheet with dates on x-axis?

I recently became a father to twin boys. Naturally there is a lot of weighing and measuring, and we record these numbers in a spreadsheet. I use OpenOffice.org Calc (but I'm open to solutions for ...
2
votes
5answers
5k views

In a spreadsheet (Excel, Google Docs, OpenOffice), how do I transpose data from rows to columns, but only non-null cells?

I work with all three spreadsheet programs listed above, and I believe my question can be answered the same or similarly for all three. On my sheet, I have a top row full of column headers. The ...
1
vote
2answers
6k views

embed pictures from web by url in excel spreadsheet (or oo calc)

I want to embed a picture from the web in an excel spreadsheet. I want to embed the picture by url, in the same manner that pictures are embedded in an html page. I don't want to download the picture ...