Newbie in distress

Copper Contributor

I know what I want to accomplish, but do not have the knowledge about how to accomplish it efficiently in Excel.

The assigment I've been is "List all the codes (A through Z), one on each line, using their full name, and list the office(s) connected to that code"

Here is a simplified versjon of the two source lists I've got, and the desired outcome.
Would appreciate any feedback on which corner of the Ecle formula universe to begin my quest.
Thanks!

2018-07-17 Excel case.png



Best Regards,

Geir

2 Replies

- Select your data, click the Data tab (assuming you have Excel 2016/365).

- Click the "From table" button. Excel asks to convert your data to a table. Check the box "My table has headers" and click OK.

- Your table opens in the "Get and Transform" query editor.

- Drag the second column to the left so that it becomes the first column.

- CLick on the now second column (the one with the letters) and select "Transform", "Split column", "By delimiter". Choose comma.

- this inserts a number of new columns.

- Select all new columns, choose "Home", replace values, let it replace space with nothing

- Select the first columnand choose "Transform", "Unpivot other columns".

- Click the column you don't want and press the del key to remove

- click Home, select "Close and load to" so you can choose what location you want the end result to appear.

- In the newly inserted table, use VLOOKUP to extract the full names from the other table.

Greetings! Attaching required file as desired. Hope it helps. Thanks