I have the following situation. Information is recorded in separate rows which needs to be consolidated into a single row so that a single letter detailing each activity can be generated. The spreadsheet shows the current situation, row 9 and below shows the required output. Note, the person I'm helping is not familiar with Power Query so I'm seeking a formula based approach if possible. Next choice would be a macro if it's simple enough to adapt to the actual data.
| Sample 2.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | Name | Faculty | Work completed | Hours | Cost Centre | ||||||||||
| 2 | Sam | Medicine | Lecture | 2 | Nursing | ||||||||||
| 3 | Sam | Arts | Tutorial | 3 | Admin | ||||||||||
| 4 | Jack | Psychology | Lecture | 1 | Admin | ||||||||||
| 5 | Jack | Arts | Tutorial | 4 | General | ||||||||||
| 6 | Jack | Arts | Tutorial | 3 | General | ||||||||||
| 7 | |||||||||||||||
| 8 | |||||||||||||||
| 9 | Name | Faculty | Work completed | Hours | Cost Centre | Faculty | Work completed | Hours | Cost Centre | Faculty | Work completed | Hours | Cost Centre | ||
| 10 | Sam | Medicine | Lecture | 2 | Nursing | Arts | Tutorial | 3 | Admin | ||||||
| 11 | Jack | Psychology | Lecture | 1 | Admin | Arts | Tutorial | 4 | General | Arts | Tutorial | 3 | General | ||
Sheet1 | |||||||||||||||
