Data analysis - thought it shoud be easy!

dataman001 · Apr 28, 2014

I import data into a spread sheet. it has the following columns NUMBER, NAME, GRADE, DATE and CODE.

I am attempting to produce a report that will list theNUMBER, NAME, GRADE and then the count of specific (4) codes from the code column and a second column counting (7) different codes from the code column.

Sounds simple but so far I have failed (even tried it Access).

I thought Pivotal tables would do it but I can get one count column but not the second one. Plus it doesn't count the different specified codes - only lists them.

Thanks in advance for any hellp!

dataman001 · Apr 29, 2014

OK started from scratch and followed all the steps. Did get a new Tab tytled YTD Summary but also Got an error and it highlighted the following line of code:

w1.Columns("A:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Columns("A:C"), Unique:=True

hiker95 · Apr 29, 2014

dataman001,

If the following does not work for you, then, I will have to see your actual raw data workbook/spreadsheet.

Sample raw data sheet to start:

Excel 2007

A

B

C

D

E

1

Number

Name

Grade

Date

Code

2

138716

Alarid, Norah Jaylin

5

9/23/2013

AE

3

138716

Alarid, Norah Jaylin

5

12/20/2013

AE

4

138716

Alarid, Norah Jaylin

5

8/21/2013

L

5

138716

Alarid, Norah Jaylin

5

8/29/2013

L

6

138716

Alarid, Norah Jaylin

5

11/4/2013

L

7

138716

Alarid, Norah Jaylin

5

11/8/2013

L

8

138716

Alarid, Norah Jaylin

5

3/6/2014

L

9

138716

Alarid, Norah Jaylin

5

3/14/2014

L

10

138716

Alarid, Norah Jaylin

5

4/7/2014

L

11

138716

Alarid, Norah Jaylin

5

8/30/2013

TE

12

138716

Alarid, Norah Jaylin

5

12/10/2013

TE

13

137457

Aleman, Kimberly Guadalupe

5

11/12/2013

A

14

137457

Aleman, Kimberly Guadalupe

5

12/12/2013

A

15

137457

Aleman, Kimberly Guadalupe

5

1/15/2014

A

16

137457

Aleman, Kimberly Guadalupe

5

10/29/2013

AE

17

137457

Aleman, Kimberly Guadalupe

5

10/30/2013

AE

18

137457

Aleman, Kimberly Guadalupe

5

11/6/2013

AE

19

137457

Aleman, Kimberly Guadalupe

5

11/7/2013

AE

20

137457

Aleman, Kimberly Guadalupe

5

11/8/2013

AE

21

137457

Aleman, Kimberly Guadalupe

5

9/30/2013

L

22

137457

Aleman, Kimberly Guadalupe

5

1/10/2014

TE

23

135341

Anderson, Savannah Rose

5

8/7/2013

A

24

135341

Anderson, Savannah Rose

5

12/5/2013

A

25

135341

Anderson, Savannah Rose

5

8/13/2013

AE

26

135341

Anderson, Savannah Rose

5

9/17/2013

L

27

135341

Anderson, Savannah Rose

5

12/17/2013

L

28

135341

Anderson, Savannah Rose

5

9/26/2013

TE

29

135341

Anderson, Savannah Rose

5

2/21/2014

TE

30

138705

Archuleta, Adam Elijah

5

12/16/2013

L

31

138705

Archuleta, Adam Elijah

5

3/4/2014

L

32

138705

Archuleta, Adam Elijah

5

3/6/2014

L

33

Elem_Year-To-Date_Attendance427

As the macro begins, the sheet name is changed:

Excel 2007

A

B

C

D

E

1

Number

Name

Grade

Date

Code

2

138716

Alarid, Norah Jaylin

5

9/23/2013

AE

3

138716

Alarid, Norah Jaylin

5

12/20/2013

AE

4

138716

Alarid, Norah Jaylin

5

8/21/2013

L

5

138716

Alarid, Norah Jaylin

5

8/29/2013

L

6

138716

Alarid, Norah Jaylin

5

11/4/2013

L

7

138716

Alarid, Norah Jaylin

5

11/8/2013

L

8

138716

Alarid, Norah Jaylin

5

3/6/2014

L

9

138716

Alarid, Norah Jaylin

5

3/14/2014

L

10

138716

Alarid, Norah Jaylin

5

4/7/2014

L

11

138716

Alarid, Norah Jaylin

5

8/30/2013

TE

12

138716

Alarid, Norah Jaylin

5

12/10/2013

TE

13

137457

Aleman, Kimberly Guadalupe

5

11/12/2013

A

14

137457

Aleman, Kimberly Guadalupe

5

12/12/2013

A

15

137457

Aleman, Kimberly Guadalupe

5

1/15/2014

A

16

137457

Aleman, Kimberly Guadalupe

5

10/29/2013

AE

17

137457

Aleman, Kimberly Guadalupe

5

10/30/2013

AE

18

137457

Aleman, Kimberly Guadalupe

5

11/6/2013

AE

19

137457

Aleman, Kimberly Guadalupe

5

11/7/2013

AE

20

137457

Aleman, Kimberly Guadalupe

5

11/8/2013

AE

21

137457

Aleman, Kimberly Guadalupe

5

9/30/2013

L

22

137457

Aleman, Kimberly Guadalupe

5

1/10/2014

TE

23

135341

Anderson, Savannah Rose

5

8/7/2013

A

24

135341

Anderson, Savannah Rose

5

12/5/2013

A

25

135341

Anderson, Savannah Rose

5

8/13/2013

AE

26

135341

Anderson, Savannah Rose

5

9/17/2013

L

27

135341

Anderson, Savannah Rose

5

12/17/2013

L

28

135341

Anderson, Savannah Rose

5

9/26/2013

TE

29

135341

Anderson, Savannah Rose

5

2/21/2014

TE

30

138705

Archuleta, Adam Elijah

5

12/16/2013

L

31

138705

Archuleta, Adam Elijah

5

3/4/2014

L

32

138705

Archuleta, Adam Elijah

5

3/6/2014

L

33

Sheet1

Then the macro creates sheet YTD Summary with the results:

Excel 2007

A

B

C

D

E

1

Number

Name

Grade

Tardy

Absence

2

138716

Alarid, Norah Jaylin

5

9

2

3

137457

Aleman, Kimberly Guadalupe

5

2

8

4

135341

Anderson, Savannah Rose

5

4

3

5

138705

Archuleta, Adam Elijah

5

3

0

6

YTD Summary

Then the original/first sheet is renamed:

Excel 2007

A

B

C

D

E

1

Number

Name

Grade

Date

Code

2

138716

Alarid, Norah Jaylin

5

9/23/2013

AE

3

138716

Alarid, Norah Jaylin

5

12/20/2013

AE

4

138716

Alarid, Norah Jaylin

5

8/21/2013

L

5

138716

Alarid, Norah Jaylin

5

8/29/2013

L

6

138716

Alarid, Norah Jaylin

5

11/4/2013

L

7

138716

Alarid, Norah Jaylin

5

11/8/2013

L

8

138716

Alarid, Norah Jaylin

5

3/6/2014

L

9

138716

Alarid, Norah Jaylin

5

3/14/2014

L

10

138716

Alarid, Norah Jaylin

5

4/7/2014

L

11

138716

Alarid, Norah Jaylin

5

8/30/2013

TE

12

138716

Alarid, Norah Jaylin

5

12/10/2013

TE

13

137457

Aleman, Kimberly Guadalupe

5

11/12/2013

A

14

137457

Aleman, Kimberly Guadalupe

5

12/12/2013

A

15

137457

Aleman, Kimberly Guadalupe

5

1/15/2014

A

16

137457

Aleman, Kimberly Guadalupe

5

10/29/2013

AE

17

137457

Aleman, Kimberly Guadalupe

5

10/30/2013

AE

18

137457

Aleman, Kimberly Guadalupe

5

11/6/2013

AE

19

137457

Aleman, Kimberly Guadalupe

5

11/7/2013

AE

20

137457

Aleman, Kimberly Guadalupe

5

11/8/2013

AE

21

137457

Aleman, Kimberly Guadalupe

5

9/30/2013

L

22

137457

Aleman, Kimberly Guadalupe

5

1/10/2014

TE

23

135341

Anderson, Savannah Rose

5

8/7/2013

A

24

135341

Anderson, Savannah Rose

5

12/5/2013

A

25

135341

Anderson, Savannah Rose

5

8/13/2013

AE

26

135341

Anderson, Savannah Rose

5

9/17/2013

L

27

135341

Anderson, Savannah Rose

5

12/17/2013

L

28

135341

Anderson, Savannah Rose

5

9/26/2013

TE

29

135341

Anderson, Savannah Rose

5

2/21/2014

TE

30

138705

Archuleta, Adam Elijah

5

12/16/2013

L

31

138705

Archuleta, Adam Elijah

5

3/4/2014

L

32

138705

Archuleta, Adam Elijah

5

3/6/2014

L

33

Elem_Year-To-Date_Attendance427

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Sub UpdateYTDSummaryV3()
' hiker95 04/29/2014, ME774163
Dim w1 As Worksheet, wy As Worksheet
Dim lr As Long, asn As String
Application.ScreenUpdating = False
asn = ActiveSheet.Name
ActiveSheet.Name = "Sheet1"
Set w1 = Sheets("Sheet1")
If Not Evaluate("ISREF('YTD Summary'!A1)") Then Worksheets.Add(After:=w1).Name = "YTD Summary"
Set wy = Sheets("YTD Summary")
With wy
  .UsedRange.Clear
  w1.Columns("A:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Columns("A:C"), Unique:=True
  .Cells(1, 4).Resize(, 2).Value = Array("Tardy", "Absence")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  With .Range("D2:D" & lr)
    .Formula = "=COUNTIFS(Sheet1!A:A,A2,Sheet1!E:E,""L"")+COUNTIFS(Sheet1!A:A,A2,Sheet1!E:E,""T*"")"
    .Value = .Value
  End With
  With .Range("E2:E" & lr)
    .Formula = "=COUNTIFS(Sheet1!A:A,A2,Sheet1!E:E,""A*"")+COUNTIFS(Sheet1!A:A,A2,Sheet1!E:E,""D*"")+COUNTIFS(Sheet1!A:A,A2,Sheet1!E:E,""OSS"")"
    .Value = .Value
  End With
  .Columns("A:E").AutoFit
End With
w1.Name = asn
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the UpdateYTDSummaryV3 macro.

dataman001 · Apr 29, 2014

OK I opened a new worksheet - imported the data - added names to columns - named the tab Variable - Saved spread sheet as Elem_Year-To-Date_Attendance427.xlsm - moved to sheet1 and followed the directions to insert the macro - closed it and envoked it - got a Run Time Error '1004' "The command could not be completed by using the range specified. Select a single cell within the range and try the command again."
Debug highlights the following line: w1.Columns("A:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Columns("A:C"), Unique:=True

But I do get a new tab called YTD Summary but its balnk

hiker95 · Apr 29, 2014

dataman001,

OK I opened a new worksheet - imported the data - added names to columns - named the tab Variable - Saved spread sheet as Elem_Year-To-Date_Attendance427.xlsm - moved to sheet1 and followed the directions to insert the macro - closed it and envoked it - got a Run Time Error '1004' "The command could not be completed by using the range specified. Select a single cell within the range and try the command again."
Debug highlights the following line: w1.Columns("A:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Columns("A:C"), Unique:=True

The only part of what is in the above quote that is correct is:
OK I opened a new worksheet - imported the data

You can save the workbook/spreadsheet as Elem_Year-To-Date_Attendance427.xlsm, but:

From prior instructions/directions the sheet name was/is Elem_Year-To-Date_Attendance427

We are having a major communication problem.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

hiker95 · Apr 29, 2014

dataman001,

The only way that this will get straightened out is for you to supply your actual raw data workbook/spreadsheet after the imported the data manual part. Do not change anything.

You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing
and provide us with a link to your workbook.

dataman001 · Apr 29, 2014

OK I have it available for you at: https://app.box.com/s/7jannmfae0cjxo7tbrrd

hiker95 · Apr 29, 2014

dataman001,

Thanks for the workbook.

Forget all prior instructions.

The raw data sheet name, as seen below this line of text, has to be Sheet1.

Not all 9,816 rows are shown.

Excel 2007

A

B

C

D

E

1

Number

Name

Grade

Date

Code

2

133067

Montour, Ramon Vicente

5

9/25/2013

L

3

133067

Montour, Ramon Vicente

5

9/30/2013

DTH

4

133067

Montour, Ramon Vicente

5

11/4/2013

L

5

133067

Montour, Ramon Vicente

5

2/18/2014

A

6

133067

Montour, Ramon Vicente

5

2/25/2014

L

7

133067

Montour, Ramon Vicente

5

3/4/2014

L

8

132413

Ringlero, Timothy Elijah

5

8/30/2013

TE

9

132413

Ringlero, Timothy Elijah

5

9/12/2013

TU

10

132413

Ringlero, Timothy Elijah

5

10/7/2013

L

11

132413

Ringlero, Timothy Elijah

5

11/21/2013

L

12

132413

Ringlero, Timothy Elijah

5

12/5/2013

AE

13

132413

Ringlero, Timothy Elijah

5

12/11/2013

L

14

132413

Ringlero, Timothy Elijah

5

1/9/2014

L

15

132413

Ringlero, Timothy Elijah

5

1/13/2014

AE

16

132413

Ringlero, Timothy Elijah

5

2/11/2014

TU

17

132413

Ringlero, Timothy Elijah

5

2/18/2014

TU

18

132413

Ringlero, Timothy Elijah

5

3/4/2014

TU

19

132413

Ringlero, Timothy Elijah

5

3/5/2014

AU

20

132413

Ringlero, Timothy Elijah

5

3/6/2014

TU

21

132413

Ringlero, Timothy Elijah

5

3/11/2014

T

22

132413

Ringlero, Timothy Elijah

5

3/13/2014

TU

23

132413

Ringlero, Timothy Elijah

5

3/14/2014

AU

24

132413

Ringlero, Timothy Elijah

5

4/3/2014

TE

25

132413

Ringlero, Timothy Elijah

5

4/8/2014

TU

26

132413

Ringlero, Timothy Elijah

5

4/9/2014

L

27

132413

Ringlero, Timothy Elijah

5

4/16/2014

AE

28

132413

Ringlero, Timothy Elijah

5

4/17/2014

AE

29

132413

Ringlero, Timothy Elijah

5

4/18/2014

A

30

134696

Montez, Bleu Alice

5

9/26/2013

L

31

134696

Montez, Bleu Alice

5

10/1/2013

TE

32

134696

Montez, Bleu Alice

5

11/1/2013

L

33

134696

Montez, Bleu Alice

5

11/5/2013

L

34

134696

Montez, Bleu Alice

5

11/6/2013

OF

35

134696

Montez, Bleu Alice

5

11/13/2013

AU

36

134696

Montez, Bleu Alice

5

12/20/2013

AU

37

134696

Montez, Bleu Alice

5

3/17/2014

AU

38

134696

Montez, Bleu Alice

5

4/1/2014

TE

Sheet1

After the macro in a new sheet, YTD Summary, not all 599 rows are shown:

Excel 2007

A

B

C

D

E

1

Number

Name

Grade

Tardy

Absence

2

133067

Montour, Ramon Vicente

5

4

2

3

132413

Ringlero, Timothy Elijah

5

15

7

4

134696

Montez, Bleu Alice

5

3

YTD Summary

Please print the following, and, follow the instructions exactly.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:

Sub UpdateYTDSummaryV4()
' hiker95 04/29/2014, ME774163
Dim w1 As Worksheet, wy As Worksheet
Dim lr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
If Not Evaluate("ISREF('YTD Summary'!A1)") Then Worksheets.Add(After:=w1).Name = "YTD Summary"
Set wy = Sheets("YTD Summary")
With wy
  .UsedRange.Clear
  w1.Columns("A:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Columns("A:C"), Unique:=True
  .Cells(1, 4).Resize(, 2).Value = Array("Tardy", "Absence")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  With .Range("D2:D" & lr)
    .Formula = "=COUNTIFS(Sheet1!A:A,A2,Sheet1!E:E,""L"")+COUNTIFS(Sheet1!A:A,A2,Sheet1!E:E,""T*"")"
    .Value = .Value
  End With
  With .Range("E2:E" & lr)
    .Formula = "=COUNTIFS(Sheet1!A:A,A2,Sheet1!E:E,""A*"")+COUNTIFS(Sheet1!A:A,A2,Sheet1!E:E,""D*"")+COUNTIFS(Sheet1!A:A,A2,Sheet1!E:E,""OSS"")"
    .Value = .Value
  End With
  .Columns("A:E").AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the UpdateYTDSummaryV4 macro.

dataman001 · Apr 30, 2014

Did exactly as you instructed.
On your step 7 after ALT+Q keys are depressed I return to a blank Sheet1 (is that correct?) Then when I envoke the macro press ALT + F8
I see the MACRO Name To choose (UpdateYTDSummaryV4) double ckick it to run the macroThen the runtime error appears.
I do get a YTD Summary tab but it has no data.

I still get a runtime error '1004'

The debug code line is this line:
w1.Columns("A:C").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Columns("A:C"), Unique:=True

hiker95 · Apr 30, 2014

dataman001,

On your step 7 after ALT+Q keys are depressed I return to a blank Sheet1 (is that correct?)

NO.

OK I opened a new worksheet - imported the data - added names to columns - named the tab Sheet1

Then, after the import, you run the macro.

After four macros, and, many screenshots, you are still not following my instructions correctly.

I strongly suggest that you take an Excel training course that teaches how to use macros.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

dataman001 · Apr 30, 2014

Data analysis - thought it shoud be easy!

dataman001

New Member

dataman001

New Member

Excel Facts

hiker95

Well-known Member

dataman001

New Member

hiker95

Well-known Member

hiker95

Well-known Member

dataman001

New Member

hiker95

Well-known Member

dataman001

New Member

hiker95

Well-known Member

dataman001

New Member

Similar threads

Forum statistics

Share this page

Data analysis - thought it shoud be easy!

New Member

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New 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