Identifying unique data

[MrJamesS]

Hi,

This is a tricky one to explain so bear with me:

I have a set of data which are telephone calls made to a national freephone number which are then directed to a local number. Depending on the location of the call, the number will be directed to the area where the call was made. Each call made has a cost based on the length of the call. Each call made creates 2 rows of data - one with the information from the original caller, and one with the information of where the call was directed to. I have given an extract of the data below as an example

The cost in Column G is against the original caller. I want it to be against where the call ended up getting routed to.

I ultimately want the cost in Column G to be against the row that has "NGN Outbound Offnet" in it. This is because I need to summarise the data relating to the number in Column B, but only the number that has the "NGN Outbound Offnet" information in that row. This is assumed/known that the Data/Time/Length of call in Columns C-E are unique. Therefore 0.2 in Row 3 needs to be in Row 2. 0.4 in Row 4 needs to be in row 5. Note that whilst each call, i.e. 2 rows of data, always occur as a group of 2, the order of each of the 2 rows are not which is where it gets tricky

Unfortunately there is actually nothing unique to identify the 2 rows of data as 1 piece of data. You would assume the Date/Time/Length for each call to be consistent, however rows 16 and 17 shows this not to be the case. This happens quite a few times in the data. For some reason the time or length of call (which is in seconds) can sometimes be 1 second out. This is an issue with the data provider that cannot be fixed. I've tried a bunch of nested IF/AND/OR formulas to try and take into account the second being out but doesn't work in all cases.

I've tried looking up a macro/VBA to move every other row to the side of the row above and delete the now empty row but can't get it to work. If they were side by side, a simple if formula would work fine.

Note that the above is a an example, there are many more columns with data (nothing unique!), and 42000 rows, so if you can provide a VBA solution I may need to alter it slightly to get it to work!

I have a number of tabs of information like this I need to complete this for, so if a VBA solution can be found for all of them, great! Assume the tab name what is in column A (National No 1)

Thanks in advance

 

[MickG]

Give this a try !!!

[COLOR="Navy"]Sub[/COLOR] MG07Dec25
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Tp1 [COLOR="Navy"]As[/COLOR] Double, Tp2 [COLOR="Navy"]As[/COLOR] Double, omax [COLOR="Navy"]As[/COLOR] Double
Lst = Range("A" & Rows.Count).End(xlUp).Row
[COLOR="Navy"]For[/COLOR] n = 2 To Lst [COLOR="Navy"]Step[/COLOR] 2
  [COLOR="Navy"]If[/COLOR] DateDiff("s", Cells(n, "D").Value, Cells(n + 1, "D").Value) <= 1 And _
        Cells(n, "E").Value = Cells(n + 1, "E").Value [COLOR="Navy"]Then[/COLOR]
        Tp1 = Cells(n, "G").Value
        Tp2 = Cells(n + 1, "G").Value
        omax = Application.Max(Tp1, Tp2)
        [COLOR="Navy"]If[/COLOR] Cells(n, "F") = "NGN Outbound Offnet" [COLOR="Navy"]Then[/COLOR]
            Cells(n, "G").Value = omax
            Cells(n + 1, "G").Value = 0
        [COLOR="Navy"]Else[/COLOR]
            Cells(n, "G").Value = 0
            Cells(n + 1, "G").Value = omax
        [COLOR="Navy"]End[/COLOR] If
        omax = 0: Tp1 = 0: Tp2 = 0
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MrJamesS]

Hi Mick,

Apologies for the delay, but thanks for your answer. I have tested this on a sample of large data and come unstuck in a couple of situations:

Is it possible to instead, for every other row, starting from row 2, to move the data in columns A-G into Columns H-M, and delete the now empty row?

Thanks,

 

[MickG]

Based on your data, moving every other row will result in some rows in "H To M" being"NGN Outbound Offnet" an others being
"FreePhone Inbound From Land Line", Is that what you want.

 

[MrJamesS]

Hi Mick

I think so - at least I know that the cost for will either be column H or Column M - I could then have a bunch of formulas in Column N onwards that would simplify the information

Thanks,

 

[MickG]

Try ??:-

[COLOR="Navy"]Sub[/COLOR] MG18Dec37
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Row Mod 2 = 0 [COLOR="Navy"]Then[/COLOR]
        Dn.Resize(, 7).Copy Dn.Offset(, 7)
        Dn.Resize(, 7).ClearContents
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MrJamesS]

Hi Mick,

Sorry, I don't think I was clear. This works, but what I want to do is put the data moved from cells A2:G2 into H3:N3 and delete row 2 (or move data from A3:G3 into H2:N2 and delete row 3). Now it makes sense as to why you asked!

This coding is just moving data from A2:G2 into H2:N2

Thanks,

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG18Dec03
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Application.ScreenUpdating = False
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    c = c + 1
    [COLOR="Navy"]If[/COLOR] c Mod 2 = 0 [COLOR="Navy"]Then[/COLOR]
        Dn.Resize(, 7).Copy Dn.Offset(-1, 7)
        Dn.Resize(, 7).ClearContents
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick