how can i manage it.....?

[deepakbathla]

Hello frnz....

If i have data this type.....days & Model category-wise(CTV,FPD,DC & REF)....

this data is more than 50,000 rows..just inform u.....


Day CTV FPD DC FF

1 12 55 65 25

1 12 55 65 25

1 12 55 65 25

1 12 55 65 25

1 12 55 65 25

2 50 16 70 34

3 54 8 71 12

4 75 80 81 60

5 60 75 75 12

6 65 54 85 12


but i want data this type....


Day...1.... 2....3..4..5..6....

CTV...60...50....

FPD...275..16....

DC....325..70....

FF....125..34....


how will i manage it.....?

plz suggest me...


Regards/ Thanks

Deepak Bathla

 

[xld]

Try this macro

[pre]

Public Sub ProcessData()
Dim lastrow As Long
Dim lastcol As Long
Dim endcol As Long
Dim i As Long, ii As Long

Application.ScreenUpdating = False

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:E1").Resize(lastrow).Copy
.Range("H1").PasteSpecial Paste:=xlPasteAll, Transpose:=True

lastrow = .Cells(.Rows.Count, "H").End(xlUp).Row
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = lastcol - 1 To 9 Step -1

endcol = i + 1
Do While .Cells(1, i).Value = .Cells(1, i + 1).Value

i = i - 1
Loop
If endcol - 1 <> i Then

For ii = 2 To lastrow

.Cells(ii, i + 1).Value = Application.Sum(.Cells(ii, i + 1).Resize(, endcol - i))
Next ii

.Columns(i + 2).Resize(, endcol - i - 1).Delete
End If
Next i
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

[/pre]

 

[NARAYANK991]

Hi Deepak ,


If you want a formula oriented solution , try this :


=SUM(OFFSET($A$2:$E$11,MATCH(J$1,$A$2:$A$11,0)-1,MATCH($I2,$B$1:$E$1,0),COUNTIF($A$2:$A$11,J$1),1))


Copy this down / across as far as required.


The labels CTV , FPD ,... are the row headers , in I2 , I3 , I4 , I5 ,...


The labels 1 , 2 , 3 ,... are the column headers , in J1 , K1 , L1 ,....


Narayan