+ Reply to Thread
Results 1 to 12 of 12

Convert Horizontal data to Vertical - loop through files in folder

  1. #1
    Registered User
    Join Date
    04-07-2014
    Location
    n/a
    MS-Off Ver
    Excel 2010
    Posts
    13

    Convert Horizontal data to Vertical - loop through files in folder

    I have a set of workbooks-all located in the same folder-in which data are shown "horizontally"

    i.e. file 1 - sheet1 looks more or less like this:

    1 2 3
    4 5 6

    same with the other files.

    what I am trying to achieve is that in another woorkbook, all the data are copied in one vertical vector

    i.e

    1
    2
    3
    4
    5
    6 from file 1 and then
    1
    2
    3
    4
    5
    6 from file 2

    I can seem to be able to resize the array to the correct size but the only values copied are for the last file. in the example that I have above, the result would look something like:

    <empty>
    <empty>
    <empty>
    <empty>
    <empty>
    <empty>
    1
    2
    3
    4
    5
    6

    this is the code I have so far:

    Sub Consolidate()

    Dim a As Workbook
    Dim b As Workbook
    Dim x, y(), i&, j&, k&
    Dim myPath As String
    Dim filename As String
    Dim wb As Workbook
    Dim Fnum&


    Set b = ThisWorkbook

    myPath = "C:\Data"

    If Right(myPath, 1) <> "\" Then myPath = myPath + "\"

    filename = Dir(myPath & "*.xl*")

    Fnum = 0
    k = 0

    Do While filename <> ""

    Fnum = Fnum + 1
    Application.ScreenUpdating = False

    Set wb = Workbooks.Open(myPath & filename)

    x = wb.Sheets("Sheet1").Range("B3").CurrentRegion.Value

    ReDim y(1 To UBound(x, 1) * UBound(x, 2) * Fnum, 1 To 3)
    For i = 2 To UBound(x, 1)
    For j = 2 To UBound(x, 2)
    k = k + 1
    y(k, 1) = x(i, 1)
    y(k, 2) = x(i, j)
    y(k, 3) = x(1, j)
    Next j
    Next i

    filename = Dir
    wb.Close SaveChanges:=False

    Loop

    With b.Sheets("Consolidate")
    .Range("A2").Resize(k, 3) = y()
    .Columns.AutoFit
    .Activate

    End With

    Application.ScreenUpdating = True

    End Sub
    I'd appreciate some help on solving this.

    Thanks

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Convert Horizontal data to Vertical - loop through files in folder

    If you want to convert all columns in to single column, I would use these lines instead.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-07-2014
    Location
    n/a
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Convert Horizontal data to Vertical - loop through files in folder

    Thank you for your reply. I should have explained a little bit better.

    Data in the source files are on 3 rows. In each of the files, column A is Labels and row 1 is years (a1 is empty) - I attach an example.

    The problem that I have is that the final array is only populated with data from the last source file in the folder.

    Thanks,
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Convert Horizontal data to Vertical - loop through files in folder

    This works. If you want to exclude a blank cell, you need to delete (Comment) that line(If Len(x(i, j)) Then.

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Convert Horizontal data to Vertical - loop through files in folder

    Hmm!
    This line is the culprit.
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-07-2014
    Location
    n/a
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Convert Horizontal data to Vertical - loop through files in folder

    still with the same problem. values copied are only those from last data file. I am realizing now it is something to do with the fact that i am not using a ReDim Preserve on y(), which I cannot actually do because y() is multidimensional. I'll try to find a workaround there - thank you for your help!

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Convert Horizontal data to Vertical - loop through files in folder

    No, the redim is correct.
    I did not look at your code in details, but 2 issues may need to look at

    k = 0

    Get rid of this line.
    Do you a sheet1 in each workbook opened?

    x = wb.Sheets("Sheet1").Range("B3").CurrentRegion.Value
    How many sheets do you have in each work book?

  8. #8
    Registered User
    Join Date
    04-07-2014
    Location
    n/a
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Convert Horizontal data to Vertical - loop through files in folder

    Correct, 1 sheet per workbook and that sheet is always called "sheet1"

  9. #9
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Convert Horizontal data to Vertical - loop through files in folder

    Okay, give me a couple of minutes for testing.

  10. #10
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Convert Horizontal data to Vertical - loop through files in folder

    Okay!
    If you want to loop first in all books, load the array in to Y, then when you close the loop, you can load the result in sheet consolidation. This requires persevering the redim.
    Here is anther alternative which does not require preserve the redim.

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-07-2014
    Location
    n/a
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Convert Horizontal data to Vertical - loop through files in folder

    Thank you! The solution you provided works perfectly!

  12. #12
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Convert Horizontal data to Vertical - loop through files in folder

    The preserve code in module 2 seems to work on my test. Personally, I do not use redimpreserve
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Convert Data Vertical to Horizontal
    By dextryn in forum Excel General
    Replies: 2
    Last Post: 02-19-2013, 07:27 PM
  2. convert data horizontal to vertical
    By vorabha in forum Excel General
    Replies: 8
    Last Post: 02-10-2013, 03:53 PM
  3. convert vertical data to horizontal
    By syuk225 in forum Excel General
    Replies: 3
    Last Post: 06-14-2012, 03:58 AM
  4. Convert data From Vertical to Horizontal Format
    By reyrey in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-20-2011, 03:52 PM
  5. [SOLVED] Convert Vertical row data into Horizontal
    By ajang in forum Excel General
    Replies: 8
    Last Post: 11-01-2010, 01:28 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1