Create Summary Table From Data Sheet

I

ir121973

Active Member
Joined
Feb 9, 2008
Messages
371
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.

[TABLE="width: 641"]
<tbody>[TR]
[TD="align: center"]NAME[/TD]
[TD="align: center"]FTE[/TD]
[TD="align: center"]Oct-12[/TD]
[TD="align: center"]Nov-12[/TD]
[TD="align: center"]Dec-12[/TD]
[TD="align: center"]Jan-13[/TD]
[TD="align: center"]Feb-13[/TD]
[TD="align: center"]Mar-13[/TD]
[TD="align: center"]Manager[/TD]
[/TR]
[TR]
[TD="align: center"]Person 1[/TD]
[TD="align: center"]1.00[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Manager A[/TD]
[/TR]
[TR]
[TD="align: center"]Person 2[/TD]
[TD="align: center"]1.00[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]Manager B[/TD]
[/TR]
[TR]
[TD="align: center"]Person 3[/TD]
[TD="align: center"]0.78[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Manager C[/TD]
[/TR]
[TR]
[TD="align: center"]Person 1[/TD]
[TD="align: center"]1.00[/TD]
[TD="align: center"]0.75[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Manager A[/TD]
[/TR]
[TR]
[TD="align: center"]Person 2[/TD]
[TD="align: center"]1.00[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Manager B[/TD]
[/TR]
[TR]
[TD="align: center"]Person 5[/TD]
[TD="align: center"]0.50[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]Manager F[/TD]
[/TR]
[TR]
[TD="align: center"]Person 3[/TD]
[TD="align: center"]0.78[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Manager C[/TD]
[/TR]
[TR]
[TD="align: center"]Person 4[/TD]
[TD="align: center"]1.50[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]Manager D[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 666"]
<tbody>[TR]
[TD="align: center"]NAME[/TD]
[TD="align: center"]FTE[/TD]
[TD="align: center"]Oct Total[/TD]
[TD="align: center"]Nov Total[/TD]
[TD="align: center"]Dec Total[/TD]
[TD="align: center"]Jan Total[/TD]
[TD="align: center"]Feb Total[/TD]
[TD="align: center"]Mar Total[/TD]
[TD="align: center"]Manager[/TD]
[/TR]
[TR]
[TD="align: center"]Person 1[/TD]
[TD="align: center"]1.00[/TD]
[TD="align: center"]1.75[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Manager A[/TD]
[/TR]
[TR]
[TD="align: center"]Person 2[/TD]
[TD="align: center"]1.00[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]Manager B[/TD]
[/TR]
[TR]
[TD="align: center"]Person 3[/TD]
[TD="align: center"]0.78[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Manager C[/TD]
[/TR]
[TR]
[TD="align: center"]Person 4[/TD]
[TD="align: center"]1.50[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]Manager D[/TD]
[/TR]
[TR]
[TD="align: center"]Person 5[/TD]
[TD="align: center"]0.50[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]Manager F[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Sort by date Sort by votes
Try this:-
Results Unsorted .
Code: 
[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
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Copying date from one sheet to another based on time left
Replies
2
Views
114
Vogateer
V
P
  • Question Question
Moving rows to different sheets based on drop down.
2
Replies
12
Views
979
purevega
P
V
  • Question Question
Toggle Feature on Summary
Replies
3
Views
235
myall_blues
myall_blues
P
  • Question Question
Report with Grouping
2
Replies
19
Views
426
DanteAmor
DanteAmor
T
  • Solved
Extrapolate Values between 2 Rows
Replies
4
Views
208
TheTiredEngineer
T

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top