+ Reply to Thread
Results 1 to 6 of 6

Output from stats software in awkward wide format

  1. #1
    Registered User
    Join Date
    01-19-2022
    Location
    Ireland
    MS-Off Ver
    365
    Posts
    3

    Output from stats software in awkward wide format

    Hi,

    I use stata for my analysis and use the outreg2 add-on for converting my results to a format I can easily copy and paste into my word doc tables. This is fine for multivariate analysis but I'm creating large univariate tables and the program adds each new univariate result into a new column rather than immediately below the last column. Is there a quick way for me to move all the results into the first four columns? I don't need the #observations in the bottom row, just the coefficient and the confidence intervals.

    I would just go the long way and copy and paste each item but I have many tables like this and 95 rows in each so was hoping for a quick fix I can apply to all.

    Thanks in advance,

    Laura
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,302

    Re: Output from stats software in awkward wide format

    Hello statalaura and Welcome to Excel Forum.
    I am not sure that I understand, however I feel that you would like something similar to the arrangement shown in columns HL:HQ of the attached file.
    The formula used is: =IFERROR(INDEX($A5:$HI5,AGGREGATE(15,6,COLUMN($A5:$HI5)/($A5:$HI5<>"")/($A$3:$HI$3=HL$3),COUNTIFS($HL$3:HL$3,HL$3))),"")
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    01-19-2022
    Location
    Ireland
    MS-Off Ver
    365
    Posts
    3

    Re: Output from stats software in awkward wide format

    Hi JeteMc,

    Thank you so much! That was exactly what I wanted. I do have one more question if you don't mind. I've just been trying to apply that formula to another table but the data aren't showing up. Been playing around with it for a while now and can't figure it out.


    Maybe I am making incorrect assumptions about the components of it.

    Basically, the formula shows correctly in the formula bar, and when 'show formulas' is toggled but the cell is blank when 'show formulas' is off.

    Laura

    edit: this was the formula I used
    =IFERROR(INDEX($A8:$GW8,AGGREGATE(15,6,COLUMN($A8:$GW8)/($A8:$GW8<>"")/($A$3:$GW$3=GY$3),COUNTIFS($GY$3:GZ$3,HA$3))),"")
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,302

    Re: Output from stats software in awkward wide format

    Put the column headers in row 3 i.e. Normal Normal Borderline Borderline Abnormal Abnormal
    The countifs at the end of the formula needs them to place the measurement and the range into the correct columns
    =IFERROR(INDEX($A8:$GW8,AGGREGATE(15,6,COLUMN($A8:$GW8)/($A8:$GW8<>"")/($A$3:$GW$3=GY$3),COUNTIFS($GY$3:GY$3,GY$3))),"")
    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-19-2022
    Location
    Ireland
    MS-Off Ver
    365
    Posts
    3

    Re: Output from stats software in awkward wide format

    Hi JeteMc,

    Sorry for the delay in replying this week, I'm just getting back to this now. That clarification has been really helpful. I was able to apply the formula to a new file successfully. Thank you so much for this. It's saved me a lot of time and I learned something on top of it!

    Laura

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,302

    Re: Output from stats software in awkward wide format

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Reshaping data (wide format to long format)
    By souryadas in forum Excel General
    Replies: 2
    Last Post: 11-15-2019, 04:59 AM
  2. Worksheet Wide Conditional Format
    By ajocius in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2018, 10:26 AM
  3. [SOLVED] about restructure repeated measure data into long format from wide format
    By sohel_for in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-18-2015, 01:10 AM
  4. [SOLVED] Flattening A File - Awkward Data Output
    By vichisov in forum Excel General
    Replies: 6
    Last Post: 01-22-2015, 02:45 PM
  5. Rearranging data to wide format
    By erikabee in forum Excel General
    Replies: 5
    Last Post: 10-21-2014, 07:47 PM
  6. [SOLVED] Formula to convert awkward date format
    By dchubbock in forum Excel Formulas & Functions
    Replies: 29
    Last Post: 11-27-2013, 02:02 PM
  7. transforming a long dataset into a wide format
    By dmunte in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-30-2013, 11:42 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