Consolidate Columns from Individual Sheets into One - issue with duplication

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
199
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi guys,
Yes, day before Christmas but was on the computer anyway...BTW Merry Christmas to whomever answers later!

I have 50 worksheets, one each for a stock ticker. In Column E (with header) is the adjusted close prices.
I have written some code to consolidate each tickers adj. close price into one sheet called Adjusted Close Prices.
Everything works great, except when one of the individual sheets is for a continuous future ticker symbol (like CL=F). For some reason, this causes the aggregation to make a duplicate and the rest to be blank.

So, I'm thinking someone smarter than me on the matter might have a better suggestion to grab column E from each individual worksheet and place it consecutively in a column on a summary Adjusted Close Price sheet.

What I have...
ACP = Summary Sheet and the other 3 are continuous futures symbols.
1640384318740.png


In the CL=F sheet for example, here is the data...
1640384396496.png


I would like to take the Adj Close column from each individual worksheet <> "Adjusted Close Price" and copy it into the Adjusted Close Price sheet in the next column for each.
I can do this with stock symbols no problem, but when I use futures (with an equals = sign) it only puts the first one.

Example with only stocks...it works...er no it doesn't. I just noticed it makes a duplicate too...insert curse word!!
1640384669900.png


Example ACP sheet with only futures...it only works with the first one and then for some reason duplicates it (CL=F, CL=F)
1640384562519.png


I suspect it has something to do with my vlookup and I have messed with it long enough so I was hoping to start from scratch with a better way.
How can I easily get column E from each individual worksheet copied consecutively into a summary Adjusted Close Price sheet no matter what the name?

Thanks in advance!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,784
Office Version
  1. 2010
Platform
  1. Windows
You might consider the following...

VBA Code:
Sub AnotherSummary()
Dim ws As Worksheet
Dim LastColumn As Long

Application.ScreenUpdating = False
LastColumn = Sheets("Adjusted Close Price").Cells(1, Columns.Count).End(xlToLeft).Column
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Adjusted Close Price" Then
        ws.Columns(5).Copy Destination:=Sheets("Adjusted Close Price").Columns(LastColumn)
        LastColumn = LastColumn + 1
    End If
Next ws
Application.ScreenUpdating = True
End Sub

Happy Holidays!
 

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
199
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You might consider the following...

VBA Code:
Sub AnotherSummary()
Dim ws As Worksheet
Dim LastColumn As Long

Application.ScreenUpdating = False
LastColumn = Sheets("Adjusted Close Price").Cells(1, Columns.Count).End(xlToLeft).Column
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Adjusted Close Price" Then
        ws.Columns(5).Copy Destination:=Sheets("Adjusted Close Price").Columns(LastColumn)
        LastColumn = LastColumn + 1
    End If
Next ws
Application.ScreenUpdating = True
End Sub

Happy Holidays!
Thank you Tonyyy, this suggestion is more concise than what I am doing So I’m going to try to adapt it. The only catch and I forgot to mention it in the original post, is on some occasions the Dates (A2:A) in each individual sheet are not always the same as each other in other sheets so in order to line up the dates I was using a vlookup.

Steps:
- grab individual data to individual sheets
- clear summary sheet (ACP)
- grab dates from first individual sheet to the ACP
- vlookup individual sheets against dates now on ACP sheet and return the columns accordingly to ACP.

I think I can put the vlookup inside your sub so maybe I’ll give that a try and post what works if it works.
thank you and Happy Holidays!
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,784
Office Version
  1. 2010
Platform
  1. Windows
"The only catch and I forgot to mention it in the original post, is on some occasions the Dates (A2:A) in each individual sheet are not always the same as each other in other sheets..."

Yeah, that's a pretty big exclusion. You might try this instead...

VBA Code:
Sub AnotherSummary2()
Dim kount As Long, i As Long, j As Long, k As Long
Dim arr1 As Variant, arr2 As Variant, arr3 As Variant

Sheets("Adjusted Close Price").Cells.Clear
kount = ThisWorkbook.Sheets.Count

''''Get dates from first sheet
For i = 1 To kount
    If Sheets(i).Name <> "Adjusted Close Price" Then
        arr1 = Sheets(i).UsedRange.Columns(1)
        ReDim arr3(1 To UBound(arr1), 1 To 51)
        For j = 1 To UBound(arr1)
            arr3(j, 1) = arr1(j, 1)
        Next j
        Exit For
    End If
Next i

''''Match dates and get prices from all sheets
For i = 1 To kount
    If Sheets(i).Name <> "Adjusted Close Price" Then
        arr2 = Sheets(i).UsedRange
        arr3(1, i) = arr2(1, 5)
        For j = 2 To UBound(arr1)
            For k = 2 To UBound(arr2)
                If arr1(j, 1) = arr2(k, 1) Then arr3(j, i) = arr2(k, 5)
            Next k
        Next j
    End If
Next i

Sheets("Adjusted Close Price").Range("A1:AY" & UBound(arr1)) = arr3
End Sub
 
Solution

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
199
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
"The only catch and I forgot to mention it in the original post, is on some occasions the Dates (A2:A) in each individual sheet are not always the same as each other in other sheets..."

Yeah, that's a pretty big exclusion. You might try this instead...

VBA Code:
Sub AnotherSummary2()
Dim kount As Long, i As Long, j As Long, k As Long
Dim arr1 As Variant, arr2 As Variant, arr3 As Variant

Sheets("Adjusted Close Price").Cells.Clear
kount = ThisWorkbook.Sheets.Count

''''Get dates from first sheet
For i = 1 To kount
    If Sheets(i).Name <> "Adjusted Close Price" Then
        arr1 = Sheets(i).UsedRange.Columns(1)
        ReDim arr3(1 To UBound(arr1), 1 To 51)
        For j = 1 To UBound(arr1)
            arr3(j, 1) = arr1(j, 1)
        Next j
        Exit For
    End If
Next i

''''Match dates and get prices from all sheets
For i = 1 To kount
    If Sheets(i).Name <> "Adjusted Close Price" Then
        arr2 = Sheets(i).UsedRange
        arr3(1, i) = arr2(1, 5)
        For j = 2 To UBound(arr1)
            For k = 2 To UBound(arr2)
                If arr1(j, 1) = arr2(k, 1) Then arr3(j, i) = arr2(k, 5)
            Next k
        Next j
    End If
Next i

Sheets("Adjusted Close Price").Range("A1:AY" & UBound(arr1)) = arr3
End Sub
Whew. you’re pushing my ability to read the syntax for the arrays so I will have to put into vba and step through it. many thanks for taking the time and helping me…will revert once I get back to the computer.
 

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
199
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
"The only catch and I forgot to mention it in the original post, is on some occasions the Dates (A2:A) in each individual sheet are not always the same as each other in other sheets..."

Yeah, that's a pretty big exclusion. You might try this instead...

VBA Code:
Sub AnotherSummary2()
Dim kount As Long, i As Long, j As Long, k As Long
Dim arr1 As Variant, arr2 As Variant, arr3 As Variant

Sheets("Adjusted Close Price").Cells.Clear
kount = ThisWorkbook.Sheets.Count

''''Get dates from first sheet
For i = 1 To kount
    If Sheets(i).Name <> "Adjusted Close Price" Then
        arr1 = Sheets(i).UsedRange.Columns(1)
        ReDim arr3(1 To UBound(arr1), 1 To 51)
        For j = 1 To UBound(arr1)
            arr3(j, 1) = arr1(j, 1)
        Next j
        Exit For
    End If
Next i

''''Match dates and get prices from all sheets
For i = 1 To kount
    If Sheets(i).Name <> "Adjusted Close Price" Then
        arr2 = Sheets(i).UsedRange
        arr3(1, i) = arr2(1, 5)
        For j = 2 To UBound(arr1)
            For k = 2 To UBound(arr2)
                If arr1(j, 1) = arr2(k, 1) Then arr3(j, i) = arr2(k, 5)
            Next k
        Next j
    End If
Next i

Sheets("Adjusted Close Price").Range("A1:AY" & UBound(arr1)) = arr3
End Sub
Tonyyy, this works quite nicely and is cleaner than my vlookup for sure. Thank you for the solution.

I did some stepping through your code to try to understand it better...can't say I'm very good at following the logic fully. Somehow I have managed to accidentally insert/leave a blank column in the resulting aggregation and I'm not quite sure where to fix it. Here is the slightly modified code and below that snaps of one of the individual sheets and the summary sheet if you can spot where I'm mixing things up...

VBA Code:
Sub AnotherSummary2()
Dim kount As Long, i As Long, j As Long, k As Long
Dim arr1 As Variant, arr2 As Variant, arr3 As Variant

Sheets("Adjusted Close Price").Cells.Clear
kount = ThisWorkbook.Sheets.count

''''Get dates from first sheet
For i = 1 To kount
    If Sheets(i).Name <> "Adjusted Close Price" _
    And Sheets(i).Name <> "Start Here" _
    Then
        arr1 = Sheets(i).UsedRange.Columns(1)
        ReDim arr3(1 To UBound(arr1), 1 To 51)
        For j = 1 To UBound(arr1)
            arr3(j, 1) = arr1(j, 1)
        Next j
        Exit For
    End If
Next i

''''Match dates and get prices from all sheets
''''Set the second value in arr2 to equal the column desired 6 = adj close
For i = 1 To kount
    If Sheets(i).Name <> "Adjusted Close Price" _
    And Sheets(i).Name <> "Start Here" _
    Then
        arr2 = Sheets(i).UsedRange
        arr3(1, i) = arr2(1, 6)
        For j = 2 To UBound(arr1)
            For k = 2 To UBound(arr2)
                If arr1(j, 1) = arr2(k, 1) Then arr3(j, i) = arr2(k, 6)
            Next k
        Next j
    End If
Next i

Sheets("Adjusted Close Price").Range("A1:AY" & UBound(arr1)) = arr3
End Sub

Individual sheet:
1640623501288.png

Summary sheet (ACP): somehow I've added a blank
1640623547398.png
 

tonyyy

Well-known Member
Joined
Jun 24, 2015
Messages
1,784
Office Version
  1. 2010
Platform
  1. Windows
By including the "Start Here" sheet, the variable "i" iterates another cycle before finding the data sheets and thus leaves the blank column. You can tweak the code by subtracting 1 from i as below...

VBA Code:
        arr3(1, i - 1) = arr2(1, 6)
        For j = 2 To UBound(arr1)
            For k = 2 To UBound(arr2)
                If arr1(j, 1) = arr2(k, 1) Then arr3(j, i - 1) = arr2(k, 6)

Curiously... how are you going to distinguish stock tickers when all the headers are labeled "Adj Close"?

And again curiously, what do you plan to do with the data?
 

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
199
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
By including the "Start Here" sheet, the variable "i" iterates another cycle before finding the data sheets and thus leaves the blank column. You can tweak the code by subtracting 1 from i as below...

VBA Code:
        arr3(1, i - 1) = arr2(1, 6)
        For j = 2 To UBound(arr1)
            For k = 2 To UBound(arr2)
                If arr1(j, 1) = arr2(k, 1) Then arr3(j, i - 1) = arr2(k, 6)

Curiously... how are you going to distinguish stock tickers when all the headers are labeled "Adj Close"?

And again curiously, what do you plan to do with the data?
I figured it had something to do with the start here sheet, but with all the i,j,k increments I think I got lost.

On the names, I was either going to get the sheet name and add (&) it to the second loop arr3 when the header in 1, x gets assigned from column 6 to get something like “XOM AdjClose” Or leave it as is since I pass the data to another workbook anyway.

Right now I’m toying around with some back testing/walk forward of simple strategies and I needed the data from the past in a clean and uniform format and was tired of manually fixing things. I could make it work for equities but something in my code was unhappy when I used Futures symbols with = sign in it. Your code fixes that nicely and allows me to look at continuous futures.
 

Forum statistics

Threads
1,175,524
Messages
5,897,920
Members
434,687
Latest member
alybazaza

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
Top