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