Split Column with multiple values in same column

[Anonymous]

Hi

I have a file with multiple columns, where each column can be one or more companies. Something like the following format:

Date              | Company1      | Company2

01-02-2019   | 1000               | 2000

01-02-2019   | 1000               | 2000

01-02-2019   | 1000               | 2000

01-02-2019   | 1000               | 2000

...... (400 rows about)

Date              | Company 1    | Company 4

01-02-2018   | 1000               | 2000

01-02-2018   | 1000               | 2000

01-02-2018   | 1000               | 2000

01-02-2018   | 1000               | 2000

I need to unpivot this, to something like this:

Date              |  Company       |  Amount

01-02-2018   | Company1      | 1000

01-02-2018   | Company2      | 2000

01-02-2018   | Company3      | 2000

01-02-2018   | Company4      | 2000

.....

How can I achieve this? Is there any way?

BTW, This is an example of the file:

https://www.spensiones.cl/apps/valoresCuotaFondo/vcfAFPxls.php?aaaaini=2002&aaaafin=2019&tf=A&feccon...

Thanks!

[Anonymous]

@Anonymous 

Right click on the date column and select Unpivot Other Columns.

---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."

[Anonymous]

Hi

Didn't work because doing that I've lost the relation between the companies and the amounts.

😞

Thanks

[Anonymous]

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]

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

[Anonymous]

@Anonymous 

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 = null

Once 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:

[Anonymous]

Thanks.

That will work for one file.. But is there any way I can automate this? The file is updated daily and it's a lot of work to update it daily. I'll need to check columns and positions every day.. There must be an easier way.. I hope!

Thanks