How to automatically wrap scattered text into one column?

Question

excel

There are several sheets with a multi-line table, where each line contains textual information and which is assigned to some category, all this is scattered. How can I make it so that when writing these lines, on another sheet containing a table with columns that have the names of similar categories, the text from the previous sheets and into the columns corresponding to the names of the categories is automatically entered?

Answer 1

Dmitry, if I understand correctly, then expenses “arrive” on the first sheet and the category of these expenses is put down in the same place (or the expenses “arrive” already with a category). The table you wrote on the second sheet has column names that match the category names on the first sheet. On the screenshots, I see a discrepancy between the names of categories and columns (Debt-Debt and Health/Medicine-Medicine), so I added a condition to the formula to identify the category and its correspondence to the column name. If the categories and column names are exactly the same, then the formula will be even simpler and the conditions will not be needed. Also, on the screen of the first sheet, not all categories can be compared with the names of the columns on the second sheet, so in my example I add the "Food" column to show how expenses with categories, which have not been compared all the same can get into the table on the second sheet. And if expenses with categories: "Alcohol", "Gifts", etc. will need to be attributed, for example, to the "Entertainment" column on the second sheet, then you just need to add the appropriate condition in the formula (you really did not write about this in the problem statement). Check out my screenshot. And this is the formula itself (in cell G3): IF( OR( G$2=$C3; AND( NOT(ISERROR(SEARCH("*"&"Debt"&"*";G$2))); NOT(ISERROR( SEARCH("*"&"Debt"&"*";$C3))) ); AND( NOT(ISERROR(SEARCH("*"&"Medical"&"*";G$2))); NOT(ISERROR (SEARCH("*"&"Medic"&"*";$C3))) ) ); $B3; "" ) Check out my screenshot. And this is the formula itself (in cell G3): IF( OR( G$2=$C3; AND( NOT(ISERROR(SEARCH("*"&"Debt"&"*";G$2))); NOT(ISERROR( SEARCH("*"&"Debt"&"*";$C3))) ); AND( NOT(ISERROR(SEARCH("*"&"Medical"&"*";G$2))); NOT(ISERROR (SEARCH("*"&"Medic"&"*";$C3))) ) ); $B3; "" ) Check out my screenshot. And this is the formula itself (in cell G3): IF( OR( G$2=$C3; AND( NOT(ISERROR(SEARCH("*"&"Debt"&"*";G$2))); NOT(ISERROR( SEARCH("*"&"Debt"&"*";$C3))) ); AND( NOT(ISERROR(SEARCH("*"&"Medical"&"*";G$2))); NOT(ISERROR (SEARCH("*"&"Medic"&"*";$C3))) ) ); $B3; "" )