# Consolidate Columns from Individual Sheets into One - issue with duplication

#### GeeWhiz7

##### Board Regular
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.

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

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!!

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

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?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
LastColumn = LastColumn + 1
End If
Next ws
Application.ScreenUpdating = True
End Sub``````

Happy Holidays!

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
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!

"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

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``````

"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

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.

"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

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

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:

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

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?

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.

Replies
3
Views
350
Replies
0
Views
162
Replies
5
Views
153
Replies
1
Views
332
Replies
3
Views
1K

1,203,070
Messages
6,053,364
Members
444,657
Latest member
jessejames1of3

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

### Which adblocker are you using?

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

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