+ Reply to Thread
Results 1 to 12 of 12

Help! Create Large Data List from two worksheets

  1. #1
    Registered User
    Join Date
    01-09-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    8

    Question Help! Create Large Data List from two worksheets

    Hello,

    I have a worksheet with 5 columns (ID, Cost Center, GL Account, GL Account Desc, and Action) and 337 rows, three of the columns are static (ID, Cost Center, and Action) and have the same data in all 337 rows.

    I have another worksheet with 1 column (Cost Center) and 109 rows of unique values.

    I need to take the Cost Center value from the second worksheet and place it in the Cost Center field of the first worksheet. The way it would look is one cost center would be listed 337 times because there are 337 unique GL Codes, that need to be linked to that one cost center.

    The worksheet would grow 337 rows multiplied by the unique 109 cost centers.

    I don't know how to do this or if it can be done in Excel. Please help.
    Attached Files Attached Files
    Last edited by mauricioaglr; 01-09-2014 at 03:49 PM.

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Help! Create Large Data List from two worksheets

    Welcome to the board!

    So let me see if I understand. The data on GLUpdate would be duplicated except for the CostCenterID, Each group of 337 would have a different CostCenterID from Cost Centers Sheet?
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    01-09-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Help! Create Large Data List from two worksheets

    Quote Originally Posted by Tinbendr View Post
    Welcome to the board!

    So let me see if I understand. The data on GLUpdate would be duplicated except for the CostCenterID, Each group of 337 would have a different CostCenterID from Cost Centers Sheet?
    Yes, that is correct. Its a lot of work if I have to copy and paste

  4. #4
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Help! Create Large Data List from two worksheets

    If you update the accounts and the sheet name changes, you'll have to adjust the sheet name in the code. Or you can change it to Worksheets(1) to be more generic.

    This creates a new worksheet with all the new data.

    This is also dynamic, so if the accounts or the cost centers increase or decrease, it'll account for that.

    Hope this helps.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-09-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Help! Create Large Data List from two worksheets

    Quote Originally Posted by Tinbendr View Post
    If you update the accounts and the sheet name changes, you'll have to adjust the sheet name in the code. Or you can change it to Worksheets(1) to be more generic.

    This creates a new worksheet with all the new data.

    This is also dynamic, so if the accounts or the cost centers increase or decrease, it'll account for that.

    Hope this helps.

    Please Login or Register  to view this content.
    Where do I enter this code in Excel 2010?

  6. #6
    Registered User
    Join Date
    01-09-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Help! Create Large Data List from two worksheets

    Quote Originally Posted by mauricioaglr View Post
    Where do I enter this code in Excel 2010?
    Never mind I figured out how to enable the developer option and create the macro.

  7. #7
    Registered User
    Join Date
    01-09-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Help! Create Large Data List from two worksheets

    GLMacro.xlsxHello Tinbendr,

    I had to make a change to the second worksheet, see attached, I added a second column from the first worksheet "Buying Entity ID (COID)" I need to do the samething, add the 320 so GL codes to each of the two columns from the second worksheet. Please help

    Sub BreakOut()
    Dim WSAccts As Worksheet
    Dim WSsrc As Worksheet
    Dim WSdest As Worksheet
    Dim AryAccts() As Variant
    Dim AryCC As Variant
    Dim AryFinal As Variant
    Dim A As Long
    Dim B As Long
    Dim C As Long
    Dim LRAccts As Long
    Dim LRCC As Long

    Set WSAccts = Worksheets("GLUpdate")
    Set WSsrc = Worksheets("COID-CC")
    Set WSdest = Worksheets.Add

    With WSAccts
    LRAccts = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    With WSsrc
    LRCC = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    ReDim AryCC(LRCC)
    ReDim AryAccts(1 To LRAccts, 1 To 5)
    ReDim AryFinal(1 To LRAccts * LRCC, 1 To 5)

    AryAccts = WSAccts.Range("A2:E" & LRAccts)
    AryCC = WSsrc.Range("A2:A" & LRCC)

    WSdest.Range("A1:E1").Value = Sheet1.Range("A1:E1").Value

    For B = 1 To LRCC - 1
    For A = 1 To LRAccts - 1
    C = C + 1
    AryFinal(C, 1) = AryAccts(A, 1)
    AryFinal(C, 2) = AryCC(B, 1)
    AryFinal(C, 3) = AryAccts(A, 3)
    AryFinal(C, 4) = AryAccts(A, 4)
    AryFinal(C, 5) = AryAccts(A, 5)
    Next
    Next

    WSdest.Range("A2").Resize(LRAccts * LRCC, 5) = AryFinal

    End Sub

  8. #8
    Registered User
    Join Date
    01-09-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Help! Create Large Data List from two worksheets

    Quote Originally Posted by mauricioaglr View Post
    Attachment 291795Hello Tinbendr,

    I had to make a change to the second worksheet, see attached, I added a second column from the first worksheet "Buying Entity ID (COID)" I need to do the samething, add the 320 so GL codes to each of the two columns from the second worksheet. Please help

    Sub BreakOut()
    Dim WSAccts As Worksheet
    Dim WSsrc As Worksheet
    Dim WSdest As Worksheet
    Dim AryAccts() As Variant
    Dim AryCC As Variant
    Dim AryFinal As Variant
    Dim A As Long
    Dim B As Long
    Dim C As Long
    Dim LRAccts As Long
    Dim LRCC As Long

    Set WSAccts = Worksheets("GLUpdate")
    Set WSsrc = Worksheets("COID-CC")
    Set WSdest = Worksheets.Add

    With WSAccts
    LRAccts = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    With WSsrc
    LRCC = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    ReDim AryCC(LRCC)
    ReDim AryAccts(1 To LRAccts, 1 To 5)
    ReDim AryFinal(1 To LRAccts * LRCC, 1 To 5)

    AryAccts = WSAccts.Range("A2:E" & LRAccts)
    AryCC = WSsrc.Range("A2:A" & LRCC)

    WSdest.Range("A1:E1").Value = Sheet1.Range("A1:E1").Value

    For B = 1 To LRCC - 1
    For A = 1 To LRAccts - 1
    C = C + 1
    AryFinal(C, 1) = AryAccts(A, 1)
    AryFinal(C, 2) = AryCC(B, 1)
    AryFinal(C, 3) = AryAccts(A, 3)
    AryFinal(C, 4) = AryAccts(A, 4)
    AryFinal(C, 5) = AryAccts(A, 5)
    Next
    Next

    WSdest.Range("A2").Resize(LRAccts * LRCC, 5) = AryFinal

    End Sub
    Never mind I figured out, thank you for your help.

  9. #9
    Registered User
    Join Date
    01-09-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Help! Create Large Data List from two worksheets

    Question, what code can I add to the macro to copy the cell type for example to make column 2 (cost center) on the new sheet a text field?

  10. #10
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Help! Create Large Data List from two worksheets

    Please Login or Register  to view this content.
    Please use code tags around any code you post. It's the hash symbol. It makes reading the code so much easier.

    When you post a workbook that has code already provided, at least copy that code into the revised worksheet.

    Glad we got it working.

  11. #11
    Registered User
    Join Date
    01-09-2014
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Help! Create Large Data List from two worksheets

    Thank you. Where in the code do I place that line?

  12. #12
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Help! Create Large Data List from two worksheets

    Just before the End Sub

+ 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. Replies: 2
    Last Post: 05-06-2013, 08:53 AM
  2. Create New Worksheets based on one column in a Large Dataset
    By shaolinsnake in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2012, 06:29 PM
  3. Macro to create worksheets and transport data from Main list
    By LMoir in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-11-2012, 12:03 PM
  4. Create master list from several worksheets of data
    By rajsa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-05-2009, 11:33 AM
  5. [SOLVED] How do I create a list from a large spreadsheet without headings
    By HayRad in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-09-2005, 11:45 PM

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