Creating summary from one sheet

[LisaUK]

Hi All,

I have a sheet of information in excel 2007 and it is made up as follows


Company Sales Month
ABC 1000 Jan 10
ABC 15000 Jan 10
123 200 Feb 10
ABC 5500 Jan 10
567 900 Mar 11
ABC 300 Feb 11



As I have multiple entries for each company for the same month what I am trying to get to is


Company Sales Month
ABC Total Sales For the Month of Jan 10
ABC Total Sales For The month of Feb 10
123 Total Sales For the Month of Jan 10



Etc

I hope it makes sense, so I am trying to break down each company total sales by month from the data I already have, any ideas would be appreciated.

Thank you

Lisa

 

[MickG]

Try this:-
Results start "E1".

[COLOR="Navy"]Sub[/COLOR] MG08Jun43
[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] Twn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 3)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            Twn = Dn & Dn(, 3)
            [COLOR="Navy"]If[/COLOR] Not .Exists(Twn) [COLOR="Navy"]Then[/COLOR]
                n = n + 1
                .Add Twn, Array(Dn(, 2), n)
                ray(n, 1) = Dn: ray(n, 2) = Dn(, 2): ray(n, 3) = Format(Dn(, 3), "mmm-yy")
            [COLOR="Navy"]Else[/COLOR]
                Q = .Item(Twn)
                Q(0) = Q(0) + Dn(, 2)
                ray(Q(1), 2) = Q(0)
                .Item(Twn) = Q
            [COLOR="Navy"]End[/COLOR] If
       [COLOR="Navy"]Next[/COLOR]
Range("E1").Resize(.Count, 3) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[LisaUK]

Hi Mick,

You have come to the rescue again, thank you for such a quick reply.
I tried the suggestion and I get a runtime error 13 at line

Twn = Dn & Dn(, 3)

Type mismatch

Any ideas what this might be?

Thanks
Lisa

 

[MickG]

I Can't replicate that error!
Can you show a small examole of the data it fails on.
NB:-To Paste you data to a thread . Place a border around each cell you want to Copy (to form a grid) then Copy and paste the range to the Thread.

 

[LisaUK]

Mick,

I just had to change the first column format to text rather than general and it all works a treat

Thank you so much for your help again.

Regards
Lisa

 

[LisaUK]

Hi Mick,

Sorry to bring this one back up, I have something strange going on with the date month column.

When I run the code I get month as

10-May instead of May-10 and if I try to refomat the output it gives the wrong date.

Any Ideas?

Many Thanks
Lisa

 

[MickG]

If your still using the same ranges to return the Results , you could try adding (Red line) as shown.

Range("E1").Resize(.Count, 3) = Ray
End With
Columns("G:G").NumberFormat = "mmm-yy"
End Sub

 

[LisaUK]

Mick,

I tried that, I get the result of month-11 for everything.
So instead of May-10, June-10, June-11 I am getting
May-11, June-11, Feb-11 etc, not sure what this could be I tried formatting the cell in different date format but still no luck, any ideas?

Thank you
Lisa

 

[MickG]

I've really no idea, you could sent a sample of data that fails, (copy with borders, as I specified in previous thread), and I'll see what i get.

 

[LisaUK]

<table border="0" cellpadding="0" cellspacing="0" width="352"><colgroup><col style="mso-width-source:userset;mso-width-alt:6692;width:137pt" width="183"> <col style="mso-width-source:userset;mso-width-alt:3840;width:79pt" width="105"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt;width:137pt" height="20" width="183"> CB AUTOMATE TECHNOLOGY LTD </td> <td class="xl68" style="border-left:none;width:79pt" width="105">15.00</td> <td class="xl66" style="border-left:none;width:48pt" width="64">May-10</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt;border-top:none" height="20"> CB AUTOMATE TECHNOLOGY LTD </td> <td class="xl68" style="border-top:none;border-left:none">33.16</td> <td class="xl66" style="border-top:none;border-left:none">Sep-10</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl71" style="height:15.0pt;border-top:none" height="20"> CB AUTOMATE TECHNOLOGY LTD </td> <td class="xl69" style="border-top:none;border-left:none">111.30</td> <td class="xl67" style="border-top:none;border-left:none">Oct-10</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt;border-top:none" height="20">121 COMPUTER SERVICES LTD </td> <td class="xl68" style="border-top:none;border-left:none">50.00</td> <td class="xl66" style="border-top:none;border-left:none">Jan-10</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt;border-top:none" height="20">121 COMPUTER SERVICES LTD </td> <td class="xl68" style="border-top:none;border-left:none">36.75</td> <td class="xl66" style="border-top:none;border-left:none">May-10</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt;border-top:none" height="20">121 COMPUTER SERVICES LTD </td> <td class="xl68" style="border-top:none;border-left:none">100.00</td> <td class="xl66" style="border-top:none;border-left:none">May-10</td> </tr> </tbody></table>