[Solved] Find and copy an entire row.

Discuss the spreadsheet application

[Solved] Find and copy an entire row.

Postby r0bs » Mon Jan 28, 2013 8:05 pm

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

Re: Find and copy an entire row.

Postby FJCC » Mon Jan 28, 2013 9:59 pm

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 143 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7307
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Find and copy an entire row.

Postby Vexed » Tue Jan 29, 2013 4:27 am

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.

Postby FJCC » Tue Jan 29, 2013 5:35 am

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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7307
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby r0bs » Tue Jan 29, 2013 12:05 pm

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.

Postby hksbindra » Tue Mar 12, 2019 2:20 pm

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


Return to Calc

Who is online

Users browsing this forum: jon oneill and 17 guests