Openoffice vlookup

Hello,

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
LVL 1
movieprodwAsked:
Who is Participating?
 
ltlbearand3Commented:
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 >> OpenOffice.org 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.

-Bear
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()
   	oCellCursor1.gotoEndOfUsedArea(False)
   	intSheet1End = oCellCursor1.getRangeAddress().endRow

	' Get Last Row on Sheet 2
   	oSheet2 = oCalcDoc.Sheets.getByIndex(1)
   	oCellCursor2 = oSheet2.createCursor()
   	oCellCursor2.gotoEndOfUsedArea(False)
   	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
   		Next
   	Next
End Sub

Open in new window

0
 
royhsiaoCommented:
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.
0
 
royhsiaoCommented:
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.
0
 
movieprodwAuthor Commented:
Roy,

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.

0
 
royhsiaoCommented:
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:
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_VLOOKUP_function
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_MATCH_function
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_INDEX_function
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_IF_function

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 OpenOffice.org 1.1.x versions were able to handle a maximum of 32,000 rows. Update to the current version to solve this issue.

2.x

The limitations of the OpenOffice.org 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
3.x

The limitations of the OpenOffice.org 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
===
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.