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

**polipo** · 08-22-2018, 01:09 AM

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

**kersplash** · 08-22-2018, 01:09 AM

Can you attach your sample workbook.

Go Advanced -> Manage Attachments -> Upload

**FDibbins** · 08-22-2018, 01:35 AM

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.

**polipo** · 08-22-2018, 01:45 AM

Attached the file

**JeteMc** · 08-26-2018, 05:46 PM

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:

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:

Please Login or Register  to view this content.

Let us know if you have any questions.

**polipo** · 08-27-2018, 07:26 AM

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?

**JeteMc** · 08-27-2018, 08:14 AM

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:

Please Login or Register  to view this content.

3) Modify the formula for the range I4:L7 to read:

Formula:

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.

**grossi** · 10-31-2022, 07:08 AM

Hello guy, is there a way easier with dynamic array for example?

**AliGW** · 10-31-2022, 07:27 AM

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

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

LinkBack

Thread Tools

Display

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Convert to matrix table

Need to convert pivot table numbers to text

Convert a Matrix into a List. Can I use Pivot Table for that?

[SOLVED] Pivot Table - Count number of rows in each category, display that amount in a table/matrix

VBA code to convert value in pivot table to text

[SOLVED] Convert matrix table into columns

Convert Text to Numeric Excel 2010 Pivot Table

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions