Attempting to use a an IF formula to dynamically copy cell contents given the presence of a specific value

ms office

I need to duplicate cells from one worksheet to another if the associated record [row] contains a given word.

Say row 2 has "foo" in cell A, I would like to duplicate all the cells in that row to another workbook. If A3 has "floop" in it no cells should be duplicated—it would be skipped essentially (without a blank row).

The data is as so:

    A   |   B   |  C

2  foo  | blah  | blah

3 floop | blah  | blah

4  foo  | blah  | blah

The resulting table would be as so:

    A   |   B   |  C

2  foo  | blah  | blah

3  foo  | blah  | blah

—

This is as far as I've gotten:

I've created a helper table beginning at A52 to sort the data for which "foo" is present in column A utilizing the formula =IF(ISNUMBER(SEARCH("foo",a2)),a2,"")
[Which I have a feeling is redundant, but is the only bit that seems to be working.]

In the final table I'm using =IF(ISNUMBER(ISBLANK(A52)),A2,[!!!!!])
[Wherein [!!!!!] is the display code that I don't know.]
It resolves as "FALSE"

I know this is likely an elementary question, but I haven't had any luck finding an answer elsewhere.

Thanks in advance!
– p

PS As I'm writing this I realize that I'm going to need to process the entire row as opposed to the individual cells, so I'm likely going to need another approach. Oh well! I've gotten this far!

Best Answer

Using an IF formula to achieve this is going to be problematic for a number of reasons. But assuming you got it to work, you're always going to have the issue that a formula has to be placed within a cell and therefore your workbook is not scalable (i.e. what happens over time if/when you add/delete rows)? Sure, you can work around that too (e.g. by formatting your data as a proper table/list), but from my experience you'd be better to use a macro to do this.

Step 1: Add the Developer tab to Excel

First of all you need to ensure that you have the Developer tab visible in Excel's ribbon. If it's already there (typically it's the last one after the View tab, then skip to Step 2: Creating your macro. Otherwise, follow the steps below:

  1. Launch MS Excel
  2. Go to Excel > Preferences (or just press the command, keys)
  3. Select the Ribbon & Toolbar icon
  4. Ensure the Ribbon tab is selected at top
  5. On the right-hand side you'll see a list of tabs - select the checkbox next to the Developer tab (it's probably listed at the very bottom)
  6. Click on the Save button
  7. Exit Preferences
  8. Now you'll see the Developer tab (see image below):

enter image description here

Step 2: Creating your macro

Now we need to create your macro. Follow these steps:

  1. In Excel, make sure you have your workbook open
  2. Select the Developer tab
  3. Click on the Visual Basic option (at far left of the Ribbon)
  4. This will open the Microsoft Visual Basic window

  5. The top left of the window should look something like:

  6. Now right-click on ThisWorkbook and select Insert > Module (see below)

  1. In a few moments you should see a white window open on the right-hand side of the Visual Basic Editor

  2. Now copy the code below and paste it into the white space:

    Sub CopyRowsBasedOnCellValue()
'This macro copies rows from one worksheet to another within the same workbook if that row contains a specific value in Column A
    Dim xRg As Range
    Dim xCell As Range
    Dim X As Long
    Dim Y As Long
    Dim Z As Long
    X = Worksheets("Sheet1").UsedRange.Rows.Count
    Y = Worksheets("Sheet2").UsedRange.Rows.Count
    If Y = 1 Then
        If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then Y = 0
    End If
    Set xRg = Worksheets("Sheet1").Range("A1:A" & X)
    On Error Resume Next
    Application.ScreenUpdating = False
    For Z = 1 To xRg.Count
        If CStr(xRg(Z).Value) = "foo" Then
            xRg(Z).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & Y + 1)
            Y = Y + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

  3. Now you should see something like the following:

enter image description here

  1. Now you will need to customise this code slightly as it's currently written specifically for the example in your question. You need to customise the following aspects:

    • Replace the two instances of Sheet1 with the name of your first worksheet (i.e. the one you're copying from)
    • Replace the three instances of Sheet2 with the name of your second worksheet (i.e. the one you're copying to)
    • The instance of A1:A indicates the column being searched for the value you're trying to match (i.e. the "foo" value in your example. If it's not Column A in your worksheet, then change the A to whatever column it needs to be. For example, if your worksheet contains the "foo" value in Column D, then this needs to be D1:D
    • Finally, you will see a single instance of "foo" in the code. Replace the foo with whatever value you're trying to match.

  2. Finally, you will need to save your workbook as a macro-enabled one. To do this, click on the Excel icon at top left to return to Excel and then choose the Save As option from the File menu. For the file format you must select the Excel Macro-Enabled Workbook (.xlsm) option from the drop-down list.

Step 3: Using the macro

Now you're set to go. To use the macro, follow these steps:

  1. Launch Excel and open the workbook
  2. Select the Developer tab
  3. Click on the Macros icon (it should be the second one)
  4. This will bring up the Macro window:
  5. Select the CopyRowsBAsedOnCellValue macro (see below)

enter image description here

  1. Click on the Run button
  2. Your macro will run and all rows matching the criteria will be copied to your destination sheet. Below is an image showing the before and after shots, based on your example:

enter image description here

Note: The above code was adapted from code originally sourced from https://www.extendoffice.com/. I have no affiliation with that website whatsoever.

Related Question