Transpose vertical CSV data set into a linked horizontal table with index match and/or lookup and multiple criteria

[ckazok]

Hi all,

First of all, thanks in advance for any help. I will post a problem I am facing regularly. I can get around it with Pivot tables etc. but I want to do it with a proper formula.

Every month, I get a CSV file with the structure as shown on the first table. There are multiple columns, showing date, country, product and value.

Because our data systems are in horizontal date format, I have to transpose this table into the format as shown on the attached picture. Countries making the vertical column, date the horizontal and a separate table for each products.

It should be linked via a formula like index match so that once the original raw table updates every month, the tables automatically update, too.

My problem is, when I use index + multiple match for this, it usually delivers only the first value and then nothing.

Hope someone can help me.

Thank you in advance!

 

[Fluff]

Hi & welcome to MrExcel.

Can you post some sample data rather than an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.