Create Summary Table From Data Sheet

[ir121973]

Hi, I wonder whether someone may be able to help me please.

Firstly, my apologies because I'm not even sure whether this possible.

I have a Excel sheet (Sheet 1) which contains the following 'Dynamic' information which runs from column A to I with data rows starting at row 5.

NAME	FTE	Oct-12	Nov-12	Dec-12	Jan-13	Feb-13	Mar-13	Manager
Person 1	1.00	1	2					Manager A
Person 2	1.00	20	20	20	20	20	20	Manager B
Person 3	0.78	2		2			2	Manager C
Person 1	1.00	0.75	5					Manager A
Person 2	1.00	2	2	2	2	2	2	Manager B
Person 5	0.50	0.5	0.5	0.5	0.5	0.5	0.5	Manager F
Person 3	0.78							Manager C
Person 4	1.50	10	10	10	10	10	10	Manager D

<tbody>
</tbody>

On 'Sheet 2', I then have a Summary table which pulls it's data from Sheet 1.

The Summary table is in the following format.

Name (Column A)
FTE (Column B)
October Total (Column C)
November Total (Column D)
December Total (Column E)
January Total (Column F)
February Total (Column G)
March Total (Column H)
Manager (Column I)

with the data rows starting at row 5.

What I need to be able to do is search the data in the first sheet, find the first instance of the Name, copy this along with the the FTE and Manager. Then I need to search the table of all records pertinent to that person and add all the figures for each month, so using the above as an example the data would show:

NAME	FTE	Oct Total	Nov Total	Dec Total	Jan Total	Feb Total	Mar Total	Manager
Person 1	1.00	1.75	7					Manager A
Person 2	1.00	22	22	22	22	22	22	Manager B
Person 3	0.78	2		2			2	Manager C
Person 4	1.50	10	10	10	10	10	10	Manager D
Person 5	0.50	0.5	0.5	0.5	0.5	0.5	0.5	Manager F

<tbody>
</tbody>

In it's current format I'm using a very cumbersome and time consuming method with many formulas and a lot of copying and pasting.

I just wondered whether someone could possibly take a look at this please and offer a little guidance on whether there may be a more efficient way of combining this information.

Many thanks and kind regards

 

[MickG]

Try this:-
Your Data (Headers) star row 4 column "A".
Results Start:- Sheet(2) Column "A" Row 4.

[COLOR="Navy"]Sub[/COLOR] MG13Oct10
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A4"), Range("A" & Rows.Count).End(xlUp))
        ReDim Ray(1 To Rng.Count, 1 To 9)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        n = n + 1
            [COLOR="Navy"]For[/COLOR] Ac = 1 To 9
                [COLOR="Navy"]If[/COLOR] n = 1 And Ac > 1 And Ac < 9 [COLOR="Navy"]Then[/COLOR]
                    Ray(n, Ac) = Format(Dn(, Ac), "dd-mmm-yy")
                [COLOR="Navy"]Else[/COLOR]
                    Ray(n, Ac) = Dn(, Ac)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Ac
        .Add Dn.Value, n
    [COLOR="Navy"]Else[/COLOR]
           [COLOR="Navy"]For[/COLOR] Ac = 2 To 8
                [COLOR="Navy"]If[/COLOR] Not .Item(Dn.Value) = 1 [COLOR="Navy"]Then[/COLOR]
                    Ray(.Item(Dn.Value), Ac) = Ray(.Item(Dn.Value), Ac) + Dn(, Ac).Value
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Ac
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
c = .Count
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2")
    .Range("A4").Resize(c, 9) = Ray
    .Range("A4").Resize(c, 9).Sort .Range("A4"), xlAscending
[COLOR="Navy"]End[/COLOR] With
MsgBox "Run!!"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

Change lines in red to get Month Name & "Total" in Result Header.

For Ac = 1 To 9
                If n = 1 And Ac > 2 And Ac < 9 Then
                    Ray(n, Ac) = MonthName(Month(Dn(, Ac)), True) & " Total"
                Else
                    Ray(n, Ac) = Dn(, Ac)
                End If
            Next Ac

 

[ir121973]

Hi @Mick G, this is absolutely brilliant, thank you so much for taking the time to reply and post the solution.

It works great, but could you just tell me please, how could I change this so the FTE column only shows the first instance as is the case with the Manager, rather than totalling this up.

Many thanks and kind regards

 

[MickG]

Slight oversight there !!!
change the Number below in Red to "3" as shown.

Else
           For Ac = 3 To 8
                If Not .Item(Dn.Value) = 1 Then

Regrds Mick

 

[ir121973]

Hi @mick G, that's great, thank you very much for all your time and trouble, it is greatly appreciated.

Kind regards

Chris

 

[ir121973]

Hi, I wonder whether someone may be able to help me please.

Firstly, my sincere apoligies to @MickG, on this forum. I added a post to the site yesterday and @MickG gave me a brilliant solution as above, but on reflection I've let this member down because I had got confused over some of my requirements, so I'm very sorry.

The solution that MickG helped me with works great, but in my original post, I had said the Source sheet was called 'Sheet1' and the copy range in this sheet was columns 'A:I'. Having looked at this today, I've realised that the Source sheet is called 'Combined ' and although the copy range starts from row 4 as before, the columns to copy from are 'B', 'F', and 'Q:AC'.

I've also made a mistake for which I'm sorry for, in how the data needs to be pasted in the Destination sheet. Again although the paste range starts at row 4, the columns that this data needs to be copied into are columns 'A', 'B', 'D', 'F', 'H', 'J', 'L', 'N', 'P', 'R', 'T', 'V', 'X', 'Z' and 'AD'

I have already made some minor changes to the code because I needed to change the Destination sheet name, and I thought it would be easy to make these other changes, but so far I've just not been able to get these to work.

This is the code I'm currently working with:

Sub MG13Oct10()
Dim Rng As Range, Dn As Range, n As Long
Dim Ac As Integer
Dim c As Long
    Set Rng = Range(Range("A4"), Range("A" & Rows.Count).End(xlUp))
        ReDim Ray(1 To Rng.Count, 1 To 9)
With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
For Each Dn In Rng
    If Not .Exists(Dn.Value) Then
        n = n + 1
        For Ac = 1 To 9
                If n = 1 And Ac > 2 And Ac < 9 Then
                    Ray(n, Ac) = MonthName(Month(Dn(, Ac)), True) & " Total"
                Else
                    Ray(n, Ac) = Dn(, Ac)
                End If
            Next Ac
        .Add Dn.Value, n
    Else
           For Ac = 3 To 8
                If Not .Item(Dn.Value) = 1 Then
                    Ray(.Item(Dn.Value), Ac) = Ray(.Item(Dn.Value), Ac) + Dn(, Ac).Value
                End If
            Next Ac
   End If
Next
c = .Count
End With
With Sheets("Resource Summary 12-13")
    .Range("A4").Resize(c, 9) = Ray
    .Range("A4").Resize(c, 9).Sort .Range("A4"), xlAscending
End With
MsgBox "Run!!"
End Sub

I'm very conscious and realise that members of this site have got better things to do than return to a previously 'solved' post, but I'm at a loss as to where I need to make these amendments. i just wondered whether someone may be able to take a look at this please and offer a little guidance on how I may be able to make these changes.

Many thanks and kind regards

 

[MickG]

Can I assume thast the new data is basically the same, with columns "Q" being "April Tot" to "AB" Being "March-Tot" and column "AC" being "Manager".
Can I also assume there is already data in "Destination" sheet in the columns next to those specified.

 

[ir121973]

Hi MickG, thank you so much for getting back to me, and again my apologies for messing your around.

Since my last post I've made a few tweaks to the Source file, so for the confirmation in the copy range the columns are as follows:

Name (Column C)
FTE (Column G)
Manager (Column Q), and
April 12 to March 13 (Columns S:AD)

For the Destination sheet, you are correct, in that there is data in the columns next to those specified. For confirmation the layout is as follows:

Name (Column A)
FTE (Column B)
April Total (Column D)
May Total (Column F)
June Total (Column H)
July Total (Column J)
August Total (Column L)
September Total (Column N)
October Total (Column P)
November Total (Column R)
December Total (Column T)
January Total (Column V)
February Total (Column X)
March Total (Column Z)
Manager (Column AD)

I can also confirm that this is my template layout and will not be changing anymore.

Once again, many thanks for all your time and trouble.

Kind regards

Chris

 

[MickG]

In my first code I sorted the Results data based on the first column, is that necessary, and if so which column do your require the data to be sorted by ???
Mick