All right, I'm quite new to OpenOffice calc - And i've been googling around for a solution and i can't find any.
I'm working on a price-list for different articles where the prices changes by every day. What i want to do is just Copy The price-list i get on my e-mail daily, and paste it into sheet1.
On sheet2 i want some kind of function or macro that's searching for a specific article-number(which has a different row-place each day on the price-list).
When the number is found I want the Entire Row(lets say its on row 267 on sheet1) to appear on Row 1(sheet2).
For an example:
Sheet1
_______ A___________B_____________C_____________D
265 | 15211_______Kiwi 800gr_______IT___________9,85
266 | 17228_______Plums___________CL_________22,75
267 | 18214_______Sharon_________ ES___________1,88
268 | 20234 ______ Avocado________PE___________4,50
269 | 59868_______Pineapple _______ES__________9,70
...
And i want the specific Article-number to be found. (Lets say "18214____Sharon") - And i want it to go to my sheet2 row1 to look like:
sheet2
_______ A___________B_____________C_____________D
1 | 18214_______Sharon_________ ES___________1,88
2 | etc
3 | etc
4 | etc
5 | etc
...
And then i might want another specific article-number to appear on sheet 2 - row 2,3,4,5, etc.
This drives me crazy and i would really appreciate if someone took their time to help me out with this one!
[Solved] Find and copy an entire row.
[Solved] Find and copy an entire row.
Last edited by r0bs on Tue Jan 29, 2013 12:02 pm, edited 1 time in total.
Re: Find and copy an entire row.
Here is a solution in the attached file. The OFFSET() function on sheet 2 columns B,C and D is an array formula, so you need to highlight the three cells in a row, type in the formula and then confirm with CTRL+SHIFT+ENTER. See if that formula makes sense and don't hesitate to come back with questions.
- Attachments
-
- Pricelist.ods
- (10.02 KiB) Downloaded 260 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Find and copy an entire row.
I have been looking for a formula to do much the same thing as the poster wants. I've not used arrays much and I am wondering what the last four parameters in your formula do "-1;0;1;3".
Thanks
Thanks
OOo 2.4.X on Ms Windows XP
Re: Find and copy an entire row.
The formula is
Which has the form
so in my formula they are
Reference = Sheet1.$B$1
RowsToMove = MATCH(A1;Sheet1.$A$1:$A$10000;0)-1
ColumnsToMove = 0
Height = 1
Width = 3
The formula starts in cell B1 of Sheet1. The MATCH() formula returns the location of the reference text (the text in cell A1 of Sheet2) in the array A1:A10000 of Sheet1. Because that location is numbered starting from 1, we have to subtract one from it to get the correct number of RowsToMove down from B1. That is, if the Match is in the first row, the OFFSET() function needs to move down zero rows. We want to stay in column B, so ColumnsToMove is zero. Finally, we want the the returned range to be one row high and 3 columns wide, spanning columns B through D of a single row.
Code: Select all
=OFFSET(Sheet1.$B$1;MATCH(A1;Sheet1.$A$1:$A$10000;0)-1;0;1;3)
Code: Select all
=OFFSET(Reference;RowsToMove;ColumnsToMove;Height;Width)
Reference = Sheet1.$B$1
RowsToMove = MATCH(A1;Sheet1.$A$1:$A$10000;0)-1
ColumnsToMove = 0
Height = 1
Width = 3
The formula starts in cell B1 of Sheet1. The MATCH() formula returns the location of the reference text (the text in cell A1 of Sheet2) in the array A1:A10000 of Sheet1. Because that location is numbered starting from 1, we have to subtract one from it to get the correct number of RowsToMove down from B1. That is, if the Match is in the first row, the OFFSET() function needs to move down zero rows. We want to stay in column B, so ColumnsToMove is zero. Finally, we want the the returned range to be one row high and 3 columns wide, spanning columns B through D of a single row.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: [SOLVED] Find and copy an entire row.
Works just as i want! Huge thanks to you Mr./Mrs. FJCC!
Re: [Solved] Find and copy an entire row.
FJCC wrote:The formula isWhich has the formCode: Select all
=OFFSET(Sheet1.$B$1;MATCH(A1;Sheet1.$A$1:$A$10000;0)-1;0;1;3)
so in my formula they areCode: Select all
=OFFSET(Reference;RowsToMove;ColumnsToMove;Height;Width)
Reference = Sheet1.$B$1
RowsToMove = MATCH(A1;Sheet1.$A$1:$A$10000;0)-1
ColumnsToMove = 0
Height = 1
Width = 3
The formula starts in cell B1 of Sheet1. The MATCH() formula returns the location of the reference text (the text in cell A1 of Sheet2) in the array A1:A10000 of Sheet1. Because that location is numbered starting from 1, we have to subtract one from it to get the correct number of RowsToMove down from B1. That is, if the Match is in the first row, the OFFSET() function needs to move down zero rows. We want to stay in column B, so ColumnsToMove is zero. Finally, we want the the returned range to be one row high and 3 columns wide, spanning columns B through D of a single row.
Huge thanks sir. You've taught me a wonderful function today. My life is much easier thanks to you. FYI, I've specially created this account to thank you. Please accept my heartfelt gratitude.
OpenOffice 4.1.5 on Windows 10 Pro