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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Try this:-
Your Data (Headers) star row 4 column "A".
Results Start:- Sheet(2) Column "A" Row 4.
Code: 
[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
 
Upvote 0
Change lines in red to get Month Name & "Total" in Result Header.
Rich (BB code): 
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
 
Upvote 0
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
 
Upvote 0
Slight oversight there !!!
change the Number below in Red to "3" as shown.
Rich (BB code): 
Else
           For Ac = 3 To 8
                If Not .Item(Dn.Value) = 1 Then
Regrds Mick
 
Upvote 0
Hi @mick G, that's great, thank you very much for all your time and trouble, it is greatly appreciated.

Kind regards

Chris
 
Upvote 0
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:
Code: 
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
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,228
Messages
6,170,876
Members
452,363
Latest member
merico17

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