Copy cells from one sheet and paste in another sheet

[shibu_kt]

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

SHEET 2

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.

[jimk]

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

[jimk]

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.

[shibu_kt]

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.

[jimk]

“specific cells” … “specific columns” – which specific cells and which specific columns? Please give a complete example.

[shibu_kt]

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

[jimk]

The question is much better now.

[jimk]

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 Calc macro copy and paste woes - #2 by jheath.

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: Spreadsheet Functions - LibreOffice Help

[shibu_kt]

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

[jimk]

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

[erAck]

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.

[jimk]

@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.

[erAck]

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

[shibu_kt]

Thanks to everyone who contributed. It’s solved.

[PaulaO]

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.

[jimk]

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