How to create municipality dummy variables in Excel

Hé there,

I have the following problem: I have a dataset with corporations (codes) and (Dutch) municipalities (names) in which they have real estate development projects. Every row is a development project, so there are often multiple rows for each corporation code. What I now wish to do is make a new sheet in which each corporation code has only one row, each column is the name of a municipality (395 columns), and where they match there is a 1 if the corporation is active in that municipality and a 0 if it isn't.

With what code could this be accomplished?

I have included pictures of the datasets I speak of. The first shows the corporation codes (=Instellingsnummer) and active municipality (=Gemeentenaam). The second shows the format I want it in with again corporation codes under "Instellingsnummer" and the municipality names in the columns starting at Aa ten Hunze, Aalsmeer, Aalten etc.

I really hope someone can help me with this! Huge amounts of thanks in advance!!

You can do this with PowerQuery in Excel 2019.

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Jan

In Sheet2 of your sample you haven't seem to have added any 0/1s to indicate the active municipalities for the corporations in column B.

For example, should there be a 1 in FO2 to indicate that the corporation Stichting Wonen Noordwest Friesland is active in the Leeuwarden municipality?

Attached is a file showing a pivot table. Advise if this is something you can work with.

Here's a PowerQuery solution:

M Code (column list - connection only):

Please Login or Register  to view this content.

M Code (matrix):

Please Login or Register  to view this content.

alansidman

Thank you. What is the code you used to make this? If I could do this for every corporation I could replace all nonzero numbers with 1's to make the dummy variables.

No code required. Just inserted a Pivot Table. If you are unsure how to create PTs then look at this link

https://www.contextures.com/CreatePivotTable.html

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

What worked? Which did you go for, out of interest?