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
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
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
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
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!
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
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!
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 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/
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
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
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.
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
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
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
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
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
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
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
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/
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()
'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
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
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()), "")
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
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
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?
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
@@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?
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
Thank you Zehad and Md. Riajul!
Hello @jeffbakker7241,
You are most welcome. Thanks for your appreciation and feedback. Keep exploring Excel with ExcelDemy!
Regards
ExcelDemy
FILTER function is the only method I use. Fast to build, fully dynamic and efficient calculation
Hello @richardhay645,
Excellent choice, the FILTER function is one of the most useful and dynamic functions to get filtered data.
Regards
ExcelDemy
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
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
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
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
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!
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
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!
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.
Thank you for the code. I see there’s a gif but is there a video of you walking through the code explaining it?
@@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/
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
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
This is wonderful!!!
Dear, thank you very much.
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.
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
Do any of these methods work if the criteria is in the date format?
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
How to copy a row into another sheet where two cells in that row are in red (conditional format setting) ?
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
@@exceldemy2006 Thanks. This is great help.
@parjapatramesh4 You are most welcome. Glad to hear that it helped you. Keep learning Excel with ExcelDemy!
Is there a way this can be done in google sheets ?
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
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
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
is it possible to have rows copied based on criteria, and then deleted from the source worksheet?
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
@@exceldemy2006 Thank you so much!
Dear @@JenniferSharp9488 ,
You are most welcome. keep learning Excel with ExcelDemy.
Regards
ExcelDemy
Is there a VBA for the Array formula?
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/
thank you
Welcome!
Hi! Where is the VBA Code?
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
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
where do you get the array formula?
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()), "")
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
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
I love you
Hello @kycosand4307 ,
We appreciate your love. Hopefully you love our videos too. Stay connected with us and show your love.
Regards
ExcelDemy
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?
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
@@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?
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