Ask Your Question
0

Copy cells from one sheet and paste in another sheet [closed]

asked 2018-03-05 12:17:55 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Would like to know how this is done in Libre calc.

I have data in SHEET 1. I need to search SHEET 1 for a specific text, and if it exist, copy adjacent cell and paste it into SHEET 2.

Need to do this search, copy, paste for multiple text in SHEET1.

SHEET 1

image description

SHEET 2

image description

For Eg:

I need Libre office to search the SHEET 1 for say 00 105 98 00 01 01 NP V. Since this is found in A1, I need to get corresponding data in adjacent cell (i.e. B1 in this case) and paste it into B11 of SHEET 2 always. Similarly, if 00 105 98 00 01 03 NP V is found any where in column A of SHEET 1 (in this case, in A3), data in adjacent cell (B3 in this case) needs to be pasted in D11 always.

I hope it's clear now what I actually want to do.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-03-19 13:14:47.810341

Comments

1

Please do not post as community wiki. See guidelines for asking.

Jim K gravatar imageJim K ( 2018-03-06 07:26:00 +0200 )edit

It would help to provide a specific example. Is the text to copy and the adjacent cell in a specific column? For example, if the text is always in column A and the adjacent cell is always in column B, then perhaps you want VLOOKUP instead. My answer assumes that the text could be anywhere in the sheet.

Jim K gravatar imageJim K ( 2018-03-06 08:02:10 +0200 )edit

Yes, the text to be looked up and the adjacent cell would be in specific columns. And I would want the copied cell to be pasted in specific cells in SHEET 2, always.

shibu_kt gravatar imageshibu_kt ( 2018-03-07 08:22:26 +0200 )edit

"specific cells" ... "specific columns" -- which specific cells and which specific columns? Please give a complete example.

Jim K gravatar imageJim K ( 2018-03-08 01:41:38 +0200 )edit

Edited the question with more details. Please let me know if you need anything more.

shibu_kt gravatar imageshibu_kt ( 2018-03-08 08:40:35 +0200 )edit

The question is much better now.

Jim K gravatar imageJim K ( 2018-03-09 14:32:03 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2018-03-06 07:57:54 +0200

Jim K gravatar image

updated 2018-03-09 14:30:17 +0200

Here is an example macro that searches for "abc" in Sheet1. Then it grabs the cell in the next column and copies the value to that same cell on Sheet2. It requires uFindString_2 from section 6.23 of Andrew Pitonyak's Macro Document.

Sub SearchCalcAndCopy
    oSheet = ThisComponent.getSheets().getByIndex(0)
    oCellFound = uFindString_2("abc", oSheet)
    src_row = oCellFound.CellAddress.Row
    src_col = oCellFound.CellAddress.Column + 1
    oCellRangeSource = oSheet.getCellRangeByPosition(_
        src_col, src_row, src_col, src_row)
    Dim oDestAddr As New com.sun.star.table.CellAddress
    oDestAddr.Sheet = 1
    oDestAddr.Row = src_row
    oDestAddr.Column = src_col
    oSheet.copyRange(oDestAddr, oCellRangeSource.RangeAddress)
End Sub

Much of the code was based on https://ask.libreoffice.org/en/questi....

EDIT:

Enter this in B11:

=VLOOKUP("00 105 98 00 01 01 NP V";$Sheet1.A:B;2)

And this in D11:

=VLOOKUP("00 105 98 00 01 03 NP V";$Sheet1.A:B;2)

Documentation: https://help.libreoffice.org/Calc/Spr...

edit flag offensive delete link more

Comments

I need the values to be pasted in specific cells in SHEET 2, not the same cell in SHEET 2.

shibu_kt gravatar imageshibu_kt ( 2018-03-07 08:24:12 +0200 )edit

See edited answer based on the edited question. A macro is not needed.

Jim K gravatar imageJim K ( 2018-03-09 14:30:51 +0200 )edit

For VLOOKUP without 4th argument the lookup range needs to be continuously sorted ascending, which it is not with the example given. For unsorted data use 0 as 4th argument, so

=VLOOKUP("00 105 98 00 01 01 NPV";$Sheet1.A:B;2;0)

Note that $Sheet1.A:B best should be the actual data range like $Sheet1.A1:B12345 to not accidentally include other data below the range and for less overhead when the range is processed internally.

erAck gravatar imageerAck ( 2018-03-09 15:14:01 +0200 )edit

@erAck: "...needs to be continuously sorted ascending" - it looks like column A is sorted already. Also in my test, it was not necessary for the data to be continuous. Tested with LO 5.4.3.2. I agree that using an actual data range is best, but I used an unspecified range since the OP did not give a range.

Jim K gravatar imageJim K ( 2018-03-11 05:45:56 +0200 )edit

The column's data is not sorted, it contains empty rows, which if sorted would be expected at the end of the data.

erAck gravatar imageerAck ( 2018-03-11 18:59:55 +0200 )edit

Thanks to everyone who contributed. It's solved. :)

shibu_kt gravatar imageshibu_kt ( 2018-03-17 09:48:13 +0200 )edit
0

answered 2018-03-06 05:51:37 +0200

PaulaO gravatar image

That sounds like you need a macro. I don't think that can be done automagically. You can set a cell to display what is in another cell on another sheet (=pagename.cell). But to search something then take what is in the cell next to it will require a macro. Or a database.

edit flag offensive delete link more

Comments

The question is tagged "macro" so it seems that the OP is already aware that a macro may be required.

Jim K gravatar imageJim K ( 2018-03-06 07:27:38 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2018-03-05 12:17:55 +0200

Seen: 924 times

Last updated: Mar 09 '18