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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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,171,579
Messages
5,876,290
Members
433,192
Latest member
butterexcel

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