Convert matrix style to database style

[Pierre]

Hello,

I have a matrix style with monthly financial information per customer, that I would need to convert to a database style presentation. There is any way to use formulas to do so instead of having to copy and paste?

I enclosed an example of matrix style that I have and the database style that I'm looking for

Thank you!

 

[Luke M]

It can be done using the INDEX function and some tricks using MOD and INT. Here's the file.

Since you have 2 different matrices acting as the source, it's easier to use formulas to pull into 2 separate databases. You could then Copy/Paste into a single column later if desired. Also, I would strongly recommend not including a Total row in your database. You can use COUNTIF/SUMIF function to get those values, and would only get in the way of some tools like PivotTables.

 

[Pierre]

Hello Luke,

Thanks a lot for your answer, that's exactly what I was looking for! I now will have so spend some time to understand clearly the formulas, I would never have been able to do that alone

Just one thing: I see that if I change the number of customers to a figure higher than 4 I have some error. As I understand it is due to the fact that the array matrix in the index formula is fixed. How would it be possible to modify it so that the array would always cover all the customers, as I will probably will have to add new customers in the future?

Thanks again

[EDIT]

I believe I found a solution to make the array dynamic, but maybe it's not the most clever. If someone can have a look and let me know, I'd appreciate it

 

[Luke M]

Nice job Pierre! Way to be bold and take a crack at it. Your solution looks pretty good.

 

[Pierre]

Thanks I have been reading about the INDIRECT formula for the last couple of days, and finally have been able to use it by myself!