How do I extract the first and last values in a column?

[Andries]

Hi All


How do I extract the first and last values in Column DD based the subject code in Column CY?
It must look like this for example:
AAD101C "RED-ROW 1-SEAT 1" "to" "RED-ROW 2-SEAT 10"
HAT321T "RED-ROW 2-SEAT 17" "to" "RED-ROW 3-SEAT 4"

Excel Workbook
	CY	CZ	DA	DB	DC	DD
2	Subject code	INVIGILATOR	BLOCK	ROW NUM	SEAT NUM	BLOCK/ROW/SEAT
3	AAD101C	ERASMUS, RASSIE	RED	1	1	RED-1-1
4	AAD101C	ERASMUS, RASSIE	RED	1	2	RED-1-2
5	AAD101C	ERASMUS, RASSIE	RED	1	3	RED-1-3
6	AAD101C	ERASMUS, RASSIE	RED	2	7	RED-2-7
7	AAD101C	ERASMUS, RASSIE	RED	2	8	RED-2-8
8	AAD101C	ERASMUS, RASSIE	RED	2	9	RED-2-9
9	AAD101C	ERASMUS, RASSIE	RED	2	10	RED-2-10
10	HAT321T	ERASMUS, RASSIE	RED	2	17	RED-2-17
11	HAT321T	ERASMUS, RASSIE	RED	2	18	RED-2-18
12	HAT321T	ERASMUS, RASSIE	RED	2	19	RED-2-19
13	HAT321T	ERASMUS, RASSIE	RED	3	1	RED-3-1
14	HAT321T	ERASMUS, RASSIE	RED	3	2	RED-3-2
15	HAT321T	ERASMUS, RASSIE	RED	3	3	RED-3-3
16	HAT321T	ERASMUS, RASSIE	RED	3	4	RED-3-4
30X (2)

 

[shg]

Perhaps

=INDEX(DD:DD,MATCH("AAD101C",CY:CY,0))
& " to " &
INDEX(DD:DD, MATCH("AAD101C",CY:CY, 1))

... after changing the formula in D2 and down to

=DA3 & "-ROW " & DB3 & "-SEAT " & DC3

 

[Andries]

Once again thank so much. Just what I was looking for