Forum Discussion
Messy data handling in 1 column
HotChilli here is an example XLS... here is an example of original data
| Column | Value |
| 31/01/2020 | 1 |
| Joe Bloggs | |
| 1412123 | |
| 12314124 | |
| 52342344 | |
| 1/02/2020 | 4 |
| N/A | |
| 1314234234 | |
| N/A | |
| 10203030 | |
| 31312330 | |
| Joe Bloggs | |
| 2/02/2020 | 5 |
| 3/02/2020 | 3 |
| Jane Doe | |
| 31/01/2020 | 5 |
| N/A | |
| 12312344 | |
| 1/02/2020 | 4 |
| 2/02/2020 | 3 |
Expected Output is this
| Date | User | Value |
| 31/01/2020 | Joe Bloggs | 1 |
| 1/02/2020 | Joe Bloggs | 4 |
| 2/02/2020 | Joe Bloggs | 5 |
| 3/02/2020 | Joe Bloggs | 3 |
| 31/01/2020 | Jane Doe | 5 |
| 01/02/2020 | Jane Doe | 4 |
| 02/02/2020 | Jane Doe | 3 |
Super UserHi,
This data does not make any sense whatsoever. For 2/2/2020 and 3/2/2020, there are no names in your source data. So then why should there be Joe Bloggs against these 2 dates?
Thanks Ashish_Mathur. The names are not meant to be lined up against the dates - that's the reason for my posting. If things were lined up fine then I would have been able to solve it but unfortunately the data does not come out like that.
Take a look at the table again. I edited the data so you can see what happens when the next user name comes up in the last. In this case, I added Jane Doe. The rules are that the top section refers to Joe Bloggs, the next user is Jane Doe. The date values are the only row where value data is recorded against.