Build new table from existing table
Results 1 to 4 of 4

Thread: Build new table from existing table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2007
    Location
    NJ, USA
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Build new table from existing table

    Hiyas,

    I have a worksheet ("Plugins") that contains a table ("tblPlugins") with one of the columns ("Installed") having data validation that contains Yes/No. I'm trying to build a new table in a separate worksheet based on the condition that if the column ("Installed") has a value of Yes, that the data should exist in the new table.

    Is there a way to do this?

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,954
    Post Thanks / Like
    Mentioned
    80 Post(s)
    Tagged
    18 Thread(s)

    Default Re: Build new table from existing table

    See if this is what you want.

    Sheet 'Plugins' with "tblPlugins"

    Plugins

    ABC
    1IDInstalledAmount
    2ID1No1
    3ID2 2
    4ID3Yes3
    5ID4Yes4
    6ID5Pending5
    7ID6Yes6


    Excel tables to the web >> Excel Jeanie HTML 4


    'Other' sheet with second table, "tblYes". Formula in A2 is copied across and down

    Other

    ABC
    1IDInstalledAmount
    2ID3Yes3
    3ID4Yes4
    4ID6Yes6
    5
    6
    7

    Spreadsheet Formulas
    CellFormula
    A2=IFERROR(INDEX(tblPlugins[ID],AGGREGATE(15,6,(ROW(tblPlugins[ID])-ROW(tblPlugins[[#Headers],[ID]]))/(tblPlugins[[Installed]:[Installed]]="Yes"),ROWS(A$2:A2))),"")


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    Board Regular
    Join Date
    Aug 2007
    Location
    NJ, USA
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Build new table from existing table

    Thank you so much! I'm still running into a bit of an issue. Ideally, I was hoping for the output to be an actual table as opposed to a list or range but, I'm not sure that's possible. This solution does work for the most part but has some quirks but I think it's because I wasn't entirely clear on the data.

    Thanks again, I really appreciate your help with this one!

    Excel 2010
    ABCDEFGH
    1NameURLDependenciesDescription Cost Server1Server2Server3
    2Plugin 1www.plugin1url.comTestThis is a description $ 10.00 Yes
    3Plugin 2www.plugin2url.comTestSo is this $ 5.00
    4Plugin 3www.plugin3url.comThis one tooYes
    5Plugin 4www.plugin4url.comTestAgain, this one $ 3.00
    6Plugin 5www.plugin5url.comLastly, so is thisYes

    tblPlugins



    Current Output - The problem I'm running into here is blank dependency entries appear as a 0. I'm trying to leave them as blank entries. Additionally, when an entry has no cost, I want to leave it as a blank entry as well.

    Excel 2010
    ABCDE
    1NameURLDependenciesDescription Cost
    2Plugin 1www.plugin1url.comTestThis is a description $ 10.00
    3Plugin 3www.plugin3url.com0This one too $ -
    4Plugin 5www.plugin5url.com0Lastly, so is this $ -

    Server1



    Current Output

    Excel 2010
    ABCDE
    1NameURLDependenciesDescription Cost
    2Plugin 1www.plugin1url.comTestThis is a description $ 10.00
    3Plugin 3www.plugin3url.comThis one too
    4Plugin 5www.plugin5url.comLastly, so is this

    Desired Output



    Worksheet Formulas
    CellFormula
    A2=IFERROR(INDEX(tblPlugins[Name],AGGREGATE(15,6,(ROW(tblPlugins[Name])-ROW(tblPlugins[[#Headers],[Name]]))/(tblPlugins[[Server1]:[Server1]]="Yes"),ROWS(A$2:A2))),"")

    Last edited by reaktorblue; May 22nd, 2018 at 07:20 AM.

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,954
    Post Thanks / Like
    Mentioned
    80 Post(s)
    Tagged
    18 Thread(s)

    Default Re: Build new table from existing table

    Quote Originally Posted by reaktorblue View Post
    Ideally, I was hoping for the output to be an actual table as opposed to a list or range ...
    Mine are tables, it is just that Excel jeanie does not show the standard banded table formatting. However, a formula cannot create a table. I created a blank table with ample rows on sheet Server1 below before populating the formulas.

    A formula like I have provided that collects data from another table also will not create a hyperlink or copy the currency formatting from the 'Cost' column, you will need to do that formatting in the Server1 table yourself.


    Quote Originally Posted by reaktorblue View Post
    The problem I'm running into here is blank dependency entries appear as a 0. I'm trying to leave them as blank entries. Additionally, when an entry has no cost, I want to leave it as a blank entry as well.>
    That requires a bit more work from the formula but can be achieved as shown below.

    tblPlugins

    ABCDEFGH
    1NameURLDependenciesDescriptionCostServer1Server2Server3
    2Plugin 1www.plugin1url.comTestThis is a description$10.00 Yes
    3Plugin 2www.plugin2url.comTestSo is this$5.00
    4Plugin 3www.plugin3url.com This one too Yes
    5Plugin 4www.plugin4url.comTestAgain, this one$3.00
    6Plugin 5www.plugin5url.com Lastly, so is this Yes


    Excel tables to the web >> Excel Jeanie HTML 4


    Server1

    ABCDE
    1NameURLDependenciesDescriptionCost
    2Plugin 1www.plugin1url.comTestThis is a description$10.00
    3Plugin 3www.plugin3url.com This one too
    4Plugin 5www.plugin5url.com Lastly, so is this
    5
    6
    7
    8
    9
    10

    Spreadsheet Formulas
    CellFormula
    A2=IFERROR(IF(INDEX(tblPlugins[Name],AGGREGATE(15,6,(ROW(tblPlugins[Name])-ROW(tblPlugins[[#Headers],[Name]]))/(tblPlugins[[Server1]:[Server1]]="Yes"),ROWS(A$2:A2)))="","",INDEX(tblPlugins[Name],AGGREGATE(15,6,(ROW(tblPlugins[Name])-ROW(tblPlugins[[#Headers],[Name]]))/(tblPlugins[[Server1]:[Server1]]="Yes"),ROWS(A$2:A2)))),"")


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; May 22nd, 2018 at 08:01 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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