Data analysis help -

[Dope]

I have very detailed P&L with G/L accounts by month. The trial balances by month are provided in each tab
column A is G/L number (e.g., 40102, 40103, 40120, etc), column B is account name (revenues, ancillary revenues, etc) and column C is amount. The problem is G/L account numbers are not consistent across month meaning 40102 in January may not be in february, or there may be G/L accounts in september that are not present in any of the other months. I want to create a master list of G/L accounts from each month (with no duplicates) so i can create consolidated P&L in single tab by using vlookup from the monthly tabs). What's the easiest way to capture and dump all the G/L accounts from the monthly tabs onto a single tab? Any ideas?

 

[Hercules1946]

Dope,

I have been trying to learn how to use the Scripting.Dicionary.

Most, if not all, macros utilizing the Scripting.Dicionary to manipulate raw data have a runtime of 0.000 seconds.

My GetUniqueGLNumbers macro has a runtime of 0.000 seconds.

Hiker
Take a look at this url:

Sorting A Collection Or Dictionary

HTH

 

[hiker95]

Hercules1946,

Thank you for the link - one for my archives.

You may want to check the output of your macro - Dope had asked to sum column C (see my screenshots in reply #8).

 

[Hercules1946]

Hercules1946,

Thank you for the link - one for my archives.

You may want to check the output of your macro - Dope had asked to sum column C (see my screenshots in reply #8).

Hiker
Wasn't sure about the column C figures, and I must have missed the request for totals. (I think) the idea is to build a cross tabulation with the months across the top and the GL codes down the left, and then use LOOKUP or INDEX/MATCH to populate all the figures for each month down the twelve columns. On my updated effort, I moved the overall totals across to Col Q to accomodate this.
If your interested, I ran both our macros with about 12000 records, and mine took 0.156 seconds and yours 2.698 seconds.

HTH

Hercules

 

[Hercules1946]

Hello Dope
This is an interesting task and I couldn't resist taking it a bit further. I gather your trying to build a summary table so Ive allowed for a summary page ("NotThisSheet") thats ignored by the data collection and sets up the space for monthly totals and (ytd) summary totals (supplied by the code).
I believe that the Scripting library is available in Excel 7.0, but you will need to check.
Open the VB Editor ALT+F11. If you have never used it all the windows might be minimised.
CTRL+R opens the Project Explorer. With an Open Workbook it lists all the Sheet names.
Click on the top item (the whole project), Click Tools on the Menu, and References.

In the list that comes up, your looking for an entry called 'Microsoft Scripting Runtime'. The selected items are at the top, unselected below in alphabetical order. If you find it, check its box. If you don't theres an option to browse for the file, which is called SCRRUN.DLL.
You need to perform these steps in the workbook that will be using the macro

Heres the amended code with a snapshot of the results:

Option Explicit
Option Base 1

Sub UniqueGLCodes()

    Dim ws As Worksheet
    Dim dict As Scripting.Dictionary
    Set dict = CreateObject("scripting.dictionary")
    Dim arr
    Dim c               'Individual Values from Range List
    Dim rng1 As Range   'range where values located
    Dim rng2 As Range
    Dim Lrow As Long    'last row used in col A
    Dim ind As Long
    arr = Array("NotThisSheet", "OrThisSheet") 'Names of two sheets not to process

For Each ws In Worksheets
With ws
If .Name = arr(1) Or .Name = arr(2) Then GoTo Skip
Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set rng1 = .Range("A2:A" & Lrow)
Set rng2 = .Range("C2:C" & Lrow)
ind = 0
End With
With dict
    For Each c In rng1.Value
    ind = ind + 1
        If .Exists(c) Then
        .Item(c) = .Item(c) + rng2.Cells(ind).Value
        Else
        .Item(c) = rng2.Cells(ind).Value
        End If
    Next c
    
Skip:
End With

Next ws
Sheets("NotThisSheet").Select
With dict
    Range("A4").Resize(.Count) = Application.Transpose(.Keys)
    Range("C4").Resize(.Count) = Application.Transpose(.Items)
End With
With ActiveSheet
     Lrow = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("A4:C" & Lrow).Sort key1:=.Range("A4"), order1:=1
    .Range("C4:C" & Lrow).Copy .Range("Q4") 'Move totals to allow for 12 month columns
    .Range("C4:C" & Lrow).ClearContents
    .Range("Q2:Q" & Lrow).NumberFormat = "0.00"
End With

End Sub

To Insert the code in the Editor click Insert on the Menu, then Module and paste the code into the code window that appears. Change the spreadsheet name "NotThisSheet" to the correct name.
Close the Editor (ALT+Q) and run the macro. (ALT+F8)

Excel 2010
	A	B	C	D	J	K	L	M	N	O	P	Q
1	Unique			<------------------------------------------------------------------------------Monthly Ledger Balances--------------------------------------------------------->								2013
2	GL Codes			January	July	August	September	October	November	December		Year to Date
3
4	1121											20447.04
5	1131											29179.80
6	1185											15926.40
7	1244											49262.40
8	1245											2091.60
9	1268											23736.96
10	1286											46604.64
11	1288											40494.72
12	1364											8347.68
13	1369											33348.84
14	1400											2811.19
1007	59319											92537.64
1008	59353											56978.88
1009	59439											57061.44
1010	59445											221135.40
1011	59548											92894.88
1012	59587											85805.28
1013	59605											50068.20
1014	59623											78702.36
1015	59716											85991.04
1016	59742											215071.20
1017	59821											93320.76
1018	59822											179466.00
1019	59954											158278.56
1020
1021
1022								Year to date Total				308491027.85
1023
1024
1025
1026								Monthly Cross Total				308491027.85
1027
NotThisSheet

Cell Formulas
Range	Formula
Q1022	=SUM(Q4:Q1019)
Q1026	=SUM(Sheet1!C1001+Sheet2!C1001+Sheet3!C997+Sheet4!C996+Sheet5!C995+Sheet6!C1001+Sheet7!C1001+Sheet8!C1001+Sheet9!C1001+Sheet10!C1001+Sheet11!C1001+Sheet12!C1001)

 

[hiker95]

Hercules1946,

Wasn't sure about the column C figures, and I must have missed the request for totals. (I think) the idea is to build a cross tabulation with the months across the top and the GL codes down the left, and then use LOOKUP or INDEX/MATCH to populate all the figures for each month down the twelve columns. On my updated effort, I moved the overall totals across to Col Q to accomodate this.

See Dope's reply #4.

I guess that we will find out shortly what the results should look like from Dope.

 

[Hercules1946]

See Dope's reply #4.

I guess that we will find out shortly what the results should look like from Dope.

Hiker95
It might be me but I don't see any request for totals in post #4, but in any case we have both included them. I don't really mind which solution Dope uses as long as one of them works for him/her.
Best of luck with your work with dictionaries. They are fairly easy to get the hang of, but its a bit of a drag that the Scripting library isn't enabled by default.

cheers

Hercules

 

[hiker95]

Hercules1946,

It might be me but I don't see any request for totals in post #4

You are correct.

I assumed (I hate that word) that the output should show the unique GL codes in column A, and, the totals in column C.

 

[Hercules1946]

Hercules1946,



You are correct.

I assumed (I hate that word) that the output should show the unique GL codes in column A, and, the totals in column C.

That word takes me back a long way to my first job - I was trained by a chap called Len Rolfe (1962). If you so much as mentioned the word in any context
he would snap at you with "You CAN'T assume anything - You MUST check everything!"

 

[hiker95]

Dope,

We have not had a reply from you - have you tried any of the macros?

Based on your reply #4, with the same screenshots from my reply #8, and, using the Scripting.Dictionary:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub GetUniqueGLNumbersSDV2()
' hiker95, 11/07/2013
' http://www.mrexcel.com/forum/excel-questions/705349-merging-duplicate-cells-but-adding-together-values-another-cell.html
Dim ws As Worksheet, n As Long, nn As Long, lr As Long
Dim wsa As Variant, a As Variant, o As Variant, f As Variant, d As Object
Dim i As Long, ii As Long, iii As Long
n = Sheets.Count - 1
ReDim wsa(1 To n)
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Master" Then
    i = i + 1
    wsa(i) = ws.Name
    lr = ws.Cells(Rows.Count, 1).End(xlUp).Row
    nn = nn + lr - 1
  End If
Next ws
ReDim o(1 To nn, 1 To 3)
For i = LBound(wsa) To UBound(wsa)
  With Sheets(wsa(i))
    a = .Range("A2:C" & .Range("A" & .Rows.Count).End(xlUp).Row)
    For ii = 1 To UBound(a, 1)
      iii = iii + 1
      o(iii, 1) = a(ii, 1): o(iii, 2) = a(ii, 2): o(iii, 3) = a(ii, 3)
    Next ii
  End With
Next i
Set d = CreateObject("Scripting.Dictionary")
d.CompareMode = 1
For i = 1 To UBound(o, 1)
  d(o(i, 1)) = d(o(i, 1)) + o(i, 3)
Next i
Erase o
o = Application.Transpose(Array(d.Keys, d.Items))
ReDim f(1 To UBound(o, 1), 1 To 3)
For i = 1 To UBound(o, 1)
  f(i, 1) = o(i, 1)
  f(i, 3) = o(i, 2)
Next i
With Sheets("Master")
  .Columns("A:C").ClearContents
  .Range("A1").Resize(, 3).Value = [{"G/L","account name","Totals"}]
  .Range("A2").Resize(UBound(f, 1), UBound(f, 2)) = f
  .Columns("A:C").AutoFit
  .Activate
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetUniqueGLNumbersSDV2 macro.

 

[Hercules1946]

It seems likely that Dope has found another solution, which happens often.
The good news is that we have both gained from it. Im very impressed with your work on the arrays, and Im glad to see your off and running with the dictionary
which is really useful in many situations as Im sure you have realised.

Best of luck
Hercules