My simplify data format is as follows:
Material types: material code inventory
A M01 1
A M01 2
A M02 2
A M02 3
B B01 2
B B01 4
B B01 1
B B03 5
B B03 5
Using EXCEL pivot table (material categories + material code summary), get the following format:
Material types: material code inventory
A M01 3
M02 5
B B01 7
B03 10

My question is: I hope can get the data like SQL group by statement, material categories blank lines can also show the corresponding data, and the other table to do vlookup
, they don't have to manually to supplement the blank grid data of "material types", because like this column, and a lot of data, to supplement the blank after each summary grid is too much trouble,

Hope to get the data as follows:
Material types: material code inventory
A M01 3
A M02 5
B B01 7
B B03 10

CodePudding user response:

If not merge cells, supplement the very simple, screening of "blank", pull it equals a cell; If it is merged, may be in trouble, either do it brush with format rather than "merge cells" button (i.e. the longest merge cells to brush of A or B), or may be to build A simple auxiliary columns,

CodePudding user response:

reference 1/f, zara's reply:

if not merge cells, supplement the very simple, screening of "blank", pull it equals a cell; If it is merged, may be in trouble, either do it brush with format rather than "merge cells" button (i.e. the longest merge cells to brush of A or B), or may be to build A simple auxiliary columns,

Thank you for your reply, thousands of rows in a table data, which got the hundreds of "material types", as you say, manual write formula, or make some auxiliary column, table, occasionally with a fine, but often deal with such tables and data, will feel very trouble, is hope in the summary, will "material types" can display directly in each row, is very convenient,

If can like SQL statement: select the material types, material code, the sum (stock) from material list group by material types, material code to get the result? Go to do other correlation processing is very convenient, look up some post didn't find baidu, just don't know can realize?