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

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.