Excel: Help! Formula needed

SMcGivern 

I'm working with seals so it is difficult to identify individuals. I'm trying to create a spreadsheet ready for data collection and have come across two problems. 

So at least there are no concerns with violating their privacy here. 🙂 It might be helpful, since that worry doesn't apply, if you could post the actual spreadsheet rather than just an image. Use OneDrive or GoogleDrive and paste a link here that grants access.

That said, let's look at each of your questions:

Problem A: The ID (identity) relates to which individual it is. At present they are named A-F. This is so that when they are identified, the ID that the individual is can be added in, as it may be the case that later in the videos the individual is identified. Is there a way for excel to change all related cells to the new number? For example, when the first "a" changes to "105", I want it to change all the "A's" in the rest of the data set to "105" for that date? There will be multiple different days and times featured on the data collection sheet. So if a formula is created that works for this, will it then change it for the rest even over different dates?

Answer A: yes. That should be fairly easy.

• To be on the safe side, I'd suggest adding a "helper column" to the right of the current ID column, header (perhaps) "Name" or some other heading that indicates "we've actually identified this individual."
  • "safer" in that doing it this way doesn't destroy the original ID, just in case there's "re-identification" later in the process. I believe in keeping history, in that sense.
• Then create a table on a separate sheet in the same workbook where you can define the relationship between the temporary letters (your current ID) and the more verified "Name." (I've attached a sample workbook illustrating this, but as noted, if you could post your real one, then we can be fully on the same page.)
• Then in the helper column, use a formula like this (see attached spreadsheet for how it works). This uses the LET function, so you'd need a new version of Excel or a subscription to Microsoft 365. I'm on a Mac myself, so that per se is not an issue.

=LET(known,INDEX(Table2[Name],MATCH([@ID],Table2[ID],0)),IF(known=0,"",known))

Problem B: So far I haven't seen a solution for this yet. Is there a way for Excel to recognise the time patterns? I have tried the control+E method and Excel doesn't recognise the pattern

Answer for the moment: What do you mean by time pattern in this case. What kind of pattern? What is the nature of the pattern you're wanting to recognize? Frequency? Intervals? ??? Duration? etc.

Finally: what version of Excel do you have? And, while we're at it, what Mac OS?