iterate range transpose to columns grouped by person

[crtallent]

I am new to programming within Excel. I have been searching through the forums looking for something similar to help me solve the issue that I am having; however, I have not been able to find anything yet.

I am working with a result set of data that is formatted similar to the excerpt below. The actual data set is not fixed. It could easily be thousands of rows if not more; however, the format would be the same.

Given

Item Number	Person	Question	Answer
1	Anna	Favorite Color	Blue
1	Anna	Favorite Car	Model T
2	Anna	Favorite Color	Blue
2	Anna	Favorite Car	Mustang
1	Billy	Favorite Color	Green
1	Billy	Favorite Car
2	Billy	Favorite Color
2	Billy	Favorite Car	Vette
1	Charlie	Favorite Color	Black
1	Charlie	Favorite Car	Bug
2	Charlie	Favorite Color	Pink
2	Charlie	Favorite Car	Horse

I am trying to figure out the best approach to iterate through this data and have it formatted similar to below:

Desired

	Anna		Billy		Charlie
Item Number	Favorite Color	Favorite Car	Favorite Color	Favorite Car	Favorite Color	Favorite Car
1	Blue	Model T	Green		Black	Bug
2	Blue	Mustang		Vette	Pink	Horse

I have been looking at doing this cell by cell; however, I believe there are more efficient ways of doing this. I think the part that is holding me up the most is trying to come up with the steps needed to make it look like the desired.

If anyone could provide some potential guidance (maybe an example) on how I might go about solving such a request, then I would greatly appreciate it.

I am also attaching the sample workbook that has the data mentioned above within it.

Example.xlsx

[Marcol]

Try this CSE array in B2, confirm with Ctrl+Shift+Enter, not just Enter
Then Drag Across and Down

Please Login or Register  to view this content.

[EDIT]
Your sample sheet "Desired" B3:G4 is formatted for text, change this to general before trying the formula

Here is the non CSE equivalent

Please Login or Register  to view this content.

[crtallent]

Thank you for the reply.

I am having a little bit of issues entering that formula. When i try to enter (ctrl shift enter) it just appears as text not any particular value.

[Marcol]

Try this CSE array in B2, confirm with Ctrl+Shift+Enter, not just Enter
......
[EDIT]

Your sample sheet "Desired" B3:G4 is formatted for text, change this to general before trying the formula

Here is the non CSE equivalent

Please Login or Register  to view this content.

Note the edit to post #2