Help re-organising the data

**billos** · 12-10-2011, 03:27 PM

Hello,
I uploaded my excel file-example that in Sheet1 has the columns ID,Date,Type,Name and Price. The data in these columns must be copied in Sheet2 but with different structure. The lines must be sorted according to ID number. Also every time a new entry is put in Sheet 1, the Sheet2 must be updated accordingly(in the correct position and structure).
Firstly, can it be done without macros and just using some given functions in sheet2? In this case the difficult thing will be the constant update of Sheet2 which troubles me..
Secondly, If macros and VBA programming are needed what would the code-subs-functions be??
I have some programming experience but none in office so i need to see if it can be done before I spend time trying to make it.Laslty it will be run on Office 2003.
Thank you in advance

**mrice** · 12-10-2011, 05:19 PM

Try this code pasted into the tab for the output sheet (Sheet2 ?) in the VBA editor. It will regenerate the contents of the output sheet every time it is activated.

Private Sub Worksheet_Activate()
Cells.Clear
Sheets(1).Cells(1, 1).CurrentRegion.Copy Destination:=Cells(1, 1)
Cells(1, 1).CurrentRegion.Sort Header:=xlYes, Key1:=Cells(1, 1), Key1:=Cells(1, 4)
For N = Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1
    If Cells(N, 1) = Cells(N - 1, 1) Then
        Cells(N, 1).Clear
        Cells(N, 4).Clear
    End If
Next N
Columns(2).Insert
Columns(5).Copy Destination:=Columns(2)
Columns(5).Delete
Columns.AutoFit

For N = Cells(Rows.Count, 2).End(xlUp).Row To 3 Step -1
    If Cells(N - 1, 2) <> "" Then
        Rows(N).Insert
        Range(Cells(N - 1, 3), Cells(N - 1, 5)).Cut Destination:=Range(Cells(N, 3), Cells(N, 5))
        
    End If
Next N

For N = Cells(Rows.Count, 2).End(xlUp).Row To 3 Step -1
    If Cells(N, 2) <> "" Then
        Rows(N).Insert
    End If
Next N
End Sub

**jpr73** · 12-11-2011, 07:06 AM

Hi,
See attached a solution without code, using pivot table
Make a Table of your data in sheet "Data_Table" : automaticaly increase with new data
Make a pivot table in sheet "PT"
Each time you add a data, you must "Update all" in options pivot table
Also check that the option "Refresh data when opening the file" is still activated
I hope this will help
Best regards

**billos** · 12-11-2011, 10:53 PM

Thank you for your quick responses!
The pivot solution is more fitting so i started with that but I faced two more problems.
1) In excel 2003 i cant seem to be able to place any filter in any of the columns of the pivot table..is there any work around that (because in office 2007 there is such an option but unfortunately i cant use them for this project)
2) If a person has more than one entries then i would like for the table to show the sum of prices for each person apart from showing each one of the prices separately.(for example in the example for Smith the prices 234,134,566 must show and then the sum of these -934- ).I tried some of the pivot tables properties but didnt seem to figure it out..

Thanks again for the help

**billos** · 12-12-2011, 06:53 AM

PS: Also about the filtering problem I wanted to add that the reason that I tried to filter the results in the pivot table is that I want to create 2 different pivot tables each one with a different ID range...I tried to filter the results but maybe if filtering isnt possible in excel2003 then there is aanother solution..?

**billos** · 12-12-2011, 12:14 PM

Ok so I solved the second problem with the sums(it had to do with how the partial sums where shown)
So now the filtering problem remains...if the filters cant work, then the question is..:
Can i create two different pivot tables in different sheets from a single table depending on a value range (eg if id > 50 then the data must be stored in pivot table 1 in sheet1 else in pivottable2 in sheet2.) Can it be done?
Thanks in advance