Convert rows to columns while consolidating Column A

Hi All, long time viewer, first-time poster.

My problem looks like this:

1	a
1	b
1	c
2	d
2	e
2	f

My goal is for it to look like this:

1	2
a	d
b	e
c	f

I've tried to use a pivot table, but as it turns out, only numbers work as values, not text, which is what my data is.

I also tried to do a lookup, but I only got the first response each time. I think there should be a way to iterate through each row where A=1 and get the value B for that row. So far I've not come up with much. I want to be able to have a 'dynamic' sheet with the forumla and layout pre-set and the data sheet can be updated as needed.

Any help would be appreciated.
I've attached a sample workbook.