Data analysis help -

Dope

New Member
Joined
May 12, 2009
Messages
16
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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Welcome to the Board!

Can you get access to the raw GL data? If you can do that, then you can use an Access query, or in Excel you can remove duplicates, or even use a Pivot Table. It would also make reporting a lot easier if the data is consolidated from the get go...

If not, then you'd probably need some VBA code to consolidate everything.
 
Upvote 0
Hi
Would you have enough rows on one sheet to accommodate all the records you want in the single tab ? If you can start by just copying them (with all the duplicates), you can use the Advanced Filter to provide a list of unique values. Im told that this can 'fall over' if theres a huge amount of data, but you can also use a scripting dictionary if so. You need VBA Code for this though, but its great for compiling a large list of unique values (keys).

If I understand correctly your just wanting the unique list of G/L numbers in order to find every amount for the year in your lookups?

HTH

Hercules1946
 
Upvote 0
Hi
Would you have enough rows on one sheet to accommodate all the records you want in the single tab ? If you can start by just copying them (with all the duplicates), you can use the Advanced Filter to provide a list of unique values. Im told that this can 'fall over' if theres a huge amount of data, but you can also use a scripting dictionary if so. You need VBA Code for this though, but its great for compiling a large list of unique values (keys).

If I understand correctly your just wanting the unique list of G/L numbers in order to find every amount for the year in your lookups?

HTH

Hercules1946

For e.g., Tab 1 (january)
G/L (col a) Amount (column C)
400 $200
402 $300
403 $400

Tab 2 (February)
G/L (col a) Amount (column C)
400 $200
405 $300
409 $400

As you can see 402 and 403 are not in tab 2, and 409 and 405 are not in tab 1. I want to create a master tab with all the unique G/L codes in one column (400, 402, 403, 405 and 403). Yes, i could copy and paste all the G/L codes from each month and then eliminate the duplicates, but that's time consuming - i was wondering if there was an easier way to do it in one shot.
 
Upvote 0
The dictionary would be the quickest way because once you have established it you can do 12 months in a single pass, and if you need it again next year (new G/L Codes) its there. Are you familiar with VBA and what version of Excel do you have ?
 
Upvote 0
The dictionary would be the quickest way because once you have established it you can do 12 months in a single pass, and if you need it again next year (new G/L Codes) its there. Are you familiar with VBA and what version of Excel do you have ?

Excel 7. What's the dictionary - no, not too familiar with VBA, but if you give me the program, i can tailor the cell references to fit my workbook.
 
Upvote 0
Do you mean Excel 2007 ? Ive never heard of Excel 7. The dictionary is a scripting object that allows you to process data and easily identify the unique records from a given list of values.
I don't have a library of coding like some of our expert members, but I could put something together given a bit of time. Please answer these questions:

1. Its correct that you want to process a number of sheets in a workbook, and come up with one list of unique G/L codes from Col A ?
2. Is that all the sheets in the workbook, and if not, how do you know what to include or exclude? (whichever is easiest)
3. Are the G/L Codes always in Col A, and does Col A contain only G/L Codes in all the relevant sheets?
4. Does Col A always have a continuous list or does it have empty cells between some codes?

If you really have Excel 7 that was published almost 18 years ago so Im not sure about the VBA

Hercules
 
Last edited:
Upvote 0
Dope,

Here is a solution without the use of the Scripting.Dictionary.

Sample worksheets:


Excel 2007
ABC
1G/Laccount nameAmount
2400$200
3402$300
4403$400
5
January



Excel 2007
ABC
1G/Laccount nameAmount
2400$200
3405$300
4409$400
5
February


Worksheet Master before the macro:


Excel 2007
ABC
1G/Laccount nameAmount
2
3
4
5
6
7
Master


Worksheet Master after the macro:


Excel 2007
ABC
1G/Laccount nameAmount
2400$400.00
3402$300.00
4403$400.00
5405$300.00
6409$400.00
7
Master


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).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Option Explicit
Sub GetUniqueGLNumbers()
' hiker95, 11/01/2013
' http://www.mrexcel.com/forum/excel-questions/736649-data-analysis-help.html
Dim ws As Worksheet, n As Long, nn As Long, lr As Long, nr As Long
Dim wsa As Variant, a As Variant, o As Variant
Dim i As Long, ii As Long, iii As Long, rr As Long, fr 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)
      fr = 0
      For rr = LBound(o, 1) To UBound(o, 1)
        If a(ii, 1) = o(rr, 1) Then
          fr = rr
          Exit For
        End If
      Next rr
      If fr = 0 Then  'add to o array
        iii = iii + 1
        o(iii, 1) = a(ii, 1)
        o(iii, 3) = a(ii, 3)
      ElseIf fr > 0 Then
        o(fr, 3) = o(fr, 3) + a(ii, 3)
      End If
    Next ii
  End With
Next i
With Sheets("Master")
  nr = .Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  .Cells(nr, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Range("A2:C" & lr).Sort key1:=.Range("A2"), order1:=1
  .Columns.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 GetUniqueGLNumbers macro.
 
Last edited:
Upvote 0
Seen as I have it now heres my solution:


Excel 2010
A
1
22254
39681
411495
512935
614046
Sheet2



Excel 2010
A
22254
36626
49681
510028
611495
712230
812935
Sheet3


Below is summary from Sheets 2 and 3:


Excel 2010
D
22254
39681
411495
512935
614046
76626
810028
912230
NotThisSheet


Heres the code: (if your macro enabled and you want to try it let me know and I'll provide instructions)
It by-passes 'NotThisSheet' and puts the list compiled from Sheets2 and 3 in there at the end.

Code:
Option Explicit
Option Base 1

Sub UniqueGLCodes()
    Dim arr
    Dim ws As Worksheet
    Dim dict As Scripting.Dictionary
    Set dict = CreateObject("scripting.dictionary")
    Dim c               'Individual Values from Range List
    Dim rng1 As Range   'range where values located
    Dim Lrow As Long    'last row used in col A
    arr = Array("NotThisSheet", "OrThisSheet")

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)
End With
With dict
    For Each c In rng1.Value
        .Item(c) = 1
    Next c
    
Skip:
End With

Next ws
Sheets("NotThisSheet").Select
With dict
    Range("D2").Resize(.Count) = Application.Transpose(.Keys)
End With

End Sub
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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