Читать книгу Jump into Excel. Training Course from Beginner to Intermediate in two hours - - Страница 6

We proceed to fill out the «Result» sheet.

First, copy the information from the sheet from the Secretary to the «Result» sheet. Select and copy columns A:E with the names Department, Position, Surname, First Name, Patronymic.

Copied.

As it often happens in practice, we are faced with a small problem: the names in the file that was provided to us from one source (from the Secretary) are arranged in a different order. The rows are sorted differently than the rows with surnames in the file that we received from another source (HR department). If the rows with surnames on both source sheets (both from the Secretary and from the HR Department) would have the same order, then copying would be simple. We would select the columns we need (with dates of birth and employment), copy them and paste them. But in our case, this cannot be done, because it will lead to an error – in the file from the HR Department and on the sheet «Information from HR» in the second row is the surname «Borshchev», while in the file from the Secretary and on the sheet «Result» the second row is occupied by «Ivanov».

If we just copy and paste the columns from the «Information from HR» sheet to the «Result» sheet with data, as on the «Information from Secretary» sheet, then the second row with Borshchev’s Date of birth and Date of employment will be substituted into the second row with Ivanov’s Department, Position, Surname, First Name and Patronymic.

error: second row with Borshchev’s and Ivanov’s data

To avoid this error, we will perform preliminary data processing – we will put the rows on the «Result» sheet in the same alphabetical order as they are sorted on the «Information from HR» sheet. So that the line with the surname «Borshchev» on the «Result» sheet would become the second, immediately under the header, as well as on the «Information from HR» sheet. After that, we will be able to copy the columns from the «Information from HR» sheet to the «Result» sheet, and Borshchev’s data will be copied to the row with Borshchev, and not with Ivanov.

Let’s use Sorting for this.

Sort operation

Select the area for sorting (columns A:E or cells A1:E26), click on the «Sort» button on the «Data» tab, select «Sort by…", «Surname», in the window that appears, select the alphabetical sorting order «From A to Z», and click «Ok».

select «Sort by – Surname»

Please note: If we wanted to sort the names of Departments in alphabetical order, and then within these departments by Surname, we would first select «Sort by – Department», and then add a second level of sorting by clicking on «Add Level» in the upper left corner of the menu, and in the second level that appeared we would select «Surname».

first select «Sort by – Department», then by «Surname»

After the sorting is done, the order of the rows with surnames on the «Result» sheet matches the order of the rows on the «Information from HR» sheet.

Note: Sorting (and much more) can also be done using the menu that appears when you right-click. Using the right mouse button to open the menu is a very useful skill that speeds up your work in Excel.

Copy the data from the «Information from HR» sheet to transfer to the «Result» sheet.

Select three columns on the «Information from HR» sheet: «Full name», «Date of employment» and «Date of birth». We don’t need the Full name column for the task, but we use it to make sure that the rows with the surnames of employees after sorting now match everywhere.

rows with the surnames of employees after sorting match everywhere

After visual verification that the order of the rows is correct (that is, the last name from column «F» with the full name coincided with the last name from column «C»), the extra column «F» with the Full name must be deleted.

Deleting a column

Select column F by hovering the mouse cursor over the heading F and click the left mouse button. Then click on the right mouse button. In the menu that appears, select «Delete».

select «Delete»

Thus, all the original data has been transferred to the «Result» sheet.

Сreating a new column

Let’s create the columns «Experience of full years on the date of the report» and «Age of full years on the date of the report» ourselves.