PDA

View Full Version : Modifying excel data through VBA



chakalido
07-07-2014, 10:11 AM
Hello!
I have some trouble changing this excel data from format 1 to format 2, I told my boss that I could doet but I found it harder that what I expected. the problem is that I need to rearrange the information, such as the numero, which needs to be put on parallel to the data and not over it and also the dayssince, which tells us the total days since today until the date in the raw. putting all this information in correct order and taking out the totals in a new page is not that much problem but the problem is reading it as the data comes in random length series. Which code tips or formulas should I use and in which order? I am planning to use first a boolean with the function isnumeric to read the blocks and then later i don't know what to use to load it to a matrix (range?) it seems easy to doet with one but guys got 15000 data to manage and the last day I spent 4 hours just arranging it. please I need help otherwise I could get fired :(
Any kind of help or advice about the order to follow is very welcome. Thank you very much

Data model:





numero
1235


date


a
0,730229844
0,824992712
0,8910754
0,207179103
24/01/00


b
0,093533973
0,742954165
0,447543154
0,789561771
25/01/00


c
0,445388599
0,261871006
0,848441355
0,16884017
26/01/00


d
0,939590924
0,054993446
0,270982266
0,401500165
27/01/00


e
0,795077783
0,256192901
0,390648391
0,963056254
28/01/00


f
0,431519688
0,564159749
0,034432887
0,333556217
29/01/00


g
0,146387455
0,872931907
0,38941544
0,159426696
30/01/00


h
0,530462297
0,212115688
0,37363141
0,182392945
31/01/00


i
0,51476049
0,548201772
0,976150845
0,098851525
01/02/00


j
0,711398072
0,067160626
0,687721241
0,027383706
02/02/00


k
0,771788243
0,747426884
0,784247298
0,791044794
03/02/00


l
0,658791236
0,019660898
0,314133383
0,145061327
04/02/00


m
0,193252945
0,583310055
0,968759682
0,31653567
05/02/00


n
0,22068699
0,31030548
0,033796761
0,924544293
06/02/00


ñ
0,362384231
0,991565828
0,481601275
0,956265663
07/02/00


o
0,58239276
0,375942528
0,535784077
0,924493567
08/02/00


p
0,236515935
0,557750311
0,023441762
0,910243847
09/02/00


q
0,606452979
0,76436205
0,320071019
0,161598741
10/02/00


r
0,006850638
0,08709637
0,403407667
0,306409466
11/02/00


s
0,2899103
0,315586138
0,980479148
0,490035707
12/02/00


t
0,650660515
0,295493944
0,878186674
0,970512702
13/02/00


u
0,038610083
0,550579862
0,109856038
0,417289966
14/02/00



-
-

-




--
total

12345












numero
1234





v
0,562928397
0,122741418
0,246706341
0,236683886
14/02/00


w
0,010844499
0,814120923
0,168504276
0,128929831
15/02/00


x
0,560163389
0,290640133
0,855471821
0,525374957
16/02/00


y
0,050205549
0,853065673
0,164762477
0,085602949
17/02/00


z
0,316254545
0,034471432
0,957811404
0,599870692
18/02/00


total: 98348934

RESULTS FORMAT


name
value1
value2
value3
value4
date
days since
number


a
0,96744616
0,207810176
0,969817705
0,449287995
24/01/00
100
1235


b
0,61571615
0,152172898
0,607634065
0,846259443
25/01/00
100
1235


c
0,819513864
0,73445847
0,667510376
0,641227101
26/01/00
100
1235


d
0,945269902
0,393498731
0,613122045
0,056427727
27/01/00
100
1235


e
0,179357625
0,960015045
0,205928795
0,247176989
28/01/00
100
1235


f
0,495035684
0,991283832
0,22786266
0,002721088
29/01/00
100
1234


g
0,343283785
0,988925028
0,566433174
0,589712543
30/01/00
100
1234


h
0,815500627
0,639647602
0,867941599
0,263627223
31/01/00
100
1234


i
0,22523379
0,56421092
0,331017485
0,993942348
01/02/00
100
1234


j
0,304904842
0,327341607
0,365227163
0,755969165
02/02/00
100
1234


k
0,097349737
0,491324378
0,550311631
0,528461748
03/02/00
100
1234


l
0,608801789
0,797105591
0,531012006
0,262051007
04/02/00
100
1234


m
0,74772918
0,294153329
0,222966235
0,578013616
05/02/00
100
1234


n
0,394783281
0,670594187
0,97543812
0,134321771
06/02/00
100
1234


ñ
0,498087609
0,029391032
0,222251698
0,068283039
07/02/00
100
1234


o
0,923981589
0,349491141
0,778659265
0,509704437
08/02/00
100
1234


p
0,914654501
0,015896285
0,92254053
0,381342037
09/02/00
100
1234


q
0,234047584
0,343610951
0,785663513
0,866222469
10/02/00
100
1234


r
0,028946792
0,261428832
0,299741859
0,713603334
11/02/00
100
1234


s
0,591556564
0,7393152
0,687215997
0,087306131
12/02/00
100
1234


t
0,598620672
0,666166765
0,875339028
0,861960226
13/02/00
100
1234


u
0,748634727
0,87394075
0,6401052
0,494120693
14/02/00
100
1234

Jan Karel Pieterse
07-08-2014, 12:00 AM
This code should do the trick:


Sub ReshapeData()
Dim oCell As Range
Dim sNumero As String
Application.ScreenUpdating = False
For Each oCell In Intersect(ActiveSheet.UsedRange, Range("A:A"))
If IsNumeric(oCell.Value) And Not IsEmpty(oCell.Value) Then
With Worksheets("Sheet2")
Intersect(oCell.EntireRow, ActiveSheet.UsedRange).Copy
.Paste .Range("A" & .Rows.Count).End(xlUp).Offset(1)
.Range("A" & .Rows.Count).End(xlUp).End(xlToRight).Offset(, 1).Value = sNumero
End With
ElseIf oCell.Value = "numero" Then
sNumero = oCell.Offset(, 1).Value
End If
Next
Application.ScreenUpdating = False
Application.CutCopyMode = False
End Sub