Stack Overflow Asked by Jozef on December 30, 2021
I would like to shift rows in some columns to unify some values to compare 2 records (records were created aproximately in the same time (2-3 sec difference), but in the set "b" has the extra records, more than in set "a").
Value in a3 is certainly also somewhere in b3. Date in a1 should be the same or max 1 day higher than the day in b1. Time in a2 should be 2 or max 3 seconds higher than the time in b2.
At the moment, I am doing it manually in excel, only with one simple macro (to shift celected range).
Never mind how to use Visual Basic, I just think about approach like this:
Would it be sufficient to use only excel functions (vlookup?, lookup?, how to set counter, and how to insert range?)?
Thank you.
enter image description here
Thanks for tips. This code works, after adding 1 variable - the counter "c1" in column A (integers from 1 to 10000), and datetime values converted to number:
Sub shiftRange()
'
'
Dim poc0, poc, poc2, poc3, poc4 As Long
Dim c1Range As Range
Set c1Range = Range("A3:A100000")
For Each Cell In c1Range
'if value in b3 is not equal to a3:
If Cells(Cell.Row, 4).Value <> Cells(Cell.Row, 12).Value Then
'if a row is not shifted already:
If Not IsEmpty(Cells(Cell.Row, 4)) Then
'search in the column b3 in a range at the utmost 50 cells from the actual cell:
For poc = Cells(Cell.Row, 12).Row To Cell.Row + 50
'if a date in a1 is equal or max 1 day higher (in the case when a difference of seconds exceed to next day) then date in b1:
If Not IsEmpty(Cells(poc, 4)) Then
If Cells(poc, 2).Value <= Cells(Cell.Row, 10).Value + 1 Then
'if the time in a2 is equal or higher to time in b2:
If Cells(poc, 11).Value <= Cells(Cell.Row, 3).Value Then
poc2 = Cells(poc, 12).Row
poc3 = poc2 - Cell.Row
If poc3 > 0 Then
poc4 = (Cell.Row + poc3) - 1
Else: poc4 = (Cell.Row + poc3)
End If
'insert blank cells in the range:
Range("B" & Cell.Row, "D" & poc4).Insert Shift:=xlDown
End If
End If
End If
Next poc
End If
End If
Application.StatusBar = "Working on the row: " & Cells(Cell.Row, 1).Value
Next
End Sub
Answered by Jozef on December 30, 2021
You can indeed use basic Excel functions, in order to do this. You mention VLookup()
and Lookup()
, but I'm personally more in favour of the Match()
function.
In order to do all this, the easiest way to start is by recording your macro, and adapt it according to your needs.
When you do that, the following will appear in your code:
Source_Cell.Copy
Destination_Cell.Paste
I would advise you to replace this by:
Destination_Cell.Value = Source_Cell.Value
The one thing I can't figure out, is the last but one point: what do you mean by "setting a row number"?
Oh, I forgot one thing: in Excel, datetime values are just integer numbers (1 stands for one day), so multiplying two values by 24*3600 and you get the number of seconds.
Answered by Dominique on December 30, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP