Copy Rows from One Sheet to Another Based on Criteria in Excel

แชร์
ฝัง
  • เผยแพร่เมื่อ 1 ธ.ค. 2024

ความคิดเห็น • 53

  • @jeffbakker7241
    @jeffbakker7241 3 วันที่ผ่านมา +1

    Thank you Zehad and Md. Riajul!

    • @exceldemy2006
      @exceldemy2006  3 วันที่ผ่านมา

      Hello @jeffbakker7241,
      You are most welcome. Thanks for your appreciation and feedback. Keep exploring Excel with ExcelDemy!
      Regards
      ExcelDemy

  • @richardhay645
    @richardhay645 11 หลายเดือนก่อน +4

    FILTER function is the only method I use. Fast to build, fully dynamic and efficient calculation

    • @exceldemy2006
      @exceldemy2006  8 หลายเดือนก่อน

      Hello @richardhay645,
      Excellent choice, the FILTER function is one of the most useful and dynamic functions to get filtered data.
      Regards
      ExcelDemy

  • @ErinWells-o9n
    @ErinWells-o9n 19 วันที่ผ่านมา +1

    I need to use this however the other people on the shared fills cant see what I have filtered and when I email the workbook the people I email to cant see it either

    • @exceldemy2006
      @exceldemy2006  19 วันที่ผ่านมา

      Hello @ErinWells-o9n,
      It sounds like you're encountering an issue with filter visibility in a shared workbook. Filters in Excel are local to the user, meaning they only affect the person who applies them. To ensure that others can see the same filtered view, you can either:
      Share the filter settings: Have everyone apply the same filter by either creating a shared view (in Excel for Microsoft 365) or manually applying it.
      Use a Filter View (for Google Sheets or in shared Excel workbooks): If you're using Google Sheets, you can create filter views so that others see the same filtered data.
      If you want to send a filtered version by email, you may want to create a copy of the filtered data and share that file instead.
      Regards
      ExcelDemy

  • @ΗλιαςΜπαλ
    @ΗλιαςΜπαλ 3 หลายเดือนก่อน +1

    i work with libre office calc.we use a filter for quick search in column A. the filter has option for sort a z, z a .sometimes by accident you may press the buttons. how can i remove them from filter? i can send the file somehow for a better look to understand what i mean .thank you

    • @exceldemy2006
      @exceldemy2006  3 หลายเดือนก่อน

      Hello @user-fg6pf2ox1i,
      LibreOffice Calc doesn’t provide a direct way to disable these sort options in the filter dropdown. But you can avoid accidental sorting, by using a custom filter. Lock the specific column then use a custom filter setup that doesn't include sorting.
      If you want to share the file for a better understanding, you could share it via ExcelDemy Forum:exceldemy.com/forum/
      Regards
      ExcelDemy

  • @user-ck4nh4gz5x
    @user-ck4nh4gz5x 3 หลายเดือนก่อน +1

    When using the array formula, is there a way to give the formula two rules? If so, where do I add the second rule in the formula. I work for a non-profit that has purchase orders similar to the template you're using as an example, and I needed to sort my sheets by types of products we buy. I did that already thanks to the formula you provided. However, it would be most helpful with our budget limitations to sort the sheets by price as well. I'm just wondering if I could give the formula another rule to show me produce products under $150 for example. One rule for type of product, and another rule for price. Hopefully, that makes sense. Thanks for all the help!

    • @exceldemy2006
      @exceldemy2006  3 หลายเดือนก่อน +1

      Hello @user-ck4nh4gz5x,
      There is a way to add two rules or two criteria. You will need to add the 2nd rule using asterisk (*). By using an asterisk (*) you can add multiple rules.
      Here I added ( 'Array Formula'!$C$5:$C$101>150) condition to filter based on price.
      This formula will filter the rooted sheet based on price.
      =IFERROR(INDEX('Array Formula'!$A$5:$E$101,SMALL(IF(('Array Formula'!$F$5:$F$101=$C$17)*('Array Formula'!$C$5:$C$101>150),ROW('Array Formula'!$A$5:$A$101)-ROW('Array Formula'!$A$5)+1),ROWS('Array Formula'!$A$5:$A6)),COLUMN()),"")
      Download the Excel file:
      www.exceldemy.com/wp-content/uploads/2024/08/Copy_Rows_from_One_Sheet_to_Another_Based_on_Criteria-2.xlsm
      Regards
      ExcelDemy

  • @JP-vr9wg
    @JP-vr9wg 8 หลายเดือนก่อน

    Hi, great video! Is there a way to add to the VBA code that automatically updates the other sheets when the data is manipulated? I want to have people be able to make changes/additions to the date set and for the other sheet to change without them having to run the macro. Thanks!

    • @exceldemy2006
      @exceldemy2006  8 หลายเดือนก่อน

      Dear, thanks for the compliment. We can automatically update the other sheets using the VBA code when the data is manipulated. As you asked, we have demonstrated a scenario and implemented the mentioned requirements.
      Please check the following: www.exceldemy.com/wp-content/uploads/2024/04/automatically-update-the-other-sheets-using-the-VBA-code-when-the-data-is-manipulated-and-vice-versa.gif
      You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/04/JP-SOLVED.xlsm
      NOTE: In the solution workbook, each sheet contains an VBA event procedure. You will find these codes by right-clicking the sheet name tab followed by View Code.

    • @JP-vr9wg
      @JP-vr9wg 8 หลายเดือนก่อน

      Thank you for the code. I see there’s a gif but is there a video of you walking through the code explaining it?

    • @exceldemy2006
      @exceldemy2006  8 หลายเดือนก่อน

      @@JP-vr9wg Dear, you are most welcome. Unfortunately, we do not have any video explaining that topic. However, we will try to create a tutorial on it.
      If you face difficulties understanding these codes, do not hesitate to contact the ExcelDemy Forum.
      ExcelDemy Forum: exceldemy.com/forum/

  • @ΗλιαςΜπαλ
    @ΗλιαςΜπαλ 3 หลายเดือนก่อน +1

    we are using a filter which has the descending and ascending sorting options. Is there a way to remove these options from this filter? Sometimes it gets pressed by mistake and creates a mess as the numbers get mixed up.
    I want to remove sort a-z and sort z-a

    • @exceldemy2006
      @exceldemy2006  3 หลายเดือนก่อน

      Hello @user-fg6pf2ox1i,
      To remove the Sort A-Z and Sort Z-A options from an Excel filter, you can protect the worksheet or lock specific cells. Unfortunately, Excel doesn't allow the direct removal of sorting options from the filter dropdown itself, but restricting cell editing or protecting the sheet can help prevent accidental sorting.
      The FILTER function in Excel does not perform any sorting. It simply filters data based on the criteria you provide and returns the relevant rows.
      You can use the Method 5:
      =FILTER(FILTER!B4:F14,FILTER!F4:F14="Rooted")
      Regards
      ExcelDemy

  • @MsChapman
    @MsChapman 11 หลายเดือนก่อน

    This is wonderful!!!

    • @exceldemy2006
      @exceldemy2006  8 หลายเดือนก่อน

      Dear, thank you very much.

  • @m5man2
    @m5man2 3 หลายเดือนก่อน

    I am having trouble making the Array formula work. What is the point of including column A in the matrices in the Array Formula. Also Excel does not allow me to place the parentheses in the same locations as i see in the formula.

    • @exceldemy2006
      @exceldemy2006  3 หลายเดือนก่อน

      Hello @m5man2,
      In the Array Formula, column A is included to align the row references for the INDEX function, which extracts data based on the criteria in column F. The array formula checks if column F matches the criteria (e.g., $C$17) and retrieves the corresponding row.
      The error with parentheses might be due to Excel auto-adjusting the formula. Try entering it as an array formula (using Ctrl+Shift+Enter for older versions) to see if it helps.
      Regards
      ExcelDemy

  • @202voyer
    @202voyer 5 หลายเดือนก่อน +1

    Do any of these methods work if the criteria is in the date format?

    • @exceldemy2006
      @exceldemy2006  5 หลายเดือนก่อน

      Dear, Thanks for your question! The answer is YES. All the ideas will work for date-type criteria. You just need to adjust the formulas or features based on your dataset.
      Assume you want to retrieve rows in between dates. In this case, you can use Excel FILTER or IF functions. The FILTER function will retrieve fresh data; however, the IF function may provide some empty rows. So, using the FILTER function is recommended. Please check the following: www.exceldemy.com/wp-content/uploads/2024/06/Dynamic-Data-Extraction-Based-on-Date.gif
      You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/06/Dynamic-Data-Extraction-Based-on-Date.xlsx

  • @parjapatramesh4
    @parjapatramesh4 3 หลายเดือนก่อน +1

    How to copy a row into another sheet where two cells in that row are in red (conditional format setting) ?

    • @exceldemy2006
      @exceldemy2006  3 หลายเดือนก่อน

      Hello @parjapatramesh4,
      To copy a row into another sheet based on the color of conditional formatting of two cells, you can use the following VBA code:
      Do the following before using the code:
      1. First, replace "Source" and "Target" with your actual sheet names.
      2. Then, update the columns in cell1 and cell2 based on which cells should be checked.
      This code checks if both cells are red (RGB(255, 0, 0)) based on the conditional formatting display.
      Sub CopyRowsWithRedCells()
      Dim wsSource As Worksheet, wsTarget As Worksheet
      Dim lastRow As Long, targetRow As Long
      Dim i As Long
      Dim cell1 As Range, cell2 As Range
      ' Set your worksheets
      Set wsSource = ThisWorkbook.Sheets("Source") ' Change the source sheet name
      Set wsTarget = ThisWorkbook.Sheets("Target") ' Change the target sheet name
      lastRow = wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row
      targetRow = 2 ' Start copying at row 2 in the target sheet
      ' Loop through the rows in the source sheet
      For i = 2 To lastRow
      ' Set the two specific cells to check (e.g., column B and C)
      Set cell1 = wsSource.Cells(i, 2) ' Column B
      Set cell2 = wsSource.Cells(i, 3) ' Column C
      ' Check if both cells are red (based on conditional formatting color)
      If cell1.DisplayFormat.Interior.Color = RGB(255, 0, 0) And _
      cell2.DisplayFormat.Interior.Color = RGB(255, 0, 0) Then
      ' Copy the entire row to the target sheet
      wsSource.Rows(i).Copy Destination:=wsTarget.Rows(targetRow)
      targetRow = targetRow + 1 ' Move to the next row in the target sheet
      End If
      Next i
      End Sub
      Regards
      ExcelDemy

    • @parjapatramesh4
      @parjapatramesh4 3 หลายเดือนก่อน

      @@exceldemy2006 Thanks. This is great help.

    • @exceldemy2006
      @exceldemy2006  3 หลายเดือนก่อน

      @parjapatramesh4 You are most welcome. Glad to hear that it helped you. Keep learning Excel with ExcelDemy!

    • @parjapatramesh4
      @parjapatramesh4 3 หลายเดือนก่อน +1

      Is there a way this can be done in google sheets ?

    • @exceldemy2006
      @exceldemy2006  3 หลายเดือนก่อน +1

      Hello @parjapatramesh4,
      To achieve the same functionality in Google Sheets, you'll need to use Google Apps Script. Here's a script that mimics the VBA code provided for Excel.
      Open Google Sheets and click on Extensions -> Apps Script.
      function copyRowsWithRedCells() {
      // Set your sheet names here
      var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Source'); // Change 'Source' to your actual sheet name
      var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Target'); // Change 'Target' to your actual sheet name
      var lastRow = sourceSheet.getLastRow();
      var targetRow = 2; // Start copying at row 2 in the target sheet
      for (var i = 2; i

  • @ΗλιαςΜπαλ
    @ΗλιαςΜπαλ 4 หลายเดือนก่อน

    hello.how can i copy a data from column a for example when the data to column d is complete.the column d is empty and i want the data from column a to auto copied in second sheet when i complete the column d. when the d is full transfer a to second sheet .its for parking job and payments.i want the paid cars licence plates to be copied to a second sheet by themselves seperate from the rest of the cars when the payment is made. column a licences plates column d exit time .when i add exit time licence plate copied to 2nd sheet. thank you

    • @exceldemy2006
      @exceldemy2006  4 หลายเดือนก่อน

      Hello @user-fg6pf2ox1i,
      To automatically copy column A from the first sheet to 2nd sheet when column D is complete.
      Use the following VBA code:
      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim wsSource As Worksheet
      Dim wsDest As Worksheet
      Dim LastRow As Long
      Dim LicensePlate As String
      Set wsSource = ThisWorkbook.Sheets("Parking") ' Change "Sheet1" to your source sheet name
      Set wsDest = ThisWorkbook.Sheets("Destination") ' Change "Sheet2" to your destination sheet name
      If Not Intersect(Target, wsSource.Range("D:D")) Is Nothing Then
      If Target.Value "" Then
      LastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1
      LicensePlate = wsSource.Cells(Target.Row, "A").Value
      wsDest.Cells(LastRow, "A").Value = LicensePlate
      End If
      End If
      End Sub
      Every time you enter a time in the Exit column it will automatically copy the value of the License plate from Column A.
      Download the Excel file:
      www.exceldemy.com/wp-content/uploads/2024/07/Automatically-Copy-Rows.xlsm
      Regards
      ExcelDemy

  • @JenniferSharp9488
    @JenniferSharp9488 4 หลายเดือนก่อน

    is it possible to have rows copied based on criteria, and then deleted from the source worksheet?

    • @exceldemy2006
      @exceldemy2006  4 หลายเดือนก่อน

      Hello @JenniferSharp9488,
      It is possible to copy data based on conditions and then you can delete it from the source sheet.
      Use the following updated VBA code where I added the delete properties to delete the values from the source sheet after copying.
      Sub Copy_Delete_Based_On_Criteria()
      ' Taking the Inputs
      Dim Sheet1 As String
      Dim Sheet2 As String
      Dim Range1 As String
      Dim Range2 As String
      Dim CriteriaColumn As Integer
      Sheet1 = "VBA_Source"
      Sheet2 = "VBA_Destination"
      Range1 = "B5:E12"
      Range2 = "B5"
      CriteriaColumn = 4
      ' Forming the Necessary Ranges
      Dim Rng1 As Range
      Dim Rng2 As Range
      Set Rng1 = Sheets(Sheet1).Range(Range1)
      Set Rng2 = Sheets(Sheet2).Range(Range2)
      ' Copying the Headers (Optional)
      Rng1.Rows(0).Copy
      Rng2.Cells(0, 1).PasteSpecial Paste:=xlPasteAll
      ' Copying the Dataset with Criteria
      Dim Count As Integer
      Count = 0
      For i = 1 To Rng1.Rows.Count ' Start at 1 to skip the header row
      If Rng1.Cells(i, CriteriaColumn).Value > 300 Then
      Count = Count + 1
      Rng1.Rows(i).Copy
      Rng2.Cells(Count, 1).PasteSpecial Paste:=xlPasteAll
      ' Optionally delete the row from the source sheet
      Rng1.Rows(i).Delete
      ' Adjust the loop counter and range due to row deletion
      i = i - 1
      Set Rng1 = Sheets(Sheet1).Range(Range1)
      End If
      Next i
      Application.CutCopyMode = False
      End Sub
      Download the Excel File:
      www.exceldemy.com/wp-content/uploads/2024/07/Copy_Rows_from_One_Sheet_to_Another_Based_on_Criteria-1.xlsm
      Regards
      ExcelDemy

    • @JenniferSharp9488
      @JenniferSharp9488 4 หลายเดือนก่อน +1

      @@exceldemy2006 Thank you so much!

    • @exceldemy2006
      @exceldemy2006  4 หลายเดือนก่อน

      ​Dear @@JenniferSharp9488 ,
      You are most welcome. keep learning Excel with ExcelDemy.
      Regards
      ExcelDemy

  • @TheKuttingify
    @TheKuttingify 8 หลายเดือนก่อน

    Is there a VBA for the Array formula?

    • @exceldemy2006
      @exceldemy2006  8 หลายเดือนก่อน

      Dear, thanks for the comment. Using the ArrayFormula, multiple rows are copied based on shop criteria. However, if you want an Excel VBA User-defined function instead of using this, you can use the following UDF:
      Function UserDefinedArrayFormula(rng As Range, ByVal compareValue As Variant) As Variant

      Dim arr() As Variant
      Dim filteredArr() As Variant
      Dim i As Long, j As Long
      Dim rowCount As Long
      Dim filteredRowCount As Long

      rowCount = rng.Rows.Count

      ReDim arr(1 To rowCount, 1 To rng.Columns.Count)

      For i = 1 To rowCount
      For j = 1 To rng.Columns.Count
      arr(i, j) = rng.Cells(i, j).value
      Next j
      Next i

      For i = 1 To rowCount
      If arr(i, rng.Columns.Count) = compareValue Then
      filteredRowCount = filteredRowCount + 1
      End If
      Next i

      ReDim filteredArr(1 To filteredRowCount, 1 To rng.Columns.Count)

      filteredRowCount = 1
      For i = 1 To rowCount
      If arr(i, rng.Columns.Count) = compareValue Then
      For j = 1 To rng.Columns.Count
      filteredArr(filteredRowCount, j) = arr(i, j)
      Next j
      filteredRowCount = filteredRowCount + 1
      End If
      Next i

      UserDefinedArrayFormula = filteredArr
      End Function
      Please check the following: www.exceldemy.com/wp-content/uploads/2024/03/Output-of-using-Excel-VBA-User-Defined-Function.gif
      You can download the solution workbook: www.exceldemy.com/wp-content/uploads/2024/03/Via-Gusto-SOLVED.xlsm
      If you have any queries about the solution, contact us at ExcelDemy Forum: exceldemy.com/forum/

  • @laurenshaw4107
    @laurenshaw4107 8 หลายเดือนก่อน

    thank you

  • @LaylaDSmith
    @LaylaDSmith 9 หลายเดือนก่อน +2

    Hi! Where is the VBA Code?

    • @exceldemy2006
      @exceldemy2006  8 หลายเดือนก่อน +1

      Dear, thanks for your patience. The VBA code mentioned in this video differs slightly from the existing article code.
      Here is the Excel VBA code used in this VIDEO:
      Sub Copy_With_Criteria()

      'Taking the Inputs

      Dim Sheet1 As String
      Dim Sheet2 As String

      Dim Range1 As String
      Dim Range2 As String

      Dim CriteriaColumn As Integer

      Sheet1 = "VBA Source"
      Range1 = "B5:E9"
      Sheet2 = "VBA Destination"
      Range2 = "B5"
      CriteriaColumn = 4

      'Forming the Necessary Ranges
      Dim Rng1 As Range
      Dim Rng2 As Range

      Set Rng1 = Sheets(Sheet1).Range(Range1)
      Set Rng2 = Sheets(Sheet2).Range(Range2)

      'Copying the Headers
      Rng1.Rows(0).Copy
      Rng2.Cells(0, 1).PasteSpecial Paste:=xlPasteAll

      'Copying the Dataset with Criteria
      Count = 0

      For i = 1 To Rng1.Rows.Count
      If Rng1.Cells(i, CriteriaColumn) > 150 Then
      Count = Count + 1
      Rng1.Rows(i).Copy
      Rng2.Cells(Count, 1).PasteSpecial Paste:=xlPasteAll
      End If
      Next i

      Application.CutCopyMode = False
      End Sub

    • @exceldemy2006
      @exceldemy2006  8 หลายเดือนก่อน +1

      Hello @LaylaDSmith,
      Sorry for the inconvenience. The VBA code is in our main article. we updated the article for your convenience. Now you will get the code from here:
      www.exceldemy.com/excel-copy-rows-from-one-sheet-to-another-based-on-criteria/#6_Using_VBA_for_Copying_Rows_from_One_Sheet_to_Another
      Or you can download our Excel file to run the code in your Excel environment.
      www.exceldemy.com/excel-copy-rows-from-one-sheet-to-another-based-on-criteria/#download
      Please stay connected with us.
      Regards
      ExcelDemy

  • @ChristianRamirez-qq5zd
    @ChristianRamirez-qq5zd 8 หลายเดือนก่อน

    where do you get the array formula?

    • @exceldemy2006
      @exceldemy2006  8 หลายเดือนก่อน

      Dear, the formula you are asking is as follows:
      =IFERROR(INDEX('Array Formula'!$A$5:$E$101, SMALL(IF('Array Formula'!$F$5:$F$101=$C$17, ROW('Array Formula'!$A$5:$B$101) - ROW('Array Formula'!$B$5) + 1), ROWS('Array Formula'!$A$5:$B5)), COLUMN()), "")

  • @ΗλιαςΜπαλ
    @ΗλιαςΜπαλ 4 หลายเดือนก่อน

    OK guys one more if you can help me.
    I have entry time and exit time.And the result it's the duration. Exit time -entry time=duration
    There is any formula if the duration is 1:13 or 1:14 to round up to 1:15? I use shift,ctrl and ; to add the exit time

    • @exceldemy2006
      @exceldemy2006  4 หลายเดือนก่อน

      Hello @user-fg6pf2ox1i,
      You can use the CEILING function to get the nearest time for 1:13 or 1:14 as 1:15.
      Use the following formula:
      =CEILING(C2*1440, 15) / 1440
      Please format the Result as Time. Make sure that the cell is formatted as time. You can do this by right-clicking the cell, selecting Format Cells, and choosing Time.
      To round time you can explore this article:
      www.exceldemy.com/excel-round-time-to-nearest-5-minutes/
      Download the Excel file :
      www.exceldemy.com/wp-content/uploads/2024/08/Round-Time-in-Excel.xlsx
      Regards
      ExcelDemy

  • @kycosand4307
    @kycosand4307 9 หลายเดือนก่อน +1

    I love you

    • @exceldemy2006
      @exceldemy2006  8 หลายเดือนก่อน

      Hello @kycosand4307 ,
      We appreciate your love. Hopefully you love our videos too. Stay connected with us and show your love.
      Regards
      ExcelDemy

  • @m5man2
    @m5man2 3 หลายเดือนก่อน

    This is how my formula looks, but it doesn't seem to work. =IFERROR(INDEX(Java!$A$2:$Q$715,SMALL(IF(Java!$O$2:$O$715=$C$1,ROW(Java!$A$2:$B$715)-ROW(Java!$B$2)+1),ROWS(Java!$A$2:$B2)),COLUMN()),"") Anyone see a problem with it?

    • @exceldemy2006
      @exceldemy2006  3 หลายเดือนก่อน

      Hello @m5man2,
      The issue with the formula might be referencing multiple columns (Java!$A$2:$B$715) in the ROW function while trying to retrieve a single column. The ROW function should only reference one column, not a range spanning multiple columns.
      Use the following formula:
      =IFERROR(INDEX(Java!$A$2:$Q$715,SMALL(IF(Java!$O$2:$O$715=$C$1,ROW(Java!$A$2:$A$715)-ROW(Java!$A$2)+1),ROWS(Java!$A$2:A2)),COLUMN()),"")
      We adjusted the ROW reference to a single column (Java!$A$2:$A$715).
      Regards
      ExcelDemy

    • @m5man2
      @m5man2 3 หลายเดือนก่อน

      @@exceldemy2006 I copied it and it didn't work. I see that in your original formula your ROW reference included 2 columns. Why did that work for your formula and not for mine?

    • @exceldemy2006
      @exceldemy2006  3 หลายเดือนก่อน

      Hello @m5man2,
      Our formula worked because of the specific alignment and layout of our data. In our formula, ROW('Array Formula'!$A$5:$B$101)-ROW('Array Formula'!$B$5)+1, both columns A and B are aligned, and the ROW() function is used to calculate the row numbers consistently across these two columns
      However, using a single-column reference in the ROW() function is generally more reliable, especially when working with larger datasets or more complex ranges, as in the user's case.
      The issue could be due to mismatches in data structure or criteria. In your formula, the use of multiple columns in the ROW() function can cause inconsistencies. I suggest ensuring the criteria in $C$1 exactly matches column O, and try this revised formula:
      =IFERROR(INDEX(Java!$A$2:$Q$715, SMALL(IF(Java!$O$2:$O$715 = $C$1, ROW(Java!$A$2:$A$715) - MIN(ROW(Java!$A$2:$A$715)) + 1), ROWS($A$2:A2)), COLUMN(Java!$A$2)), "")
      As I don't know your dataset, these are my suggestions. You may need to adjust them based on your data structure.
      Regards
      ExcelDemy