1

I have a set of data representing visitors and times of their visits. Each entry is in its own row in the sheet. Example; Time of the visit in A1 and Name in A2. The single digit numbers represent the day and range from 1 to 31. I need, in a new row or column =A1&A2, accounting for the day row. The issue is sometimes there is no Name as there was no visitor but the row where Name should go is not empty but rather has the Time for the next visit. I would like to add empty rows if there was no visitor. There is no indicator or special text in the data indicating if someone came to the meeting or not. would the below be better achieved with formulas or with a script and how?

Example of given data:

1  
2  
40P 01:40N  
40P 01:40N  
40P 02:30N  
8997 Firstname Lastname  
40P 03:20N  
9094 Firstname Lastname  
40P 04:10N  
8627 Firstname Lastname  
40P 05:00N  
4479 Firstname Lastname  
40P 06:00N  
M7967 Firstname Lastname  
40P 06:50N  
40P 06:50N  
60G 07:40N  
3  
40P 11:00N  
7588 Firstname Lastname(Z)  
40P 11:50N  
6540 Firstname Lastname  
40P 12:40N  
40P 12:40N  
40P 01:30N  
8606 Firstname Lastname  
60G 02:30N  
40P 03:40N  
8346 Firstname Lastname(Z)  
40P 04:30N  
6357 Firstname Lastname *important  
40P 05:20N  
4577 Firstname Lastname  
40P 06:10N  
8910 Firstname Lastname  
4  
5  
40P 11:00N  
1254 Firstname Lastname  

Want it to look like this:

1  
40P 01:40N - no visitor  
40P 01:40N - no visitor  
40P 02:30N - 8997 Firstname Lastname  
40P 03:20N - 9094 Firstname Lastname  
  • the data and the system it comes from is not "the best" but I have no control over it whatsoever
  • if the rows says 4 and the next row immediately under says 5, it would mean no times and or visitors on the 4th
1

I used some regex :

It needs to match lines like xxxx First name last name

^.\d{3}

If there's a match, then take this line and the previous line. Else take the line + no visitor.

Then special case when there's a single digit : Same idea, I used a regex ^\d$

Finally :

=ArrayFormula(
IF(
REGEXMATCH(TO_TEXT(A2:A100),"^\d$"),A2:A100,
if(
REGEXMATCH(A2:A100,"^.\d{3}"),A1:A99&" - "&A2:A100,A2:A100&" - no visitor")))

Result :

enter image description here

3
  • Great. This helps a lot. Please notice how, in my "Want it to look like this" example, the entry for 2:30 shows only one time. In your result it shows twice. The premise is that if there is a visitor in time slot, it should show only one time. How would that fit into the formula? Thank you. Sorry if it wasn't clear in my question. Oct 28 '20 at 0:45
  • Actually, it would be better to have time unique and show only once if possible. So, 1:40 - no visitor showing only once and then 2:30 - FirstName Lastname showing only once. Oct 28 '20 at 0:52
  • You can try split then filter with unique values, seems possible but not an easy task.
    – pjmg
    Oct 28 '20 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.