Filtering for patchy history

[lcoombes]

Hi everyone,

I have a data table like the below

	Year 1	Year 2	Year 3	Year 4
Service 1	59			92
Service 2		100	115	120
Service 3			200	151
Service 4	98	76	65	99
Service 5		24		63
Service 6	100	99		84

I want to analyse the services with 2 years of data separately to those with more than 2 years available. 

So the data would be split into two tables like these:

	Year 1	Year 2	Year 3	Year 4
Service 1	59			92
Service 3			200	151
Service 5		24		63

	Year 1	Year 2	Year 3	Year 4
Service 2		100	115	120
Service 4	98	76	65	99
Service 6	100	99		84

Does anyone have any idea how I might do this?

Thank you very much in advance 🙂

[mmenth · Accepted solution]

Hi @lcoombes,

The attached worked for me! Essentially you transpose the data, remove empty/nulls value fields, count the remaining, split the data using a filter based on your criteria, then join each set back to the original, removing the right join data fields.

Best,

mmenth

[lcoombes]

Thanks so much for such a quick reply @mmenth, very clever!! Much appreciated 😀