Matrix or Pivot table or else?

Hello Forum,

I have to build a "co-location" Matrix and I am wondering if Excel will save me the manual entry…
Here is the context: I have a table of around 2000 rows, with 2 columns.
In the first column, I have adresses, in the second, company names.
In the end, I need to have a crossed table showing which company colocates with the other, whatever the address they sit at.
Note: the number of rows per address may be different from an address to the other, depending on the number of companies they accomodate.

I tried manipulating the data with Pivot tables but I can't seem to catch the thing; and it seems I can't use the company field twice in the same Pivot, in rows and columns.

In short: I'm stuck.

I am including an example of what I have and what I need.

Thank you for your help!

In your sample, companies 3 and 6 both have Address 4. Why is there not a 1 in cells J4 and H9 of your sample?

In your sample, there is no Company 5. Why are there 1's in the Company 5 row and column?

Are your desired results not based off of your sample data? This makes it very confusing for us.

I'm sorry I made this example this Morning and started with 10 companies and 10 addresses, but realized that I didn't neet so many items and figured people would understand the picture. Those are just "manual" mistakes that I properly want to avoid by using Excel Tools.
I downsized my example to three items. I hope I did not make any mistake (early Morning).
Thank you.

Thank you very much Falcondude, this is a really good start.

My only concern is that I have 37 companies to take into account and I can't insert 37 "if" in the formula, unless there is a way to skip the blank cells… ?

Here is a better (more scaleable) method using Power Query.

If you like this method, I can walk you through the process.

I followed this.

I just looked up Power Query and understand that I have to download it. Unfortunately, computers in our company are seriously restricted and downloading anything is impossible. We have to work with the authorized applications.

Maybe I can use Access if we talk about queries?

A UDF will require the workbook to be saved as macro-enabled

Unless this is a one-time task.

will that be allowed in Sybille's company? Probably not.

I have no idea. My company has restrictions on emailing macro enabled workbooks but not saving or opening them.

That being said, this is one of the reasons why I try to stay away from bringing code into Excel if I can avoid it.
I personally would rather create 37 IF functions in a formula (which you can do, it's just tedious) than to create a UDF and save the workbook as .xlsm.

Yes I can create and use .xlsm workbooks.

I just need to know what you mean by "UDF" ?

Is it a VBA code?

UDF stands for User Defined Function. When you create a UDF, you are creating a custom function (through VBA code) to be used in that workbook.

If you create a UDF named TEXTJOIN (some don't like to name UDF's the same as built in functions but we won't get into that), you can use the TEXTJOIN function the same way that those with Office 365 use it.

You can use your favorite search engine to search for "TEXTJOIN UDF" and go from there.

Ok. I'll look it up this afternoon and will post any question I will surely have…

Thank you!

So thanks to your suggestion and to the Textjoin function, I was able to create my table with the proper layout.

Thank you so much Falcondude!