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:-
Results Unsorted .

[COLOR="Navy"]Sub[/COLOR] MG14Oct47
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] 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"]Dim[/COLOR] Cop
[COLOR="Navy"]Dim[/COLOR] Pst
 Cop = Array(1, 5, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27)
 Pst = Array(1, 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26, 30)
[COLOR="Navy"]With[/COLOR] Sheets("Combined")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("C4"), .Range("C" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
        ReDim ray(1 To Rng.Count, 1 To 15)
            [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 = 0 To UBound(Cop)
                [COLOR="Navy"]If[/COLOR] n = 1 And Ac > 1 And Ac < UBound(Cop) [COLOR="Navy"]Then[/COLOR]
                    ray(n, Ac + 1) = MonthName(Month(Dn(, Cop(Ac))), True) & "-Tot"
                [COLOR="Navy"]Else[/COLOR]
                    ray(n, Ac + 1) = Dn(, Cop(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 13
                [COLOR="Navy"]If[/COLOR] Not .Item(Dn.Value) = 1 [COLOR="Navy"]Then[/COLOR]
                    ray(.Item(Dn.Value), Ac + 1) = ray(.Item(Dn.Value), Ac + 1) + Dn(, Cop(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"]For[/COLOR] Ac = 0 To UBound(Pst)
    Sheets("Destination").Cells(4, Pst(Ac)).Resize(c) = application.Index(ray, Evaluate("row(" & LBound(ray) & ":" & UBound(ray) & " )"), Ac + 1)
[COLOR="Navy"]Next[/COLOR] Ac
MsgBox "Run!!"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ir121973]

Hi @Mick G, this is again, fantastic, thank you so much for revisiting my post and taking the time and trouble to post a new solution. It truly is appreciated.

All the best and kind regards

Chris

 

[MickG]

Your welcome
Regrds Mick