How can I remove Null cells before the first non-empty cell for each row in a table
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Solved! Go to Solution.
- Labels:
- Best Practices
- Common Use Cases
- Reporting
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Majao
Here's what you could do:
- Transpose the data
- Filter out empty/null cells
- Tile tool to assign the month sequence for each customer
- Crosstab back out
- Rename fields
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Majao ,
Is the data actually 0 or empty? Important distinction, it's achievable but wanted to check.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Data is [Null]
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Impressive - thank you!
