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
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