[Solved] Find and copy an entire row.

Discuss the spreadsheet application
Post Reply
r0bs
Posts: 2
Joined: Mon Jan 28, 2013 6:44 pm

[Solved] Find and copy an entire row.

Post by r0bs »

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 :knock: 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.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Find and copy an entire row.

Post by FJCC »

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.
Vexed
Posts: 187
Joined: Sun Feb 08, 2009 1:24 am
Location: Virginia, USA

Re: Find and copy an entire row.

Post by Vexed »

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Find and copy an entire row.

Post by FJCC »

The formula is

Code: Select all

=OFFSET(Sheet1.$B$1;MATCH(A1;Sheet1.$A$1:$A$10000;0)-1;0;1;3)
Which has the form

Code: Select all

=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.
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.
r0bs
Posts: 2
Joined: Mon Jan 28, 2013 6:44 pm

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

Post by r0bs »

Works just as i want! Huge thanks to you Mr./Mrs. FJCC! :) :) :) :)
hksbindra
Posts: 1
Joined: Tue Mar 12, 2019 2:16 pm

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

Post by hksbindra »

FJCC wrote:The formula is

Code: Select all

=OFFSET(Sheet1.$B$1;MATCH(A1;Sheet1.$A$1:$A$10000;0)-1;0;1;3)
Which has the form

Code: Select all

=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
Post Reply