?
Solved

Openoffice vlookup

Posted on 2011-05-02
5
Medium Priority
?
1,441 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:movieprodw
  • 3
5 Comments
 
LVL 6

Expert Comment

by:royhsiao
ID: 35506349
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
 
LVL 6

Expert Comment

by:royhsiao
ID: 35506379
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
 
LVL 1

Author Comment

by:movieprodw
ID: 35506789
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
 
LVL 6

Expert Comment

by:royhsiao
ID: 35508572
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
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 2000 total points
ID: 35710768
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

864 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