+ Reply to Thread
Results 1 to 7 of 7

How to convert text table to a matrix (like using pivot)

  1. #1
    Registered User
    Join Date
    08-22-2018
    Location
    Bangkok, Thailand
    MS-Off Ver
    2016
    Posts
    3

    How to convert text table to a matrix (like using pivot)

    Hi guys,

    I'm trying to convert a table of text into a matrix (like using pivot table) as example below.
    Is there any simple mean to do so?
    I tried to use "From Table/Range", then "Pivot Column" but it shows error for multiple texts per one machine.
    And it shows 'null' for no-value cells.

    convert.PNG

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: How to convert text table to a matrix (like using pivot)

    Can you attach your sample workbook.

    Go Advanced -> Manage Attachments -> Upload

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    48,140

    Re: How to convert text table to a matrix (like using pivot)

    Hi, welcome to the forum

    You could probably use INDEX/MATCH for this, but for better help, as suggested, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    08-22-2018
    Location
    Bangkok, Thailand
    MS-Off Ver
    2016
    Posts
    3

    Re: How to convert text table to a matrix (like using pivot)

    Attached the file
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,860

    Re: How to convert text table to a matrix (like using pivot)

    This proposed solution adds two columns to the Table of Text.
    Column1 is populated using: =IF(OR(B4<>B3,C4<>C3),D4,E3&", "&D4)
    Column2 is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that Wrap Text formatting is applied to Column2
    The range I4:L7 is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    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.

  6. #6
    Registered User
    Join Date
    08-22-2018
    Location
    Bangkok, Thailand
    MS-Off Ver
    2016
    Posts
    3

    Re: How to convert text table to a matrix (like using pivot)

    Hi JeteMc.
    Thanks. That works.
    Only thing I need to do is to sort the data by month and machine in case I have additional data right?

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

    Re: How to convert text table to a matrix (like using pivot)

    You're Welcome and thank you for the feedback.
    Thinking about adding data:
    1) Yes, keep the data sorted as you have done in the file you attached to post #4,
    2) Modify the formula for Column 2 to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    3) Modify the formula for the range I4:L7 to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions, however if this should resolve the issue, please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. 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)

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