Excel find smaller values than x > identify date/time and paste it

excel find lowest value with criteria
excel find first cell above with value
excel match greater than not working
excel match less than
excel find closest value greater than
excel find value greater than in column
index match less than or equal to
excel lookup greater than or equal to

I am having data (image below) and task is to find values <3, identify what date and hour is and paste dates, hours in another spreadsheet of that cells. Does any have ideas?

Option Explicit
> 
> Sub CopyPaste()
> 
> Dim LastRow As Long, LastCol As Long, Row As Long, Column As Long, x
> As Long Dim wb As Workbook Dim ws As Worksheet, ws2 As Worksheet Dim
> SearchRange As Range, Cell As Range
> 
> Set wb = ThisWorkbook Set ws = wb.Sheets("DataHorizontal") 'the sheet
> in which your data is Set ws2 = wb.Sheets("Overview") 'the sheet where
> you want your result
> 
> LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row LastCol = ws.Cells(1,
> Columns.Count).End(xlToLeft).Column
> 
> Set SearchRange = ws.Range(ws.Cells(2, 2), ws.Cells(LastRow, LastCol))
> 
> x = 27
> 
> For Each Cell In SearchRange
>     Row = Cell.Row
>     Column = Cell.Column
>     If Cell.Value < -3 Or Cell.Value > 3 Then
>         'Output is placed in 2 columns, A for date, B for time
>         ws2.Cells(x, 5).Value = ws.Cells(Row, 1).Value 'Copy-paste Date
>         ws2.Cells(x, 6).Value = ws.Cells(1, Column).Value 'Copy-Paste Time
>         x = x + 1
>     End If Next Cell
> 
> End Sub

Output with <-3 or >3 Dataset more left columns


You need a macro to do so.

Code example

    Option Explicit

Sub CopyPaste()

Dim LastRow As Long, LastCol As Long, Row As Long, Column As Long, x As Long
Dim wb As Workbook
Dim ws As Worksheet, ws2 As Worksheet
Dim SearchRange As Range, Cell As Range

Set wb = ThisWorkbook
Set ws = wb.Sheets("input") 'the sheet in which your data is
Set ws2 = wb.Sheets("Output") 'the sheet where you want your result

LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

Set SearchRange = ws.Range(ws.Cells(2, 2), ws.Cells(LastRow, LastCol))

x = 2

For Each Cell In SearchRange
    Row = Cell.Row
    Column = Cell.Column
    If Cell.Value < 3 Then
        'Output is placed in 2 columns, A for time, B for date
        ws2.Cells(x, 1).Value = ws.Cells(1, Column).Value 'Copy-Paste Time
        ws2.Cells(x, 2).Value = ws.Cells(Row, 1).Value 'Copy-paste Date
        x = x + 1
    End If
Next Cell

End Sub

Find the smallest value in a list that is larger than a number, In Excel if you have a list of values and you cannot find an exact match, then you need to find either the Closest Larger or Closest Smaller value. For example, How to find the first / last value greater than X in Excel? Supposing, you have a long list of numbers, now, you want to extract the first or the last number which is greater than a specific value. In Excel, you needn’t find them one by one, because here are some formulas to solve it quickly and time-saving.


Open the excel, press ALT+F11 and on the left side window, right click on the Worksheet you use (The name of the Worksheet can be found there), select Add -> Modul, and copy the following code into.

When you adjusted the variables, just press the Play button, or press F5.

Here is your solution:

Sub Stackoverflow()

Dim wbk1 As Workbook
Dim wbk2 As Workbook

Rem Creating a new workbook to collect data
Set wbk1 = ThisWorkbook
Set wbk2 = Workbooks.Add(xlWBATWorksheet)

Rem dc = datecolumn, the number of column AA
dc = Range("AA" & 1).Column

Rem k is the first row of new workbook, and i is the first row where the data can be found.
k = 1
i = 2

Do
  j = Range("AB" & 1).Column
  Do
    If wbk1.Worksheets("Work1").Cells(i, j).Value < 3 Then
      wbk2.Worksheets("Work1").Cells(k, 1) = wbk1.Worksheets("Work1").Cells(i, dc)
      wbk2.Worksheets("Work1").Cells(k, 2) = wbk1.Worksheets("Work1").Cells(1, j)
      wbk2.Worksheets("Work1").Cells(k, 3) = wbk1.Worksheets("Work1").Cells(i, j)
      k = k + 1
    End If
    j = j + 1
  Loop Until wbk1.Worksheets("Work1").Cells(i, j).Value = ""
  i = i + 1
Loop Until wbk1.Worksheets("Work1").Cells(i, j).Value = ""

Rem the result will be:
Rem first column in the new workbook will be the date
Rem second is the time
Rem third is the data

End Sub

Since you haven't provided the row numbers on your screen, you have to set the 'i' value to the first row where the data can be found. So if the row number is 2 where the number data are, just leave it so.

Please also note, you have to rename Work1 to the name of your Worksheet!

Hope this will help!

Retrieving the Closest Larger and Smaller Values from a , Learn how to find first value that is greater or less than your value in a list. The generic formula for finding the first number from a list that is greater than a given number is. How to use the VLOOKUP Function in Excel have a formula that will pick the nearest number that is less than a number and get it from row 1a - 4b​. Excel find smaller values than x > identify date/time and paste it. Ask Question Open the excel, press ALT+F11 and on the left side window, right click on the


A faster way is using powerquery (Get & Transform Data).

Format your source data as a table, then use a query like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Time", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] < 3),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Time", type time}, {"Value", type number}})
in
    #"Changed Type"

Retrieving the first value in a list that is greater / smaller than a , To find the n lowest values in a set of data, you can use the SMALL function. This can MATCH then returns the location of the value to INDEX as a row number. COUNTIF counts the number of cells in the range that contain numeric values less than X and returns the result as a number. = COUNTIF (C4:C10, "<=80") = COUNTIF (rng, "<" & A1) = COUNTIF (rng, "<100")


Result will be like this, where you can adjust output table:

Idea is to loop through every column for a date, and print values. Then go to next date (row) and repeat the procedure. Output will printed out as in the one from Column BA to BC.

Sub CompareCopy()
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Worksheets("Sheet1")
Dim ws2 As Worksheet
Set ws2 = ActiveWorkbook.Worksheets("Sheet1") 'You can change the output datas sheet.
Dim lrow As Long
Dim i As Long
Dim j As Long
Dim lCol As Long
Dim k As Long
Dim Header1 As String
Dim Header2 As String
Dim Header3 As String
Dim ColumnOutput As String

k = 2 'first row where output data will be placed

lCol = ws1.Cells(1, Columns.Count).End(xlToLeft).Column 'find last column
lrow = ws1.Cells(Rows.Count, 27).End(xlUp).Row 'Find last row

ColumnOutput = lCol 'Set Column where output should start, default is the last column of your original table.

ws2.Cells(k - 1, ColumnOutput + 2) = "Date" 'Place where "Date" will be printed
ws2.Cells(k - 1, ColumnOutput + 3) = "Time" 'Place where "Time" will be printed
ws2.Cells(k - 1, ColumnOutput + 4) = "Value" 'Place where "Value" will be printed

For i = 2 To lrow 'Loop through each row
    For j = 28 To lCol 'Loop through each Column
        If -3 > ws1.Cells(i, j).Value Then 'If current table value is less than -3 then copy
            ws2.Cells(k, ColumnOutput + 2).Value = ws1.Cells(i, 27).Value 'Copy Date
            ws2.Cells(k, ColumnOutput + 2).NumberFormat = "dd-mmm-yy" 'Format time value to correct time
            ws2.Cells(k, ColumnOutput + 3).Value = ws1.Cells(1, j).Value 'Copy Time
            ws2.Cells(k, ColumnOutput + 3).NumberFormat = "hh:mm" 'Format time value to correct time
            ws2.Cells(k, ColumnOutput + 4).Value = ws1.Cells(i, j).Value 'Copy Value
            k = k + 1
        End If
    Next j
Next i
End Sub

Excel formula: Find lowest n values, To get the smaller, or lesser, of two values, you can use the MIN function. In the example shown, the formula in D5 is: =MIN(B5,C5) How this formula works The  Find the smallest value in a list that is larger than a number. Find min/max date rng. SMALL with dupes. How to use the MINIFS function. Function Reference Formulas Charts Conditional Formatting Excel Tables Pivot Tables VBA Knowledge Base Macros User Defined Functions Videos Advanced Excel Course. Most used functions


Excel formula: Smaller of two values, Let's say you want to find out who has the the smallest error rate in a Select a cell below or to the right of the numbers for which you want to find the smallest number. click Min (calculates the smallest) or Max (calculates the largest), and then Surface Go 2 · Surface Book 3 · Microsoft 365 · Surface Pro X · Windows 10  Find all values greater or equal than a certain value. However, using that solution in my situation does not give me the correct results. I have a list of 83 names with penalties being given to each name. On a separate tab, I'd like to display the output of all names that have any penalty (>0).


Excel: searching for a maximum lower than X, Assuming that you have a list of data in range B1:B6, in which contain numeric values. And you want to find the first number from the list that is  Use the COUNTIF function to count numbers greater than or less than a number. A11 and A12 has formulas where COUNTIF checks for the number of invoices less than 20000 and greater than or equal to 20,000 in the B2:B7 range. COUNTIF finds 4 values less than 20000 and 2 values greater than and equal to 20000.


Calculate the smallest or largest number in a range, When we have numeric values in two cells in Excel and need to compare them to find the smaller of two values, then this can be done using different formulas. Hence the final match gives index row number 7. And using that INDEX returns the value at index 7. Similarly, if you want to find the first number in a list that is less than the given value, just replace ‘<’ with ‘>’ in the formula.