Dynamically move cell value when adjacent cell is sorted

sheet.PNG

Hi everyone. In the table I have depicted above, I am trying to make the values in column C shift dynamically when column B gets updated.

Use:
1) Values are input into column A. Column B then sorts these using a SORT formula.
2) Y or N values are input into column C in respect to the value in column B.
3) Later on, I add another value into column A, cell A10. For example, value "alpha".
Column B will sort this, and place "alpha" in cell B2, pushing "hotel" down to B3.
Column C will stay as it is, but this is not what I want. Is there a way for the values in column C to dynamically move so that the entered values shift with their corresponding cells from column B?

Please find sample spreadsheet attached.

Thank you for your help!

Power Query

Please Login or Register  to view this content.

Thanks Czeslaw! I think you're right to suggest that Power Queries is the way to solve this, but your solution isn't doing what I need.

The value entered in the 'Process' column needs to be in relation to the 'Sorted' column, not the 'Input' column. So when more data is entered into the 'Input' column, the 'Process' values need to shift with the 'Sorted' values.

Is this possible? Perhaps there need to be multiple power queries?

pls try this formula

Cell F2 formula

Formula:

Please Login or Register  to view this content.