Hi
I have data from a Microsoft Form which is used to update staff details, and I need to be able to combine rows from the same colleague to retain only the latest information in a single row (there are other sheets in workbook which use various Index/Match functions, which then only return the first value found, and it's missing critical information. Data from the MS form means that different columns will be updated at different times, or at the same time. I've tried Consolidate, but can't seem to get this to work as I need it to; staff can update the same element multiple times, or different elements at different times. Below is an example of the data I might need to consolidate:
Staff need to input their name and staff number every time, but then only need to enter the details that change. I've tried sorting the data on the sheet in different ways, but it doesn't give the consistency that merging would allow. What I need to do is to merge lines with the same staff number (as a primary ID) keeping the latest data in each column (the real sheet has 31 columns).
Any thoughts or suggestions would be appreciated!
TIA
Dark V
I have data from a Microsoft Form which is used to update staff details, and I need to be able to combine rows from the same colleague to retain only the latest information in a single row (there are other sheets in workbook which use various Index/Match functions, which then only return the first value found, and it's missing critical information. Data from the MS form means that different columns will be updated at different times, or at the same time. I've tried Consolidate, but can't seem to get this to work as I need it to; staff can update the same element multiple times, or different elements at different times. Below is an example of the data I might need to consolidate:
| ID (from MS Forms) | Name | Staff No | Store Number | Course Start | Manager Name |
| 1 | Emmett Brown | 1234 | 11 | ||
| 2 | Marty McFly | 4567 | 22 | ||
| 3 | Martin Tenbones | 8901 | |||
| 4 | Marty McFly | 4567 | 22 | 19/09/2022 | |
| 5 | Emmett Brown | 1234 | Gordon Gecko |
Staff need to input their name and staff number every time, but then only need to enter the details that change. I've tried sorting the data on the sheet in different ways, but it doesn't give the consistency that merging would allow. What I need to do is to merge lines with the same staff number (as a primary ID) keeping the latest data in each column (the real sheet has 31 columns).
Any thoughts or suggestions would be appreciated!
TIA
Dark V
