Openoffice vlookup

Posted on 2011-05-02
Last Modified: 2012-05-11

I am trying to compare two sheets and output the results.

I am not sure how to do it and would love any pointers.

2 sheets 'sheet1' and 'sheet2'
compare 'sheet1' 'column b' to 'sheet2' 'column b'
if the values match compare 'column a' to 'column a'
if 'sheet1' 'column a' is greater than 'sheet2' 'column a'
than replace 'sheet2' 'column a' with the greater number
Question by:movieprodw
    LVL 6

    Expert Comment

    If I could change the format, I will move the both sheets column A to column C. In column D, I will use the column B as the look up value in vlookup to look for the match.
    I will use if statement in column E to compare the value.
    Finally, I will just let column A equal E if sheet 1 value is greater than sheet 2 value.

    Or you could use VBA.
    LVL 6

    Expert Comment

    I am using Excel and I am assuming that openOffice has the same formula like Vlookup(Lookup_value, Table_array, col_index_num, Range_lookup) because you listed your question in the MS Excel zoon.
    LVL 1

    Author Comment


    I do believe that the are very similar so any help on excel I am sure I can use for oo.

    I have 18k rows on one and 2500 rows on another and they are not ordered to match the rows.

    Another problem is that the list is more rows than oo will allow me to have, when I try to paste one table into another I get an error saying that they table is not large enough.

    LVL 6

    Expert Comment

    I check the vlookup at OO. I think they are the same. You might need to read the following post about vlookup, match, index, and if to make it work.
    Here is the link:

    Vlookup works well if Lookup_value is unique in both sheet.

    You could try to google the error message.
    It looks like there are different version of OO. Do you have the same format? You might not be able to save 2.x talbe in the 1.1x version.

    In excel, I can't copy table from 2007 to 2003 because the maximum number of rows are different.

    The maximum number of rows and cells for a OO spreadsheet file:
    The 1.1.x versions were able to handle a maximum of 32,000 rows. Update to the current version to solve this issue.


    The limitations of the 2.x Calc versions are

    maximum number of rows: 65,536
    maximum number of columns: 256
    maximum number of cells per sheet: 16,777,216
    maximum number of sheets: 256
    maximum number of cells per file: 4,294,967,296

    The limitations of the 3.x Calc versions are

    maximum number of rows: 65,536
    maximum number of columns: 1024
    maximum number of cells per sheet: 67,108,864
    LVL 20

    Accepted Solution

    You really cannot use VLookup for this task as VLookup expects the data to be sorted on the Lookup range.  If I read the question and posts correctly the data is not sorted.  Therefore we need to move to a macro that loops through both sets and compares them.  Below is on OpenOffice Basic Macro that should do the trick.  To add it to your document, press Alt-F11 (this opens tools >  Macros >> Organize Macros >> Basic).  Click the new button on the right.  Give your module a name or leave it as default and click OK.  Paste the code below over the code that it displays by default.  Now you can run your macro.

    Sub CompareMyColumns
    	Dim oCalcDoc as object
    	Dim oSheet2 as object
    	Dim oSheet1 as object
    	Dim oCellCursor1 as object
    	Dim oCellCursor2 as object
    	Dim intSheet2End as Integer
    	Dim intSheet1End as Integer
    	Dim oCellB as object
    	Dim oCellA1 as object
    	Dim oCellA2 as object
    	Dim i as integer
    	Dim j as integer
    	' Get Access to the Document
    	oCalcDoc = ThisComponent
       	' Get Last Row on Sheet 1
       	oSheet1 = oCalcDoc.Sheets.getByIndex(0)
       	oCellCursor1 = oSheet1.createCursor()
       	intSheet1End = oCellCursor1.getRangeAddress().endRow
    	' Get Last Row on Sheet 2
       	oSheet2 = oCalcDoc.Sheets.getByIndex(1)
       	oCellCursor2 = oSheet2.createCursor()
       	intSheet2End = oCellCursor2.getRangeAddress().endRow
       	For i = 0 to intSheet2End
       		'Get the Value for this row in Column B
       		oCellB = osheet2.getCellByPosition(1, i)
       		' Loop Through to find Match in Sheet 1
       		For j = 0 to intSheet1End
       			' Check if we found a Match
       			If oCellB.string = oSheet1.getCellByPosition(1, j).string Then
       				' Check the values in Colum A
       				oCellA1 = osheet1.getCellByPosition(0, j)
       				oCellA2 = oSheet2.getCellByPosition(0, i)
       				If oCellA1.value > oCellA2.value Then
       					oCellA2.value = oCellA1.value
       				End If
       				' Found a Match go to next line
       				Exit For
       			End If
    End Sub

    Open in new window


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now