Hello,
I currently have a table with data in it. Some of the columns ask a question that require a drop down selection, in which multiple selection is allowed. What I am trying to do is to recreate this table so that each selection from those drop down menus becomes a column header and I will run formulas to check if each drop down selection is in that cell by inputting a 1 or a 0 (for the sake of being able to graph selections in Power BI). So let's say that my raw data table has the following headers: Date, Countries lived in, Comments. What I want to do is have a table that summarizes the answers, so it will look like: Date (which will just reference the exact answer from the original table), a col. for each country found in the drop down list, and comments (which again will just reference what is found it the original table). The point will be that for each row, it will display the date originally inputted, a 1 or 0 for each country and finally the comments. Please refer to the image posted.
My issue is that whenever a new row is added to the original table, the new table doesn't take the change into account. I tried using formulas that spill the data but for some reason Spill isn't compatible with tables. The whole reason I need it in a table is so that I can export it in power query. I am planning on just using the Spill values, and running a VBA code that can take each row that contains data from that range and input that into a table (if that works).
Please let me know of any possible solutions!
Thanks in advance
I currently have a table with data in it. Some of the columns ask a question that require a drop down selection, in which multiple selection is allowed. What I am trying to do is to recreate this table so that each selection from those drop down menus becomes a column header and I will run formulas to check if each drop down selection is in that cell by inputting a 1 or a 0 (for the sake of being able to graph selections in Power BI). So let's say that my raw data table has the following headers: Date, Countries lived in, Comments. What I want to do is have a table that summarizes the answers, so it will look like: Date (which will just reference the exact answer from the original table), a col. for each country found in the drop down list, and comments (which again will just reference what is found it the original table). The point will be that for each row, it will display the date originally inputted, a 1 or 0 for each country and finally the comments. Please refer to the image posted.
My issue is that whenever a new row is added to the original table, the new table doesn't take the change into account. I tried using formulas that spill the data but for some reason Spill isn't compatible with tables. The whole reason I need it in a table is so that I can export it in power query. I am planning on just using the Spill values, and running a VBA code that can take each row that contains data from that range and input that into a table (if that works).
Please let me know of any possible solutions!
Thanks in advance
