Split Column with multiple values in same column

Question

Hi&nbsp;I have a file with multiple columns, where each column can be one or more companies. Something like the following format:&nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | Company1&nbsp; &nbsp; &nbsp; | Company201-02-2019&nbsp; &nbsp;| 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 200001-02-2019&nbsp; &nbsp;| 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 200001-02-2019&nbsp; &nbsp;| 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 200001-02-2019&nbsp; &nbsp;| 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2000...... (400 rows about)Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | Company 1&nbsp; &nbsp; | Company 401-02-2018&nbsp; &nbsp;| 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 200001-02-2018&nbsp; &nbsp;| 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 200001-02-2018&nbsp; &nbsp;| 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 200001-02-2018&nbsp; &nbsp;| 1000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| 2000&nbsp;I need to unpivot this, to something like this:Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; |&nbsp; Company&nbsp; &nbsp; &nbsp; &nbsp;|&nbsp; Amount01-02-2018&nbsp; &nbsp;| Company1&nbsp; &nbsp; &nbsp; | 100001-02-2018&nbsp; &nbsp;| Company2&nbsp; &nbsp; &nbsp; | 200001-02-2018&nbsp; &nbsp;| Company3&nbsp; &nbsp; &nbsp; | 200001-02-2018&nbsp; &nbsp;| Company4&nbsp; &nbsp; &nbsp; | 2000.....How can I achieve this? Is there any way?&nbsp;BTW, This is an example of the file:https://www.spensiones.cl/apps/valoresCuotaFondo/vcfAFPxls.php?aaaaini=2002&amp;aaaafin=2019&amp;tf=A&amp;fecconf=20191130&nbsp;Thanks!

anonymous · Answer

Anonymous&nbsp;Right click on the date column and select Unpivot Other Columns.&nbsp;---Please hit the&nbsp;"Accept as Solution"&nbsp;button if my post answered your question! If my post was helpful please consider giving it a&nbsp;"Thumbs Up."

anonymous · Answer

Hi&nbsp;Didn't work because doing that I've lost the relation between the companies and the amounts.&nbsp;😞&nbsp;Thanks

anonymous · Answer

I guess I'm unclear what you mean by the "relation between the companies and the amounts"

You started with something like this, correct?:

And you should have ended up with something like this:

What do you need that this does not have?

anonymous · Answer

&nbsp;The difference is that in one Column the file can have more than one Company, like these:

anonymous · Answer

Anonymous&nbsp;You will have to duplicate the query and then use Remove Rows and Use First Row as Headers to transform the table into multiple queries containing one table of data each:Then add a column to each query so that the columns match across queries. Use add custom column with a value of = nullOnce the columsn in all your queries match up, append the queries as new to put them all together:Now right click on the date and select unpivot other columns:

Forum Discussion

Split Column with multiple values in same column

6 Replies

Related content

how to dynamically replace values for multiple columns

count values from multiple columns

Stacked Column Chart with multiple values?

Waterfall with multiple values

Multiple Values Same Column