TransWikia.com

How to shift range of cells to unify values in rows

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:

  1. read value in a3 (8,20 in D19156)
  2. compare a3 value to value in b3 (3,20 in L19156)
  3. if a3=b3, and date in a1=date in b1 or date in a1 is max 1 day higher than b3, and time in a2 is 2 or max 3 sec higher compared to b2, than read next value in a3 (go to 1.)
  4. if a3!=b3, than read values in column b3 and find value that is equal to a3 (8,20 in D19156 = 8,20 in L19159)
  5. read the number of row for a3 value (8,20 is in 19156)
  6. read the number of b3 value (8,20 is in 19519)
  7. set the row counter to 3 (19159-19156)
  8. insert 3 rows (cells B19156:D19158) to columns a1, a2 and a3, above to value 8,20

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

2 Answers

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP