How can I remove Null cells before the first non-empty cell for each row in a table

[Majao]

Dear all, 

I am working with a data set of revenues per customer over several months. However, I want to change this table to show revenues per customer from the first month they purchased. So instead of the first column header '202001', the first column header should be Month 1, and instead of the values being the actual spend per customer in that month, the values should be the spend per customer in their first month. Example tables below: 

What I have: 

	202001	202002	202003	202004
Customer 1		50	20
Customer 2	2			40
Customer 3		3		30

What I want:

	Month 1 after first purchase	Month 2 after purchase	Month 3 after purchase	Month 4 after purchase
Customer 1	50	20
Customer 2	2			40
Customer 3	3		30

The data set is very large. I normally do this in excel, but there are unfortunately too many rows and too many columns. 

Could someone help?

[Luke_C]

Hi @Majao 

Here's what you could do:

1. Transpose the data
2. Filter out empty/null cells
3. Tile tool to assign the month sequence for each customer
4. Crosstab back out
5. Rename fields

[Majao]

I believe that works, but notwhen there is a long sequence of zeros before there are new values again. Is that something you can help with as well? I updated the table in my question to include a number in the end after some zeros. 

[Luke_C]

Hi @Majao ,

Is the data actually 0 or empty? Important distinction, it's achievable but wanted to check. 

[Majao]

Data is [Null]

[Luke_C · Accepted solution]

@Majao 

Here's an updated version using a multirow formula to check. 

[Majao]

Impressive - thank you!