Need to populate value in different view

asi234

New Member
Joined
Jan 7, 2014
Messages
1
I'm using excel 2010.
I would like to change the format from "Room List" to Result. Currently, manually paste the values. I'm looking for a faster solution as i have 100 rooms in my original list.
Below is the example. Please help me.

Room List
Room NameMonthAccuracy
Room1

<tbody>
</tbody>
Jan-1350
Room1Apr-13100
Room1Jul-1320
Room1Oct-1320
Room2Jan-13100
Room2Apr-13100
Room2Jul-13100
Room2Feb-1350
Room3Oct-13100

<tbody>
</tbody>


Result:
Room NameJan-13Feb-13Apr-13Jul-13Oct-13
Room150n/a1002020
Room210050100100n/a
Room3n/an/an/an/a100

<tbody>
</tbody>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Paella1

Active Member
Joined
Mar 10, 2005
Messages
382
You need a Pivot Table.

Highlight thae Range (including the headers). Select Insert -> Pivot Table -> Pivot Table.

At the dialog box choose New Worksheet.

Then you will get a blank table and on the right will appear your column headings. Drag Room Name to the Column Heading area in the table. Drag Month to the Row Heading Area. Then drag Accuracy to the main section of the table, and make sure it is set to sum (check at the bottom right of the screen).

This will take less than 10 seconds when you get the hang of it.
 

Forum statistics

Threads
1,137,301
Messages
5,680,705
Members
419,929
Latest member
Atlas Quinn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top