How to copy rows if column contains specific text/value in Excel?

Question

I have two files,say F1 and F2.

If data in column1 from F1 matches Column1 from F2, paste column2 from F1 to F2.

Eg.

 file F1 has
    column1  column2
    X        value1
    Y        value2
    Z        value3



file F2 has
    column1    column2
    Y          key1
    Z          key2
    X          key3

I am trying to insert a new column in F2 to look like:

column1   column2  column3
X         value1   key3
Y         value2   key1
Z         value3   key2

This is achievable within same file. How can accomplish this across multiple files in excel/libreoffice?

There are 100s of rows in column1. BTW, Would you mind writing it as an answer? — ramailo sathi
– ramailo sathi, Commented Jan 25, 2016 at 16:36
I agree with @Scott Craner that VLOOKUP is your best option. To scale this to 100s of rows is not an issue. — nbayly
– nbayly, Commented Jan 25, 2016 at 17:02

Jim K · Accepted Answer · 2016-01-25 19:11:57Z

2

As stated in the comments, VLOOKUP can use other files. Here is what it looks like in LibreOffice:

The formula in the image is:

=VLOOKUP(A1,'file:///C:/Users/JimStandard/Desktop/F1.ods'#$Sheet1.A$1:B$3,2)

The $ signs make it easier to fill the formula down, by clicking and dragging the square in the lower-right corner of cell C1.

answered Jan 25, 2016 at 19:11

Jim K

14.1k2 gold badges26 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ramailo sathi Over a year ago

Thank you for the answer. Is there any workaround if there are more than a value for key1? For eg. the expected output is : Y key1 value2 value4

Jim K Over a year ago

Do you mean if there is a row in F1 like Y value4 as well as Y value2?

Jim K Over a year ago

VLOOKUP only gets one value per key. One good way to handle multiple values is to use LibreOffice Base. It can do a query on a Calc spreadsheet. Or you could write a macro to handle this, as suggested in the other answer.

David Lozano · Accepted Answer · 2016-01-26 11:47:07Z

Assuming that F1 and F2 are excel sheets in a workbook , you can create a macro with this code in VBA and adapt it to your needs

Public Sub CopyColumns()

    Dim init As Range
    Dim nameColumn As String
    Dim i As Integer
    Dim n As Integer
    Dim array1(3) As String
    Dim array2(2, 3) As String     'We declare two dimensional array

    Sheets("NameOfF1Sheet").Activate

    i = 0

    Range("A1").Select    'Suppose the start cell of the row that contains the text "column1" in F1 file

    nameColumn = "column1"   'Search column name to copy

    Do
        If ActiveCell.Value = nameColumn Then
            ActiveCell.offset(1, 0).Select
            Do
                array2(1, i) = ActiveCell.Value                 'Copy data in array2 from column1
                array2(2, i) = ActiveCell.offset(0, 1).Value    'Copy data in array2 from column2
                i = i + 1
            Loop Until IsEmpty(ActiveCell) = True
        Else
            ActiveCell.offset(0, 1).Select
        End If
    While IsEmpty(ActiveCell) = True    'Copy while there is data in column1

    Sheets("NameOfF2Sheet").Activate    'Sheet change

    i = 0
    n = 0

    Range("A1").Select     'Suppose the start cell of the row that contains the text "column1" in F2 file

    nameColumn = "column1"   'Search column name to paste

    Do
        If ActiveCell.Value = nameColumn Then

            init = ActiveCell.Address

            ActiveCell.offset(0, 1).Select      'Copy all column2
            Do
                array1(n) = ActiveCell.Value
                n = n + 1
                ActiveCell.offset(1, 0).Select
            While IsEmpty(ActiveCell) = True

            Range(init).Select

            ActiveCell.offset(0, 2).Value = "column3"   'Rename old "column2" as "column3"
            ActiveCell.offset(1, 2).Select

            n = 0

            Do                                  'Paste all rows of "column2" in "column3"
                ActiveCell.Value = array1(n)
                n = n + 1
                ActiveCell.offset(1, 0).Select
            Loop Until n < 3

            Range(init).Select

            ActiveCell.offset(1, 1).Select
            Do
                If ActiveCell.Value = array2(1, i) Then
                    ActiveCell.offset(0, 2).Value = array2(2, i)    'Paste data in column2 from array2
                End If
                i = i + 1
            Loop Until i < 3
        Else
            ActiveCell.offset(0, 1).Select
        End If
    While IsEmpty(ActiveCell) = True

End Sub

I hope you serve , I'm new and my first answer!

Thanks for you first answer! Just a tip for future work with Calc, this code would be cleaner and would execute faster using the .getDataArray and .setDataArray API functions instead of pulling and setting values one at a time.

Collectives™ on Stack Overflow

How to copy rows if column contains specific text/value in Excel?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related