+ Reply to Thread
Results 1 to 11 of 11

Reorganizing table; parsing, text file based

  1. #1
    Registered User
    Join Date
    11-10-2020
    Location
    World
    MS-Off Ver
    365
    Posts
    8

    Reorganizing table; parsing, text file based

    Hello;

    Based on some log output, I would need to reorganize it to have some better position of data.

    To make it simple, I do have something like:

    Please Login or Register  to view this content.
    All data in one single column. There's possibility to have one, more or even none "details" per Row.

    Is there a possibility to, using available functions in Excel, to get something like this:

    Please Login or Register  to view this content.
    Table should have two columns, first column with Row info, second column Details info.

    Anyone has suggestions?

    Thanks!

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

    Re: Reorganizing table; parsing, text file based

    Hello and Welcome to Excel Forum.
    This proposal employs two helper columns (B:C) which may be moved and/or hidden for aesthetic purposes.
    Column B is populated using: =IF(ISNUMBER(SEARCH("row",A2)),A2,B1)
    Column C is populated using: =IF(ISNUMBER(SEARCH("detail",A2)),A2,"")
    The output in columns E:F is populated using: =IFERROR(INDEX(B$2:B$18,AGGREGATE(15,6,(ROW($B$2:$B$18)-ROW($B$1))/($C$2:$C$18<>""),ROWS($A$1:$A1))),"")
    Let us know if you have any questions.
    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
    11-10-2020
    Location
    World
    MS-Off Ver
    365
    Posts
    8

    Re: Reorganizing table; parsing, text file based

    Hello; thanks for reply and the suggested solution;
    Seems that there is a need for helpers columns, because of the main column with data, it should be expanded. Must say, this works.
    I am now moving result column to second worksheet, using references.

    What I tried initially also included additional two columns, to have basic details in order. I tried to use the INDIRECT function, it works as it should, I was able to do better positioning of data.

    For example:
    Please Login or Register  to view this content.
    Thanks again for the suggested solution!

  4. #4
    Registered User
    Join Date
    11-10-2020
    Location
    World
    MS-Off Ver
    365
    Posts
    8

    Re: Reorganizing table; parsing, text file based

    And, yes, making additional worksheet, with results only, works great as well, referencing to the first worksheet.

    Your suggestion works in those lines when there is Row: without any Detail: as well.

    Just a question, is it possible to have the column mentioned instead of range (instead of 2 to 18; $B$2:$B$18, to have 2 - any, with exception of first, header row)?

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

    Re: Reorganizing table; parsing, text file based

    My suggestion would be to convert the range A1:C18 into a table.
    When new rows are added [1], or old rows are deleted[2], from the table the formulas in columns E:F will automatically update.
    [1] Test by selecting cell C18 and pressing the tab key, then type Detail: Three into cell A19
    [2] Test by selecting cell A13 and pressing the Ctrl and - keys
    Let us know if you have any questions.

  6. #6
    Registered User
    Join Date
    11-10-2020
    Location
    World
    MS-Off Ver
    365
    Posts
    8

    Re: Reorganizing table; parsing, text file based

    Hello; thanks for reply;

    This solution is great when entering new rows of data, row by row, is needed. Looks that Excel has very nice capability of referencing specific calculations into newly created table inside worksheet.

    What I did is, I added new worksheet (Results) with A column:

    Please Login or Register  to view this content.
    and B column:

    Please Login or Register  to view this content.
    The reason is, when I am copying - pasting data to first worksheet (Mainlist); the second worksheet (Results) is being populated with those newly entered data as well. The 1048576 as maximum number of rows.

    Looks that it's working okay. I had to expand the A and B formulas at the second worksheet down, question may only be related to that, is it possible to have mentioned formulas initially at starting row and to leave Excel to expand those results automatically down thru the worksheet? Or it's a must to populate formulas manually thru A and B columns at the second worksheet (it's not a problem at all, it is easy to do that, just wondering if it's possible to have Excel to do it)?

    (I had to use _AT_ in the formula, otherwise I may not submit this, also, for some reason, I cannot add reputation again, I already did that and now, cannot add second reputation.)
    Last edited by aldowski; 05-11-2021 at 01:29 AM.

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

    Re: Reorganizing table; parsing, text file based

    is it possible to have mentioned formulas initially at starting row and to leave Excel to expand those results automatically down thru the worksheet?
    I don't know as I don't have the 365 version, however I have asked some of the other contributors to chime in.

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Reorganizing table; parsing, text file based

    Please try

    =CHOOSE({1,2},LOOKUP(FILTER(ROW(A2:A18),LEFT(A2:A18)="d"),ROW(A2:A18)/(LEFT(A2:A18)="R"),A2:A18),FILTER(A2:A18,LEFT(A2:A18)="d"))

    or with LET function

    =LET(a,A2:A18,r,ROW(a),d,LEFT(a)="d",CHOOSE({1,2},LOOKUP(FILTER(r,d),r/(LEFT(a)="R"),a),FILTER(a,d)))

  9. #9
    Registered User
    Join Date
    11-10-2020
    Location
    World
    MS-Off Ver
    365
    Posts
    8

    Re: Reorganizing table; parsing, text file based

    Hello, thanks for reply;

    I tried to add row with some data, "Detail: Three" at the A19; at those two suggested options the range A2:A18 is still as it was.

    Is there a possibility for automating range increasing, expanding, after adding new details at A column (or copying - pasting some data there)?
    For example, if I am adding the "Detail: Three" into A19, the range (at the right side, at structured data) to be expanded to A2:A19 automatically?

    Even if there is no possibility for that - the result column(s) can be prepared as formula column, so it is not big problem.

    Option, Insert, Table, with checked: My table has headers; has the possibility to prepare complete column as a table, excluding header row; can the formula be propagated thru that column?

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Reorganizing table; parsing, text file based

    =CHOOSE({1,2},LOOKUP(FILTER(ROW(A2:A180),LEFT(A2:A180)="d"),ROW(A2:A180)/(LEFT(A2:A180)="R"),A2:A180),FILTER(A2:A18,LEFT(A2:A180)="d"))

    =LET(a,A2:A180,r,ROW(a),d,LEFT(a)="d",CHOOSE({1,2},LOOKUP(FILTER(r,d),r/(LEFT(a)="R"),a),FILTER(a,d)))

  11. #11
    Registered User
    Join Date
    11-10-2020
    Location
    World
    MS-Off Ver
    365
    Posts
    8

    Re: Reorganizing table; parsing, text file based

    Thanks for reply, okay, yes, those additional 0's may be used for expanding.

+ 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. Parsing XML from text file
    By colins5286 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-09-2017, 05:00 AM
  2. Parsing text file
    By handerson0603 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-06-2014, 12:14 PM
  3. Parsing text file into excel
    By Lisa4legin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-24-2012, 10:37 PM
  4. [SOLVED] Help Parsing Text File into Rows & Columns Based on Values
    By griffinco in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-25-2012, 03:48 PM
  5. Parsing data from text file
    By grumps in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-14-2009, 05:59 AM
  6. Parsing text file to Excel (VBA)
    By mixmaster in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-19-2007, 03:18 AM
  7. Parsing a Text File
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-30-2006, 12:45 PM

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