Hi,
While i can frame a formula based solution, i am sure your next question would be "What if i have more Groups". This approach will take care of as many Groups as you have
1. Select the data range of 2 columns (including the header row of Item and Group) i.e. B4:C19 and assign it a name [Ctrl+F3], say try
2. Select the data range of 2 columns (including the header row of Item and Group) i.e. B4:C19 and convert it to a Table/List (Ctrl+L)
3. Save the file on the desktop and click on any blank cell
4. Navigate to
a. Excel 2003 - Data > Import External Data > New Database Query > Excel files > OK
b. Excel 2007 - Data > Get External Data > From Other Sources > From Microsoft Query > Excel files > OK
5. Select the Excel file (saved in 3 above) and click on Next
6. Click on Next 2 times
7. Select "View or Edit Query in MS Query". Click on Finish
8. Click on SQL and type the following
TRANSFORM first(item)
SELECT item
FROM try
GROUP BY item
PIVOT group
9. Go to File > Return Data to MS Office Excel
10. In the Import box, select Table and the cell where you want the output. Click on Finish
11. You will get the desired output
12. Now when you edit any entry in the range try (as defined in step 1 above) or any new data by rows, just right click on any outout cell and Refresh the data
Hope this helps. Do let me know how this works.