Get total from possible result sheet, in to pivot table… if criteria match

motilulla · Nov 25, 2015

Hello,

Example below the Sheet Source; shows as data received.

Book1

A

B

C

D

E

1

Year

EM

Count-1

Sum-1

Patterns-1

2

Year

EM

Count-1

Sum-1

Patterns-1

3

1975

5

80

2,1,1,1

4

1978

4

67

3,1

5

1980

5

80

1,3,1

6

1985

4

67

1,1,1,1

7

1985

5

80

2,1,1,1

8

1988

4

67

2,1,1

9

1990

4

67

1,1,1,1

10

1990

5

80

1,1,1,2

11

1992

3

36

1,2

12

1993

3

36

1,1,1

13

1993

5

80

2,2,1

14

1996

3

30

1,1,1

15

1998

4

67

1,1,2

16

1998

5

50

2,1,2

17

2000

3

21

2,1

18

2001

4

67

1,1,1,1

19

2001

5

80

1,1,3

20

2002

4

67

2,1,1

21

2003

3

36

2,1

22

2003

5

80

4,1

23

2003

5

80

3,1,1

24

2005

4

67

1,1,1,1

25

2006

4

28

3,1

26

2007

4

67

2,1,1

27

2008

4

67

1,2,1

28

2008

4

67

2,1,1

29

2010

3

18

2,1

30

2010

4

67

1,1,2

31

2011

5

80

1,1,2,1

32

2012

4

67

1,2,1

33

2012

5

80

3,2

34

2012

5

80

2,1,1,1

35

2013

3

30

1,1,1

36

2013

4

67

2,1,1

37

2013

5

80

1,2,1,1

38

2014

3

30

1,2

Source

Example below the Sheet Pivot Table; shows data result of 3 fields (Count-1, Sum-1, Patterns-1) of Source Sheet in to the pivot table.

Book1

A

B

C

D

1

2

3

Count

4

Count-1

Sum-1

Patterns-1

Total

5

3

18

2,1

1

6

Total 18

1

7

21

2,1

1

8

Total 21

1

9

30

1,1,1

2

10

1,2

1

11

Total 30

3

12

36

1,1,1

1

13

1,2

1

14

2,1

1

15

Total 36

3

16

Total 3

8

17

4

28

3,1

1

18

Total 28

1

19

67

1,1,1,1

4

20

1,1,2

2

21

1,2,1

2

22

2,1,1

5

23

3,1

1

24

Total 67

14

25

Total 4

15

26

5

50

2,1,2

1

27

Total 50

1

28

80

1,1,1,2

1

29

1,1,2,1

1

30

1,1,3

1

31

1,2,1,1

1

32

1,3,1

1

33

2,1,1,1

3

34

2,2,1

1

35

3,1,1

1

36

3,2

1

37

4,1

1

38

Total 80

12

39

Total 5

13

40

Total general

36

Pivot Table

Example below the Sheet Possible Results; shows all possible result could be formed with 3 fields (Count-1, Sum-1, Patterns-1).

Book1

A

B

C

D

1

2

3

Contar de Count-1

4

Count-1

Sum-1

Patterns-1

Total

5

3

18

1,2

1

6

3

18

2,1

1

7

3

21

3

1

8

3

21

1,1,1

1

9

3

21

2,1

1

10

3

30

3

1

11

3

30

1,1,1

3

12

3

30

1,2

1

13

3

30

2,1

2

14

3

36

1,1,1

5

15

3

36

1,2

2

16

3

36

2,1

3

17

Total 3

22

18

4

28

2,2

1

19

4

28

3,1

1

20

4

67

1,1,1,1

16

21

4

67

1,1,2

5

22

4

67

1,2,1

7

23

4

67

1,3

2

24

4

67

2,1,1

11

25

4

67

3,1

2

26

4

70

2,2

4

27

4

70

3,1

2

28

4

73

1,1,1,1

20

29

4

73

3,1

2

30

Total 4

73

31

5

50

5

1

32

5

50

1,3,1

1

33

5

50

2,1,2

1

34

5

50

2,2,1

1

35

5

50

3,1,1

2

36

5

50

4,1

1

37

5

80

5

1

38

5

80

1,1,1,1,1

7

39

5

80

1,1,1,2

3

40

5

80

1,1,2,1

5

41

5

80

1,1,3

2

42

5

80

1,2,1,1

9

43

5

80

1,2,2

2

44

5

80

1,3,1

5

45

5

80

2,1,1,1

12

46

5

80

2,1,2

4

47

5

80

2,2,1

6

48

5

80

2,3

1

49

5

80

3,1,1

7

50

5

80

3,2

1

51

5

80

4,1

1

52

Total 5

73

53

Total general

168

Possible Results

Here is what I need as shown in the Sheet Result Required in column F find total from; Sheet Possible Results
If 3 fields (Count-1, Sum-1, Patterns-1) criteria match as shown

Book1

A

B

C

D

E

F

1

2

3

Count

TT:Find In Sheet Possible Results

4

Count-1

Sum-1

Patterns-1

Total

TT:Find In Sheet Possible Results

5

3

18

2,1

1

6

Total 18

1

7

21

2,1

1

8

Total 21

1

9

30

1,1,1

2

3

10

1,2

1

11

Total 30

3

12

36

1,1,1

1

5

13

1,2

1

2

14

2,1

1

3

15

Total 36

3

16

Total 3

8

17

4

28

3,1

1

18

Total 28

1

19

67

1,1,1,1

4

16

20

1,1,2

2

5

21

1,2,1

2

7

22

2,1,1

5

11

23

3,1

1

2

24

Total 67

14

25

Total 4

15

26

5

50

2,1,2

1

27

Total 50

1

28

80

1,1,1,2

1

3

29

1,1,2,1

1

5

30

1,1,3

1

2

31

1,2,1,1

1

9

32

1,3,1

1

5

33

2,1,1,1

3

12

34

2,2,1

1

6

35

3,1,1

1

7

36

3,2

1

37

4,1

1

38

Total 80

12

39

Total 5

13

40

Total general

36

Result Required

Example Sheet Pivot Table; 3 fields (Count-1, A5 = 3… Sum-1, B5 = 18… Patterns-1, C5 = 2,1) look in to Sheet Possible Results if criteria match show total in column F5

Example Sheet Pivot Table; 3 fields (Count-1, A5 = 3… Sum-1, B7 = 21… Patterns-1, C7 = 2,1) look in to Sheet Possible Results if criteria match show total in column F7

Example Sheet Pivot Table; 3 fields (Count-1, A5 = 3… Sum-1, B9 = 30… Patterns-1, C9 = 1,1,1) look in to Sheet Possible Results if criteria match show total in column F9

Example Sheet Pivot Table; 3 fields (Count-1, A5 = 3… Sum-1, B9 = 30… Patterns-1, C10 = 1,2) look in to Sheet Possible Results if criteria match show total in column F10

Note; pivot table change every time data is filled in source sheet so I think VBA solution will be work great
It is just my thought.

I am using excel version 2000
Thank you in advance.

Regards,
Moti

motilulla · Nov 25, 2015

motilulla · Nov 25, 2015

motilulla said:
bump

bump

motilulla · Nov 25, 2015

Hello,

When I generate pivot table from my source sheet it looks like shown as below where in column A & B cells are empty.

Book1

A

B

C

D

1

2

3

Count

4

Count-1

Sum-1

Patterns-1

Total

5

3

18

2,1

1

6

Total 18

1

7

21

2,1

1

8

Total 21

1

9

30

1,1,1

2

10

1,2

1

11

Total 30

3

12

36

1,1,1

1

13

1,2

1

14

2,1

1

15

Total 36

3

16

Total 3

8

17

4

28

3,1

1

18

Total 28

1

19

67

1,1,1,1

4

20

1,1,2

2

21

1,2,1

2

22

2,1,1

5

23

3,1

1

24

Total 67

14

25

Total 4

15

26

5

50

2,1,2

1

27

Total 50

1

28

80

1,1,1,2

1

29

1,1,2,1

1

30

1,1,3

1

31

1,2,1,1

1

32

1,3,1

1

33

2,1,1,1

3

34

2,2,1

1

35

3,1,1

1

36

3,2

1

37

4,1

1

38

Total 80

12

39

Total 5

13

40

Total general

36

Pivot Table

Is there are any ways so pivot table look like this as shown with all cells filled.

Book1

A

B

C

D

1

2

3

Count

4

Count-1

Sum-1

Patterns-1

Total

5

3

18

2,1

1

6

3

Total 18

1

7

3

21

2,1

1

8

3

Total 21

1

9

3

30

1,1,1

2

10

3

30

1,2

1

11

3

Total 30

3

12

3

36

1,1,1

1

13

3

36

1,2

1

14

3

36

2,1

1

15

3

Total 36

3

16

Total 3

Total 36

8

17

4

28

3,1

1

18

4

Total 28

1

19

4

67

1,1,1,1

4

20

4

67

1,1,2

2

21

4

67

1,2,1

2

22

4

67

2,1,1

5

23

4

67

3,1

1

24

4

Total 67

14

25

Total 4

Total 67

15

26

5

50

2,1,2

1

27

5

Total 50

1

28

5

80

1,1,1,2

1

29

5

80

1,1,2,1

1

30

5

80

1,1,3

1

31

5

80

1,2,1,1

1

32

5

80

1,3,1

1

33

5

80

2,1,1,1

3

34

5

80

2,2,1

1

35

5

80

3,1,1

1

36

5

80

3,2

1

37

5

80

4,1

1

38

5

Total 80

12

39

Total 5

13

40

Total general

36

Pivot Table-Filled All Cells

Regards,
Moti

motilulla · Nov 26, 2015

Hello, may be is this possible

Original Data

Book1

A

B

C

D

E

2

Year

EM

Count-1

Sum-1

Patterns-1

3

1975

5

80

2,1,1,1

4

1978

4

67

3,1

5

1980

5

80

1,3,1

6

1985

4

67

1,1,1,1

7

1985

5

80

2,1,1,1

8

1988

4

67

2,1,1

9

1990

4

67

1,1,1,1

10

1990

5

80

1,1,1,2

11

1992

3

36

1,2

12

1993

3

36

1,1,1

13

1993

5

80

2,2,1

14

1996

3

30

1,1,1

15

1998

4

67

1,1,2

16

1998

5

50

2,1,2

17

2000

3

21

2,1

18

2001

4

67

1,1,1,1

19

2001

5

80

1,1,3

20

2002

4

67

2,1,1

21

2003

3

36

2,1

22

2003

5

80

4,1

23

2003

5

80

3,1,1

24

2005

4

67

1,1,1,1

25

2006

4

28

3,1

26

2007

4

67

2,1,1

27

2008

4

67

1,2,1

28

2008

4

67

2,1,1

29

2010

3

18

2,1

30

2010

4

67

1,1,2

31

2011

5

80

1,1,2,1

32

2012

4

67

1,2,1

33

2012

5

80

3,2

34

2012

5

80

2,1,1,1

35

2013

3

30

1,1,1

36

2013

4

67

2,1,1

37

2013

5

80

1,2,1,1

38

2014

3

30

1,2

Orignal Data

Macro can give result as shown in sheet2

Book1

A

B

C

D

1

2

3

Count-1

Sum-1

Patterns-1

Total

4

3

18

2,1

1

5

3

21

2,1

1

6

3

30

1,1,1

2

7

3

30

1,2

1

8

3

36

1,1,1

1

9

3

36

1,2

1

10

3

36

2,1

1

11

Total 3

8

12

4

28

3,1

1

13

4

67

1,1,1,1

4

14

4

67

1,1,2

2

15

4

67

1,2,1

2

16

4

67

2,1,1

5

17

4

67

3,1

1

18

Total 4

15

19

5

50

2,1,2

1

20

5

80

1,1,1,2

1

21

5

80

1,1,2,1

1

22

5

80

1,1,3

1

23

5

80

1,2,1,1

1

24

5

80

1,3,1

1

25

5

80

2,1,1,1

3

26

5

80

2,2,1

1

27

5

80

3,1,1

1

28

5

80

3,2

1

29

5

80

4,1

1

30

Total 5

13

31

Total general

36

Macro Resume Data

Thank you

Regards,
Moti

motilulla · Nov 26, 2015

motilulla said:
Hello, may be is this possible

Original Data

A B C D E
2,1,1,1
3,1
1,3,1
1,1,1,1
2,1,1,1
2,1,1
1,1,1,1
1,1,1,2
1,2
1,1,1
2,2,1
1,1,1
1,1,2
2,1,2
2,1
1,1,1,1
1,1,3
2,1,1
2,1
4,1
3,1,1
1,1,1,1
3,1
2,1,1
1,2,1
2,1,1
2,1
1,1,2
1,1,2,1
1,2,1
3,2
2,1,1,1
1,1,1
2,1,1
1,2,1,1
1,2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: #00FF00, align: center"]Year[/TD]
[TD="bgcolor: #000000, align: center"]EM[/TD]
[TD="bgcolor: #FFFF00, align: center"]Count-1[/TD]
[TD="bgcolor: #FF99CC, align: center"]Sum-1[/TD]
[TD="bgcolor: #FF0000, align: center"]Patterns-1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1975[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]1978[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]1980[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]1985[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1985[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1988[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]1990[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1990[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]1992[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]36[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]1993[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]36[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]1993[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]1996[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]30[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]1998[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]1998[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]50[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]2000[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]21[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]2001[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]2001[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]2002[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]2003[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]36[/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]2003[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]2003[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]2005[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]2006[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]28[/TD]

[TD="align: center"]26[/TD]
[TD="align: center"]2007[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]27[/TD]
[TD="align: center"]2008[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]28[/TD]
[TD="align: center"]2008[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]29[/TD]
[TD="align: center"]2010[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]18[/TD]

[TD="align: center"]30[/TD]
[TD="align: center"]2010[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]31[/TD]
[TD="align: center"]2011[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]32[/TD]
[TD="align: center"]2012[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]33[/TD]
[TD="align: center"]2012[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]34[/TD]
[TD="align: center"]2012[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]35[/TD]
[TD="align: center"]2013[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]30[/TD]

[TD="align: center"]36[/TD]
[TD="align: center"]2013[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]37[/TD]
[TD="align: center"]2013[/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]38[/TD]
[TD="align: center"]2014[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]30[/TD]

</tbody>
Orignal Data

Macro can give result as shown in sheet2

A B C D
Patterns-1
2,1
2,1
1,1,1
1,2
1,1,1
1,2
2,1
Total 3
3,1
1,1,1,1
1,1,2
1,2,1
2,1,1
3,1
Total 4
2,1,2
1,1,1,2
1,1,2,1
1,1,3
1,2,1,1
1,3,1
2,1,1,1
2,2,1
3,1,1
3,2
4,1
Total 5
Total general

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Count-1[/TD]
[TD="align: center"]Sum-1[/TD]

[TD="align: center"]Total[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]18[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]21[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]30[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]30[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]36[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]36[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]36[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]28[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]67[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"][/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]50[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]26[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]27[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]28[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]29[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]80[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]30[/TD]

[TD="align: center"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]31[/TD]

[TD="align: center"][/TD]

[TD="align: center"]36[/TD]

</tbody>
Macro Resume Data

Thank you

Regards,
Moti

bump

motilulla · Nov 26, 2015

Hello, I am trying to searching answer in Mr.Excel & Google using word “vba unique list” find many macro and tried all but do not find any what I want "List unique and give unique totals"?

Please any suggestion

Thank You

Regards
Moti

motilulla · Nov 26, 2015

motilulla · Nov 26, 2015

Hello, search all the day but I think there is a no VBA to complete the task what I want.

But yes may be possible if I change the idea that could work

VBA Fill Blanks in column A, B, C, D if find

Original

Book1

A

B

C

D

1

2

3

Count

4

Count-1

Sum-1

Patterns-1

Total

5

3

18

2,1

1

6

Total 18

1

7

21

2,1

1

8

Total 21

1

9

30

1,1,1

2

10

1,2

1

11

Total 30

3

12

36

1,1,1

1

13

1,2

1

14

2,1

1

15

Total 36

3

16

Total 3

8

17

4

28

3,1

1

18

Total 28

1

19

67

1,1,1,1

4

20

1,1,2

2

21

1,2,1

2

22

2,1,1

5

23

3,1

1

24

Total 67

14

25

Total 4

15

26

5

50

2,1,2

1

27

Total 50

1

28

80

1,1,1,2

1

29

1,1,2,1

1

30

1,1,3

1

31

1,2,1,1

1

32

1,3,1

1

33

2,1,1,1

3

34

2,2,1

1

35

3,1,1

1

36

3,2

1

37

4,1

1

38

Total 80

12

39

Total 5

13

40

Total general

36

Orignal

Result

Book1

A

B

C

D

1

2

3

Count

4

Count-1

Sum-1

Patterns-1

Total

5

3

18

2,1

1

6

3

Total 18

1

7

3

21

2,1

1

8

3

Total 21

1

9

3

30

1,1,1

2

10

3

30

1,2

1

11

3

Total 30

3

12

3

36

1,1,1

1

13

3

36

1,2

1

14

3

36

2,1

1

15

3

Total 36

3

16

Total 3

8

17

4

28

3,1

1

18

4

Total 28

1

19

4

67

1,1,1,1

4

20

4

67

1,1,2

2

21

4

67

1,2,1

2

22

4

67

2,1,1

5

23

4

67

3,1

1

24

4

Total 67

14

25

Total 4

15

26

5

50

2,1,2

1

27

5

Total 50

1

28

5

80

1,1,1,2

1

29

5

80

1,1,2,1

1

30

5

80

1,1,3

1

31

5

80

1,2,1,1

1

32

5

80

1,3,1

1

33

5

80

2,1,1,1

3

34

5

80

2,2,1

1

35

5

80

3,1,1

1

36

5

80

3,2

1

37

5

80

4,1

1

38

5

Total 80

12

39

Total 5

13

40

Total general

36

Result

Please Help
Thank you

Regards,
Moti

motilulla · Nov 26, 2015

Hello, I found macro AutoFill2 at the link below modified a range and work fine.
http://www.mrexcel.com/forum/excel-...rge-then-copying-blank-cells.html#post2785182

Code:

Sub AutoFill2()
Dim rng As Range
Dim c As Range
Dim lr As Integer
    lr = Cells(Rows.Count, 2).End(xlUp).Row
'Assign Formula in Range
    Range("A5:B" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
'Assign Range to work with
    Set rng = Range("A5:B" & lr)
'Assign Value inplace of formulas
    For Each c In rng
        c.Value = c.Value
    Next c
End Sub

Before Macro

Book1

A

B

C

D

1

2

3

Count

4

Count-1

Sum-1

Patterns-1

Total

5

3

18

2,1

1

6

Total 18

1

7

21

2,1

1

8

Total 21

1

9

30

1,1,1

2

10

1,2

1

11

Total 30

3

12

36

1,1,1

1

13

1,2

1

14

2,1

1

15

Total 36

3

16

Total 3

8

17

4

28

3,1

1

18

Total 28

1

19

67

1,1,1,1

4

20

1,1,2

2

21

1,2,1

2

22

2,1,1

5

23

3,1

1

24

Total 67

14

25

Total 4

15

26

5

50

2,1,2

1

27

Total 50

1

28

80

1,1,1,2

1

29

1,1,2,1

1

30

1,1,3

1

31

1,2,1,1

1

32

1,3,1

1

33

2,1,1,1

3

34

2,2,1

1

35

3,1,1

1

36

3,2

1

37

4,1

1

38

Total 80

12

39

Total 5

13

40

Total general

36

Before Macro

After Macro

Book1

A

B

C

D

1

2

3

Count

4

Count-1

Sum-1

Patterns-1

Total

5

3

18

2,1

1

6

3

Total 18

1

7

3

21

2,1

1

8

3

Total 21

1

9

3

30

1,1,1

2

10

3

30

1,2

1

11

3

Total 30

3

12

3

36

1,1,1

1

13

3

36

1,2

1

14

3

36

2,1

1

15

3

Total 36

3

16

Total 3

Total 36

8

17

4

28

3,1

1

18

4

Total 28

1

19

4

67

1,1,1,1

4

20

4

67

1,1,2

2

21

4

67

1,2,1

2

22

4

67

2,1,1

5

23

4

67

3,1

1

24

4

Total 67

14

25

Total 4

Total 67

15

26

5

50

2,1,2

1

27

5

Total 50

1

28

5

80

1,1,1,2

1

29

5

80

1,1,2,1

1

30

5

80

1,1,3

1

31

5

80

1,2,1,1

1

32

5

80

1,3,1

1

33

5

80

2,1,1,1

3

34

5

80

2,2,1

1

35

5

80

3,1,1

1

36

5

80

3,2

1

37

5

80

4,1

1

38

5

Total 80

12

39

Total 5

13

40

Total general

36

After Macro

Please need help can someone take a look so macro do not fill cells are highlighted in green will be great help

Thank you

Regards,
Moti

Get total from possible result sheet, in to pivot table… if criteria match

motilulla

Well-known Member

Excel Facts

motilulla

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

motilulla

Well-known Member

Similar threads

Forum statistics

Share this page

Get total from possible result sheet, in to pivot table… if criteria match

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock