Grouping and transposing data in excel

Question

i'm trying to create an excel spreadsheet to be used to perform some basic "planning".

In one sheet I have a list of couples:

In a separate sheet I need to rearrange such data in the following way:

So basically I need to group the items by week number and transpose the items associated with each week number horizontally.

Does anyone know if this is feasible with excel?

Answer 1

This is the formula you need:

=IFERROR(INDEX($C$5:$C$11,SMALL(IF($B$5:$B$11=$E5,ROW($C$5:$C$11)-MIN(ROW($C$5:$C$11))+1),COLUMNS($E$5:E5))),"")

Just put it on cell E5 press CTRL+ALT+ENTER for an array formula and drag along.

Answer 2

A Pivot Table will quickly handle that job.

---

More Information:

• Office.com : Create a PivotTable to analyze worksheet data

• Medium : Microsoft Excel: Data Analysis; Getting Started with Pivot Tables

• Tech Soup : Getting Started with Pivot Tables in Excel