What you have there is a spreadsheet, not a database table. Rows in a database table don't have a sequence to them, so there's no way for the database to know that "the second row" should have the same value as "the first row". As far as the database is concerned, there is no "first row" or "second row". Those are things that humans looking at the data add in, based on our own mental prejudices.

I know that sounds weird, and it's one of the hardest things for new DBAs to wrap their heads around (along with how join math works, that's the one I usually finds kicks people's feet out from under them them worst). But it's true.

It's one of the key differences in how you approach databases vs how you approach spreadsheets.

What you're trying to do isn't something that SQL Server (or Oracle, DB2, MySQL, etc.) is designed to do. You won't be able to count on the results working correctly because of that. It may look right 10 times in a row, but you can't count on the 11th time being right. So don't rely on it at all.

Get the devs to fix how they are entering the data. That's the only valid solution.