Hello @Shraddha2204
Yes, we can do it with automation. There are 2 ways to achieve this.
If there are all records in a single table or multiple tables only catch here is it's need to linked then you can achieve this using some basic UI steps of Airtable Automation.
But if those records are not linked then the best way to use the script step and then write your logic for filter and update data to achieve these things.
👍