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.

Put this formula in C1 of the Data sheet:

=IF(A1="","",A1&"_"&COUNTIF(A$1:A1,A1))

Copy it down to the bottom of your data (or beyond). Then you can use this formula in A2 of the Desired sheet:

=IFERROR(INDEX(Data!$B:$B,MATCH(A$1&"_"&ROWS($1:1),Data!$C:$C,0)),"")

Copy this across and down, as required.

Hope this helps.

Pete

EDIT: This assumes that you already have the headers in row 1 of the Desired sheet.

Pete

Thanks, Pete! Your solution worked great.

CK76, Thanks for your reply. I had some trouble connecting your formulas to my data. But it sounds like it would automaticly generate my Row1, which would have been nice. I'll be gone for the weekend, but I'll try your formula again Monday.

Glad to help, Noel.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete