Has a large Excel spreadsheets, inside has three columns of data need according to the first column and the second column now summarize the contents of the third column of the data into a line, as shown in the graphic

In the form before


After operating table


Because the data volume is too big, manual operation is too much trouble, also error-prone, excuse me what method???????


thank you

CodePudding user response:

There are two ways to achieve, is to write a formula, but need more step, the second is to use data perspective, data ranked first, then copy and paste,

CodePudding user response:

Problem has been solved, by setting the auxiliary of the two columns of data processing,
Among them, the D column shall be carried out in accordance with the requirements for data aggregation, each separated by a space behind a name, formula for=the IF (AND B1=(A1=A2, B2), D1 & amp;" "& amp; C2, C2),
E column to check the data, IF district, street, the same cell is 0, IF different, is set to 1, so the final cell is 1 row only picked up to meet the requirements, the formula for=the IF (AND (A3=A2, B2=B3), 0, 1),
Also added a secondary column, column is used to count, F that facilitate the final statistics, also can need not, statistics or relatively easy formula for=LEN (D2) - LEN (SUBSTITUTE (D2, ""," ")) + 1
The end result is as follows:

CodePudding user response:

Too bad, the function study

CodePudding user response:

This is too practical