How to change data from awkward column form to list form, suitable for Macro

Hi all,

Currently my data is in this form:

A B C D E
1 10 11 29 39
2 12 30 40
3 13 31 41
4 14 32 42
5 15 33 43
6 16 34
7 17 35
8 18 36
9 19 37
20 38
21
22
23
24
25
26
27
28
When I'd like it to be in this form:

1 A
2 A
3 A
4 A
5 A
6 A
7 A
8 A
9 A
10 B
11 C
12 C
13 C
14 C
15 C
16 C
17 C
18 C
19 C
20 C
21 C
22 ETC

I can do it with hassle but there's got to be an easier way to change between the two formats! Please help!

All the numbers are actually children's names so are all different, if that helps.

Cheers,

Harrison

insert two new columns at A and B

Paste this formula into A1

Please Login or Register  to view this content.

Paste this formula into B1

Please Login or Register  to view this content.

Fill down.

Sorry about the delay. I had to go to a funeral.

Insert a new column A

Paste this formula into A1 and fill down

Formula:

Please Login or Register  to view this content.