Retrieve a column of the same name from another workbook

VBAnewbie82

New Member
Joined
Nov 13, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I have two large workbooks that I need to combine as one. First report is the budget for specific year (workbook BUDGET with one sheet “Sheet1”) and second is estimate for same year (workbook ESTIMATE with one sheet “Sheet1”). Layout of these are identical as follows, only the numbers or the money in budget columns change:

Row1 = cost center numbers for each column
Row2 = cost center names
Row3 = header “Budget” for budget or estimate or header “Results” for current results
Row 4 to x = actual budgets and results in currency

Example for workbook “BUDGET” Sheet1 could look like this:
5555; 5555; 6666; 6666; 7777; 7777;
MAN; MAN; ICT; ICT; HRM; HRM;
Budget; Result; Budget; Result; Budget; Result;
900; 600; 700; 200; 500; 400;

Example for workbook “ESTIMATE” Sheet1 could look like this:
5555; 5555; 6666; 6666; 7777; 7777;
MAN; MAN; ICT; ICT; HRM; HRM;
Budget; Result; Budget; Result; Budget; Result;
1200; 600; 400; 200; 800; 400;

Example for what I am trying to achieve should look like this:
5555; 5555; 5555, 6666, 6666; 6666; 7777, 7777; 7777;
MAN; MAN; MAN; ICT; ICT; ICT; HRM; HRM; HRM;
Budget; Estimate; Result; Budget; Estimate; Result; Budget; Estimate; Result;
900; 1200; 600; 700; 400; 200; 500; 800; 400;

Macro could make this for a new workbook or use ESTIMATE workbook as the one where it is bringing columns from BUDGET. There are around 90 different cost centers = aroung 180 columns in each workbook and around 600 rows in both of them.
 
I assume that the following scenarios are also possible:
-Either the Budget sheet or the Estimate sheet could have the larger number of rows
-The order of the CCnames in column A is not necessarily the same in each of the two sheets
-There could be a CCname in Budget that is not included in Estimate and vice versa
Are all of these scenarios possible?
Almost.
- Sheets might have different number of rows as is in my example, where Account 4 is missing from the Budget but is in the Estimate.
- Column A is for Account numbers, B is for their names. Order is always same, but some might be missing from the Budget as I just wrote referring to my example = number of rows varies, but columns stay identical.
- No, CCnames (cost center names) are the ones stated in row 2. Their numeric ID is in row 1 (CCid). So for example 7777 is HRM. These do not change and there is always same amount of columns (cost centers) in exported data even though data might be othervice empty. I mean if I export data from January 1st, there is a lot of empty cells in my report. Only the budget column has data, but since year has just started, many of the results are zero (empty). In real life there are around 180 columns (around 90 cost centers) in my exported reports.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
There could be a CCname in Budget that is not included in Estimate and vice versa
For example:
Acc4 is in Estimate but not in Budget
Acc8 is in Budget but not in Estimate

Is this possible?
 
Upvote 0
For example:
Acc4 is in Estimate but not in Budget
Acc8 is in Budget but not in Estimate

Is this possible?
  • Acc4 in Estimate, but not in Budget is possible. When doing budget for the year, it might be assumed that there will be no use for this account (Acc4). But then while months pass they notice that they have to use Acc4 also and there for there is given an estimate of money to be used in this year Estimate.xlsx.
  • Acc8 in Budget but not in Estimate is also possible, but that means that some cost center has budgeted to use money from account Acc8, but then when months pass they have not used any money from that account. There for when doing Estimate, it is estimated that this cost center is not using any money from account Acc8 and there for it doesn't appear in Estimate.
 
Upvote 0
Try:
VBA Code:
Sub GetData()
    Application.ScreenUpdating = False
    Dim lRow1 As Long, lRow2 As Long, desWB As Workbook, srcWS1 As Worksheet, srcWS2 As Worksheet, desWS As Worksheet, x As Long, lCol As Long, y As Long: y = 3
    Dim dic As Object, arr1 As Variant, arr2 As Variant, i As Long, CCname As Range, fnd As Range, fnd2 As Range
    Set desWB = Workbooks("Estimate.xlsx")
    Set srcWS1 = ThisWorkbook.Sheets("Sheet1")
    Set srcWS2 = desWB.Sheets("Sheet1")
    Set desWS = desWB.Sheets("Estimate")
    Set dic = CreateObject("Scripting.Dictionary")
    With srcWS1
        lRow1 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        arr1 = .Range("A4:A" & lRow1).Resize(, 2).Value
        For i = LBound(arr1) To UBound(arr1)
            If Not dic.exists(arr1(i, 1)) Then
                dic.Add arr1(i, 1), arr1(i, 2)
            End If
        Next i
    End With
    With srcWS2
        lRow2 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        arr2 = .Range("A4:A" & lRow2).Resize(, 2).Value
        For i = LBound(arr2) To UBound(arr2)
            If Not dic.exists(arr2(i, 1)) Then
                dic.Add arr2(i, 1), arr2(i, 2)
            End If
        Next i
    End With
    With desWS
        .Range("A1:A2") = Application.Transpose(Array("CCid", "CCname"))
        .Range("A4").Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.items))
        .Range("A4:B4").Resize(dic.Count).Sort Key1:=.Columns(1), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlNo
    End With
    For x = 3 To lCol Step 2
        With desWS
            .Cells(1, y).Resize(2, 3).Value = srcWS1.Cells(1, x).Resize(2).Value
            .Cells(3, y).Resize(, 3).Value = Array("Budget", "Estimate", "Result")
        End With
        y = y + 3
    Next x
    y = 3
    For Each CCname In desWS.Range("A4", desWS.Range("A" & Rows.Count).End(xlUp))
        Set fnd = srcWS1.Range("A:A").Find(CCname, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            For x = 3 To lCol Step 2
                With desWS
                    .Cells(CCname.Row, y) = srcWS1.Cells(fnd.Row, x)
                    .Cells(CCname.Row, y + 2) = srcWS1.Cells(fnd.Row, x + 1)
                    y = y + 3
                End With
            Next x
        End If
        y = 4
        Set fnd = srcWS2.Range("A:A").Find(CCname, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            For x = 3 To lCol Step 2
                With desWS
                    .Cells(CCname.Row, y) = srcWS2.Cells(fnd.Row, x)
                    y = y + 3
                End With
            Next x
        End If
        y = 3
    Next CCname
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
It works like magic. There is only one line that messed up order, but disabling it made it work like a champ. Thank you.

Line that I disabled was:
VBA Code:
.Range("A4:B4").Resize(dic.Count).Sort Key1:=.Columns(1), Order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlNo
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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