Horizontal counting with conditions

I want to identify oldest and latest job without breaks. In Row 50 work 5B is the oldest job while job 1 is the latest. In row 47 oldest job is 1 and newest job is also 1.

In terms of output i am looking for 2 columns one for oldest job and another for newest job.

In Database

In-Database

Accepted answers

grazitti_sapna

@Chinmay_Pednekar , Hope the below solution is what you need. Please let me know if it was helpful and do accept my solution.

Oldest and newest.yxmd

All comments

binu_acs

@Chinmay_Pednekar Can you upload the input file?

Christina_H

How are you determining the oldest and newest jobs? E.g. row 47 you say 1 is both oldest and newest, but what about 3?

Here's a workflow that calls the leftmost entry "oldest" and rightmost entry "newest"

Oldest and newest.yxmd

Chinmay_Pednekar

I am looking for a continuous set. If I work my way from left to right then if the first column is newest and if we have continuous occurrences then the right most is the oldest occurrence. So in the case of row 47 since there is a break, therefore Work_1B is the oldest and newest occurrence.

Christina_H

I see, here's an updated workbook using a multi-row formula tool to identify the relevant rows (and swapping round the oldest/newest labels)

Oldest and newest.yxmd

grazitti_sapna

@Chinmay_Pednekar , Hope the below solution is what you need. Please let me know if it was helpful and do accept my solution.

Oldest and newest.yxmd

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335