+ Reply to Thread
Results 1 to 11 of 11

Display multiple tables with the same fields as one table

  1. #1
    Registered User
    Join Date
    06-03-2020
    Location
    Fairfield, NJ
    MS-Off Ver
    Office 365
    Posts
    5

    Display multiple tables with the same fields as one table

    Hello friends, and hope everyone is doing well,

    As you can see in the attachment, I have existing data formatted on the left side. I would like to display that data how it is on the right side.

    Also, what is the word for this operation? I'm having trouble searching because I can't quite identify what the process is called.

    Thank you greatly!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: Display multiple tables with the same fields as one table

    Hi
    your file is a csv text file??

  3. #3
    Registered User
    Join Date
    06-03-2020
    Location
    Fairfield, NJ
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Display multiple tables with the same fields as one table

    Hi Arthur,

    Thank you for your reply.

    I used a PowerShell command to generate the file, which outputs local users to a csv.

    Should I be working with xlsx?

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: Display multiple tables with the same fields as one table

    I have existing data formatted on the left side. I would like to display that data how it is on the right side.
    Well I only see a column with text separated by one or more commas? That does not really fit our description. A xlsx sheet would probably be better

  5. #5
    Registered User
    Join Date
    06-03-2020
    Location
    Fairfield, NJ
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Display multiple tables with the same fields as one table

    Of course, you are right. I did some delimiting on the original csv that I forgot about. Apologies. I re-uploaded as xlsx.

    Thank you for your help Arthur!
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,360

    Re: Display multiple tables with the same fields as one table

    It might help to understand the overall process or context this task is part of. A few thoughts that may or may not be part of working out a solution:

    1) You are extracting data from somewhere (OS information?) using Power Shell, and Power Shell is generating a single column of data in a text file. It appears that your end goal is to get this data compiled and stored into a nice data base that you can then analyze further. Did I guess right?
    2) Does Power Shell have any other output options other than the "single column text file" output? Some of this might be easier to do if you can get Power Shell to do it.
    3) Are you required to use Excel for this processing? A dedicated database app may have better tools for manipulating text files into a good data base. If you want to do this in Excel, Power Query/Get and Transform seems to be a good tool for working with data in text files and then shaping the data into a good database.

    I don't do database work, so I am not very helpful with specifics. Perhaps something in those thoughts will prompt some research and discussion that will help you find a good solution to your task.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Registered User
    Join Date
    06-03-2020
    Location
    Fairfield, NJ
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Display multiple tables with the same fields as one table

    Thanks, MrShorty.

    1. You are correct. I'm using a PowerShell command that exported that data as a csv and I would like it in a layout on the right.
    2. I have been looking into this exporting from PowerShell in a different format and will continue to do so. In case Excel is easier, I also wanted to explore that option.
    3. I'm not required to use Excel. I saw the Power Query/Get and Transform but am unsure how to use it. I'll have to slow down and take a good look.

    Any tips on how to use Power Query/Get and Transform to reformat those tables into one table would be great. I am looking into that now.

    Thank you!

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,360

    Re: Display multiple tables with the same fields as one table

    I am a non-user of Power Query, so I have no tips. An internet search for "power query manipulate text files" found:
    https://docs.microsoft.com/en-us/pow...y/combinefiles which should also provide a gateway into the rest of Microsoft's documentation on Power Query.
    Power Query uses an "M" language, and this should provide a gateway into that documentation: https://docs.microsoft.com/en-us/pow...text-functions
    A tutorial for beginners: https://powerspreadsheets.com/excel-...uery-tutorial/

    I know that other users are familiar with Power Query, so searching this forum may be useful.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,950

    Re: Display multiple tables with the same fields as one table

    Using your first download in Post #1
    Here is the Mcode from Power Query

    Please Login or Register  to view this content.
    Attached is the file for your review.

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    1
    Name FullName Disabled Description
    2
    username1 first1 last1 False user desc1
    3
    username2 first2 last2 False user desc2
    4
    username3 first3 last3 False user desc3
    5
    username4 first4 last4 False user desc4
    6
    username5 first5 last5 False user desc5
    Sheet: Sheet1


    In the attached file
    Click on any cell in the new table
    On the Data Tab, click on Queries & Connections
    In the right window, double click to open Query
    Review PQ steps
    M-code basics:
    - "let" is the start of a query
    - "in" is the closing of a query
    - each transformation step sits in between those 2 lines
    - each step line is ended by a comma, except the last one
    - "Source" is always the first step (Source Data)
    - After "in" you have the last step referenced
    Attached Files Attached Files
    Last edited by alansidman; 06-03-2020 at 03:18 PM.

  10. #10
    Registered User
    Join Date
    06-03-2020
    Location
    Fairfield, NJ
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Display multiple tables with the same fields as one table

    Alan, that is great. Thank you! I exported users in a different way but I am researching what you said. Thanks again for always sharing your wealth with us.

    Thank you MrShorty, Thank you Arthur!

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,950

    Re: Display multiple tables with the same fields as one table

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

    Also, as a relatively new member of the forum, 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 those who helped.

+ 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] Invoke Pivot Table by Coding and Display Fields Data
    By Quivolt in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-22-2017, 01:02 PM
  2. [SOLVED] Display Table via Dropdown. Vlookup across multiple tables.
    By DAE05 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-04-2017, 11:10 AM
  3. [SOLVED] Form to display other table fields associated with combo box then store those values...
    By southward in forum Access Tables & Databases
    Replies: 2
    Last Post: 12-02-2016, 10:33 PM
  4. pivot table display/formatting hide fields not being counted
    By oamrt in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-28-2012, 10:22 AM
  5. Linking Pivot tables - Multiple Page Fields
    By mettekr in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-22-2010, 09:43 AM
  6. Replies: 6
    Last Post: 10-11-2005, 05:05 PM
  7. Mapping Tables.Fields from a data source to my MS Access Table Structure
    By Mark Roach in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-09-2005, 06:07 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