Home

Newbie in distress

Geir Waaler
Occasional Visitor

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
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies