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!

hiker95 · Apr 29, 2014

dataman001,

I assume that the raw data worksheet name is Sheet1.

If the raw data worksheet has a different name, you can change the macro code:

Change "Sheet1" to the actual worksheet name.

Rich (BB code):

Set w1 = Sheets("Sheet1")

Sample raw data:

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

After the macro a new worksheet YTD Summary will be created, or, cleared, and the results will then be created:

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

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.

Rich (BB code):

Sub UpdateYTDSummary()
' 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 UpdateYTDSummary macro.

dataman001 · Apr 29, 2014

hiker95 said:
dataman001,

Last try:

What is the actual worksheet name that contains the below raw data?

Its a test file I import called Elem_Year-To-Date_Attendance427.xls

Am I missing somethign here? Whats the sygnificance of what the file is called unless you are looking for it someplace. Should I have uploaded a good part of it or something?

hiker95 · Apr 29, 2014

dataman001,

I did not ask for the file name.

I asked for the worksheet name that contains the raw data, in my reply #11.

dataman001 · Apr 29, 2014

Now I undersatand - don't usually name each worksheet so maybe thats why the macro got a run time error.
I did highlight the data and created the name ELM_YTD

hiker95 · Apr 29, 2014

dataman001,

I did highlight the data and created the name ELM_YTD

What you replied with sounds like you create range name?????

A workbook/spreadsheet contains worksheets/sheets.

So, the raw data is in sheet name is ELM_YTD?

dataman001 · Apr 29, 2014

hiker95 said:
dataman001,

What you replied with sounds like you create range name?????

A workbook/spreadsheet contains worksheets/sheets.

So, the raw data is in sheet name is ELM_YTD?

Yes I did create a range name. Can you tell I am quite rusty at this? Took on the project as a volunteer thing hoping I could help but have fallen on my sord so far.

The name (on the bottom tab) Is called Elem_Year-To-Date_Attendance427
I think that is what you are reffering to.

hiker95 · Apr 29, 2014

dataman001,

1. So, this is what your raw data worksheet looks like (see the sheet name below the screenshot)?

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

2. Is the sheet name always the same?

dataman001 · Apr 29, 2014

No the date 427 would change weekly although if its helpful I could just drop the numeric characters at the end since the overall spreadsheet tytle indicated the date.

hiker95 · Apr 29, 2014

dataman001,

Please answer all questions.

1. Can I assume that the active workbook/spreadsheet only contains one worksheet/sheet?

2. The active workbook/spreadsheet worksheet/sheet name begins with Elem_Year-To-Date_Attendance, and, ends with the date, like 427?

In order to not have to change the formulae that do the counting, I will save the active worksheet/sheet name in a variable.

Then I will change the active worksheet/sheet name to Sheet1.

And, the macro will then create worksheet/sheet YTD Summary, and, add the data, and, do the calculations.

Then the macro will replace the worksheet/sheet name of Sheet1 back to its original name.

I have already tested the above idea, and, it does work correctly.

3. Is the above acceptable to you?

dataman001 · Apr 29, 2014

1. Yes
2. OK I changed the name of the tab that containes the data to variable
Then moved to Sheet1 and followed your instructions from post 11
And I get another tab called YTD Summary but is blank - no data
3. what am I doing wrong not to get the resultant data?

I will start off with a perfectly clean spreadsheet and import the data and try this again just to be sure I haven't goofed something up. Give me some time now.
Thanks! Ray

Data analysis - thought it shoud be easy!

dataman001

New Member

hiker95

Well-known Member

Excel Facts

dataman001

New Member

hiker95

Well-known Member

dataman001

New 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

Well-known Member

Excel Facts

New Member

Well-known Member

New 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