Geek asks and answers

Merge Excel and merge similar cells

I have a simple Excel spreadsheet, which is a daily updated list of counters. The sheet format is as follows:

A         B          C             D          E
date      name       computer      info       counter

Example:

A             B          C         D              E
04/04/2012    John       186       Workstation    4813
04/04/2012    Mary       181       Workstation    2273
04/05/2012    John       186       Workstation    4822
04/05/2012    Mary       181       Workstation    2274
04/06/2012    John       186       Workstation    4854
04/06/2012    Mary       181       Workstation    2275
04/06/2012    Bill       183       Workstation    3698

and son on ....

What I need to combine all these cells. So, for example, combine all the rows for each month and subtract the last column E from the first so that it looks like this in a new sheet.

A             B          C         D              E
April 2012    John       186       Workstation    41
April 2012    Mary       181       Workstation    2
April 2012    Bill       183       Workstation    0

etc. for each of them ... bearing in mind that it is updated daily and has many days, months and years.

I have no idea how to think about it at all, but I guess I need a macro. If someone has any help, it will be great!

+3
source share
2 answers

Assuming this is what you want to do

, , , . , :).

, , , .

( - ). , T, , , .

, . (, , 2012 , .)

F2

=IF(A2="","",MONTH(A2))

G2 ,

=IF(A2="","",YEAR(A2))

H2 ,

=IF(B2="","",B2&"|"&F2&"@"&G2)

"|" "@" , , , !! , , !

F2 H2 , .

, 8000 . 8000 , "8000" .

Q2 ,

=IF(ISERROR(INDEX($H$2:$H$8000,MATCH(0,COUNTIF($Q$1:Q1,$H$2:$H$8000),0))),"",INDEX($H$2:$H$8000,MATCH(0,COUNTIF($Q$1:Q1,$H$2:$H$8000),0)))

, , Ctrl + Shift + Enter. Q2 Enter Ctrl + Shift + Enter. . , , .

, K2,

=IF(OR(Q2=0,Q2=""),"",LEFT(Q2,FIND("|",Q2)-1))

P2

=IF(OR(Q2=0,Q2=""),"",VALUE(RIGHT(Q2,LEN(Q2)-(FIND("@",Q2)))))

O2

=IF(OR(Q2=0,Q2=""),"",VALUE(MID(Q2,FIND("|",Q2)+1,LEN(Q2)-LEN(K2)-LEN(P2)-2)))

J2

=IF(OR(Q2=0,Q2=""),"",TEXT(DATE(P2,O2,1),"mmm yyyy"))

L2

=IF(OR(Q2=0,Q2=""),"",INDEX($C$2:$C$8000,MATCH(K2,$B$2:$B$8000,0)))

M2

=IF(OR(Q2=0,Q2=""),"",INDEX($D$2:$D$8000,MATCH(K2,$B$2:$B$8000,0)))

N2 ,

=IF(OR(Q2=0,Q2=""),"",MAX(IF((($B$2:$B$8000=K2)*($F$2:$F$8000=O2)*($G$2:$G$8000=P2)),($E$2:$E$8000)))-MIN(IF((($B$2:$B$8000=K2)*($F$2:$F$8000=O2)*($G$2:$G$8000=P2)),($E$2:$E$8000))))

, , Ctrl + Shift + Enter. ,

J2 Q2, . №5 ( ).

, , . , "". , , .

!! , !! , ! , !:)

+2

4 (F to I) : ( 100 : !)

F --> =DATE(YEAR(A1), MONTH(A1), 1)

G --> =MIN(IF(($B$1:$B$100=$B1)*($C$1:$C$100=$C1)*($D$1:$D$100=$D1)*($F$1:$F$100=$F1),$E$1:$E$100)) [press CTRL+SHIFT+ENTER (this is an array formula)]

H --> =MAX(IF(($B$1:$B$100=$B1)*($C$1:$C$100=$C1)*($D$1:$D$100=$D1)*($F$1:$F$100=$F1),$E$1:$E$100)) [press CTRL+SHIFT+ENTER (this is an array formula)]

I --> =H1-G1

( , )

:

A             B          C         D              E       F                              G                              H                              I
04/04/2012    John       186       Workstation    4813    =DATE(YEAR(A1), MONTH(A1), 1)  {=MIN(IF(($B$1:$B$100=$B1)...  {=MAX(IF(($B$1:$B$100=$B1)...  =H1-G1
04/04/2012    Mary       181       Workstation    2273    =DATE(YEAR(A2), MONTH(A2), 1)  {=MIN(IF(($B$1:$B$100=$B2)...  {=MAX(IF(($B$1:$B$100=$B2)...  =H2-G2
04/05/2012    John       186       Workstation    4822    =DATE(YEAR(A3), MONTH(A3), 1)  {=MIN(IF(($B$1:$B$100=$B3)...  {=MAX(IF(($B$1:$B$100=$B3)...  =H3-G3
04/05/2012    Mary       181       Workstation    2274    =DATE(YEAR(A4), MONTH(A4), 1)  {=MIN(IF(($B$1:$B$100=$B4)...  {=MAX(IF(($B$1:$B$100=$B4)...  =H4-G4
04/06/2012    John       186       Workstation    4854    =DATE(YEAR(A5), MONTH(A5), 1)  {=MIN(IF(($B$1:$B$100=$B5)...  {=MAX(IF(($B$1:$B$100=$B5)...  =H5-G5
04/06/2012    Mary       181       Workstation    2275    =DATE(YEAR(A6), MONTH(A6), 1)  {=MIN(IF(($B$1:$B$100=$B6)...  {=MAX(IF(($B$1:$B$100=$B6)...  =H6-G6
04/06/2012    Bill       183       Workstation    3698    =DATE(YEAR(A7), MONTH(A7), 1)  {=MIN(IF(($B$1:$B$100=$B7)...  {=MAX(IF(($B$1:$B$100=$B7)...  =H7-G7

:

A             B          C         D              E       F              G        H        I
04/04/2012    John       186       Workstation    4813    04/01/2012     4813     4854     41
04/04/2012    Mary       181       Workstation    2273    04/01/2012     2273     2275     2
04/05/2012    John       186       Workstation    4822    04/01/2012     4813     4854     41
04/05/2012    Mary       181       Workstation    2274    04/01/2012     2273     2275     2
04/06/2012    John       186       Workstation    4854    04/01/2012     4813     4854     41
04/06/2012    Mary       181       Workstation    2275    04/01/2012     2273     2275     2
04/06/2012    Bill       183       Workstation    3698    04/01/2012     3698     3698     0

. , , , Sheet1, Sheet2 :

A           B            C           D           E
=Sheet1!F1  =Sheet1!B1   =Sheet1!C1  =Sheet1!D1  =Sheet1!I1
=Sheet1!F2  =Sheet1!B2   =Sheet1!C2  =Sheet1!D2  =Sheet1!I2
...

:

A             B          C         D              E
04/01/2012    John       186       Workstation    41
04/01/2012    Mary       181       Workstation    2
04/01/2012    John       186       Workstation    41
04/01/2012    Mary       181       Workstation    2
04/01/2012    John       186       Workstation    41
04/01/2012    Mary       181       Workstation    2
04/01/2012    Bill       183       Workstation    0

, Sheet2:

  • A "custom" /mmmm yyyy, (.. April 2012 04/01/2012)

  • , : , .

0

More articles:


All Articles