## [Solved] Find and copy an entire row.

### [Solved] Find and copy an entire row.

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
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!
Last edited by r0bs on Tue Jan 29, 2013 12:02 pm, edited 1 time in total.
r0bs

Posts: 2
Joined: Mon Jan 28, 2013 6:44 pm

### 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
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7304
Joined: Sat Nov 08, 2008 8:08 pm

### 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
OOo 2.4.X on Ms Windows XP
Vexed

Posts: 179
Joined: Sun Feb 08, 2009 1:24 am
Location: Virginia, USA

### Re: Find and copy an entire row.

The formula is
Code: Select all   Expand viewCollapse view
`=OFFSET(Sheet1.\$B\$1;MATCH(A1;Sheet1.\$A\$1:\$A\$10000;0)-1;0;1;3)`

Which has the form
Code: Select all   Expand viewCollapse view
`=OFFSET(Reference;RowsToMove;ColumnsToMove;Height;Width)`

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.
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7304
Joined: Sat Nov 08, 2008 8:08 pm

### Re: [SOLVED] Find and copy an entire row.

Works just as i want! Huge thanks to you Mr./Mrs. FJCC!
r0bs

Posts: 2
Joined: Mon Jan 28, 2013 6:44 pm

### Re: [Solved] Find and copy an entire row.

FJCC wrote:The formula is
Code: Select all   Expand viewCollapse view
`=OFFSET(Sheet1.\$B\$1;MATCH(A1;Sheet1.\$A\$1:\$A\$10000;0)-1;0;1;3)`

Which has the form
Code: Select all   Expand viewCollapse view
`=OFFSET(Reference;RowsToMove;ColumnsToMove;Height;Width)`

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.

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
hksbindra

Posts: 1
Joined: Tue Mar 12, 2019 2:16 pm