How do I make a multiple account mapping?

Not long ago, the leader assigned a node task, it is necessary to count the company's each employee has the company which application system access rights, the corresponding login account is what respectively.

The company has ERP, office automation, instant Communication, report query, part design, such as five application systems, the above application system, in the business of mutual independence, permissions and mutual cross, for example, John only ERP, parts design two sets of system permissions, and Dick but the part design system outside all system permissions. Through the analysis of each system Database account table found that ERP has 100 users, office automation has 300 users, instant communication has 200 users, report query has 150 users, parts design has 500 users, the total factory staff of 1000. In the face of such a large and irregular data, how in the shortest possible time, easy and accurate statistical "employee, System User Mapping table" it? Artificial query to build a table, obviously not practical, or to study the powerful function of mining execl function bar.

First, create a new workbook by execl, design the structure of the User mapping table, and import all employee names (Figure 1). Second, you will save the Account table exported from each application system database as a execl table and import it into the map workbook, and change the content uniformly, leaving only the names and accounts in the Account table in the two fields.

Next, we start with the VLOOKUP function syntax, and in the B3 table of the User Mapping table, enter the formula:

"=vlookup (User mapping Table!) A3,erp Account table! $A $: $B $11,2,0) ", meaning that, in the" ERP Account table "A3 to B11 area, find the" User mapping table "of the A3 data, such as matching, the" ERP Account table "in the second column (that is, column B) of the data, copy to the B3 table. Due to the "User mapping table in the formula!" A3 "takes relative values, so check the B3 column and drag the mouse down the data area to pull the fill formula."

According to the above formula, the other application system accounts are automatically populated in the User mapping table (Figure 2) in turn.

Finally, we only need to modify the "User Mapping table" slightly. Copy the user map data, and in the new worksheet, right-click, select "Paste", in the pop-up window, select "Value", OK then leave only the value, and remove all the formulas, and then press the shortcut key ctrl+f, pop-up "Find and replace" window, switch to the "Replace" tab, in the " Find what column, enter #n/a, and leave the "Replace with" column blank (Figure 3), click Replace All to get the final result (Figure 4).

Small tip:

Open a Execl table, click the Insert Function icon in the toolbar, and in the pop-up Insert Function window, the wizard guides you through the function that satisfies the requirement. In the Common Functions list box, select the VLOOKUP function to display the function immediately below the window: search for the element in the first column of the table that satisfies the condition, determine the row ordinal of the cell in the range you want to retrieve, and then return the value of the selected cell.