count each element in the column and assign it to the row it is responsible for.

Hi everyone!

I'm new at this forum and that's my first question about Excel.

So, I have a one table which it has a different kind of data.

***First table (table with machines column)***

I want to count each vlans in the "vlan" column and assign it to the "host" it is responsible for.

İ attached a example table image that I want:

***Second Table***

So. how can i automate this procedure? I need, when new vlan added to first table it will change in the second table also

See yellow banner at top of pageon how to attach a sample workbook.

And please update your Excel version (365, 2019, 2016) as you appear to have entered the release number: answers DO depend on this information being correct.

Thank you for advice, I changed the Excel version and I attached the workbook.

With Text to column and , as seperator.

After that Power Query to re-arange the data.

After that a Pivot Table to show the expected result.


See the attached file.


Notice the expected result is differant.

Thanks for the rep.

Glad I could help.

Thanks for marking the question solved.