+ Reply to Thread
Results 1 to 13 of 13

Need to fill 8x12 table from a list of 96

  1. #1
    Registered User
    Join Date
    04-08-2021
    Location
    Tennessee
    MS-Off Ver
    Office 365
    Posts
    6

    Need to fill 8x12 table from a list of 96

    I have a need to build workbooks that will pull from a list of up to 96 items and fill that into an 8x12 table on the next tab. I have a way to copy and paste the values, but this takes forever and I build these workbooks frequently. I know there has to be a way to fill the table from top to bottom, left to right. I have played in Power Query, but I can't find a way to change the column dimensions. I have attached a sample workbook with 24 items and the table on the next tab. Thanks in advance.
    Attached Files Attached Files
    Last edited by AliGW; 04-08-2021 at 12:48 PM. Reason: SOLVED tag added - no need to edit the thread title!!!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,079

    Re: Need to fill 8x12 table from a list of 96

    As you have MS365, this should provide everything you need, I think: https://support.microsoft.com/en-us/...4-2eb78519ca90
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Need to fill 8x12 table from a list of 96

    In Plate, B3:

    =INDEX(SAMPLES!$A$2:$A$97,SEQUENCE(8,12))&""
    Glenn



  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: Need to fill 8x12 table from a list of 96

    You could use a formula like this in B3 and copy it down and across.

    =INDEX(SAMPLES!$A$2:$A$97,ROW()-2+(COLUMN()-2)*8)
    If posting code please use code tags, see here.

  5. #5
    Registered User
    Join Date
    04-08-2021
    Location
    Tennessee
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Need to fill 8x12 table from a list of 96

    This worked, but filled left to right, then top to bottom. I have to go top to bottom, then left to right. But I appreciate it very much!

  6. #6
    Registered User
    Join Date
    04-08-2021
    Location
    Tennessee
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Need to fill 8x12 table from a list of 96

    This worked! Thank you!

  7. #7
    Registered User
    Join Date
    04-08-2021
    Location
    Tennessee
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Need to fill 8x12 table from a list of 96

    This achieves the goal, but I need to fill top to bottom, then left to right. Thank you for pointing me to that function though. It will be useful in the future.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,079

    Re: Need to fill 8x12 table from a list of 96

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who helped.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,574

    Re: Need to fill 8x12 table from a list of 96

    The formula I posted goes from top to bottom, left to right.

    You could adapt Glenn's to do the same by adding TRANSPOSE and switching row/columns.

    =INDEX(SAMPLES!$A$2:$A$97,TRANSPOSE(SEQUENCE(12,8)))

  10. #10
    Registered User
    Join Date
    04-08-2021
    Location
    Tennessee
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Need to fill 8x12 table from a list of 96

    That is it right there! Perfect! You guys are the best!

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Need to fill 8x12 table from a list of 96

    You're welcome and thanks for the feedback.

  12. #12
    Registered User
    Join Date
    04-08-2021
    Location
    Tennessee
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Need to fill 8x12 table from a list of 96

    Got it working as it should!
    Last edited by kam5d; 04-08-2021 at 12:59 PM. Reason: Solved

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,079

    Re: Need to fill 8x12 table from a list of 96

    You can use COUNTIF:

    =COUNTIF(range,"<>0")

+ 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: 3
    Last Post: 09-11-2020, 01:59 PM
  2. [SOLVED] AUTO FILL TABLE if a specific sheet is selected from list box
    By fryon1031 in forum Excel General
    Replies: 4
    Last Post: 01-13-2015, 04:19 AM
  3. Replies: 3
    Last Post: 07-29-2014, 01:20 PM
  4. [SOLVED] Drop Down List to fill table from multiple tables
    By hidden78731 in forum Excel General
    Replies: 2
    Last Post: 05-10-2012, 12:12 PM
  5. Replies: 1
    Last Post: 04-12-2010, 04:29 PM
  6. Fill table using drop down list data selections
    By Lasthitlarry in forum Excel General
    Replies: 2
    Last Post: 07-10-2009, 06:03 PM
  7. code the following - down list to automatically fill table
    By blue_sky_70 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-12-2008, 09:21 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