Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[TheBoy]

Bloody Excel, office tosser's tool. I'm rubbish at it. But the managers want their pretty pictures

Have a Worksheet with Date, Name, Type and a Yes/No columns.  To make it easier to clarify, lets pretend its OOF, and what sections current and future mods visit - so my worksheet may look like:

DATE    Name         Section      ActionDone
1/4/14 KevinWood  GenChat    Yes
6/4/14 TheBoy        GenChat   No
6/4/14 TheBoy        FAQ          No
8/4/14 MDTM          Newbie     Yes
9/4/14 Hotel21        Newbie     No
21/4/14 TheBoy      CarChat     Yes
5/5/14 Hotel21       GenChat     Yes
5/5/14 AA              GenChat     No


You get the idea

From this, I need to create the following columns:
Name
Number of times name appears
Number of times name appears with Action=Yes
Number of time name appears in last 30 days
Number of time name appears in last 30 days with Action=Yes

(I'll prob need to add additional time options). From this, I should be able to create the pretty graphs that mgmt will drowl over, but ultimately ignore what its telling them.

TIA for any pointers.

[TheBoy]

I'll add, I've currently got the names and type cells built as dropdowns from a Named List on another worksheet, so a) its consistent (but adjustable), and b) may be easier to use to build and calculations?

[tunnie]

Use Frequency?  Done similar, recall basic freq wizard did the job

[TheBoy]

Can you fire SQL commands into Excel - this has to be a standalone Workbook, not linking to any other files.

I could do it in SQL commands

[TheBoy]

Use Frequency?  Done similar, recall basic freq wizard did the job

There was some issue around doing the dates when I played earlier today, then I got sidetracked onto Pivot Tables, and lost 3 hours of my life without achieving anything useful.

[Bigron]

TB, can you tell me exactly what data you are needing to process, if it doesn't breach Company Confidentiality?
Excel and I are old enemies, with which I have battled often and won sometimes!
If you are able to send me the raw data I will do battle on your behalf and may even be able to end up with a pretty chart?
It would please me to be able to repay some of the great things OOF have done for me, as another member posted earlier.

Ron.

[TheBoy]

Its confidential, but the example above should give the idea...

[jimbobmccoy]

Sounds like the countif function is what you need.

Essentially counts number of times a value appears in a range if it meets your criteria.

I'll have a proper look a bit latter if still  needed.

Alternatively-script a query to import the excel in to a SQL DB on a schedule, then update the needed columns that exist in the table with the info you need and push out to csv, again scheduled.

You can then just set a macro to run text to columns, save as xls, and you're done.
Completely arse about face, but as I generally detest excel, it's stupid language and lack of ability to do what SQL does, I have done this before. (Albeit on a more complex requirement) (and yes, I'm choosing to completely ignore excel is not meant to be access, SQL or any other form of DB )

[TheBoy]

Importing into a DB is a no-go I'm afraid. That was the way I wanted to go.

[cem_devecioglu]

I'n not an excel expert ,  but  you can use visual basic macros in excel .please have a look at this page 


http://www.excel-easy.com/vba/workbook-worksheet-object.html


and this one
http://www.excel-vba.com/vba-prog-1-1-editor.htm


main page
http://www.excel-vba.com/excel-vba-contents.htm


ps: I would also transport it into sql 

[Gaffers]

You need to use the COUNTIF function

http://office.microsoft.com/en-001/excel-help/count-how-often-a-value-occurs-HP001127779.aspx

Edit:  you can nest the COUNTIFs with IF statements as required

[cem_devecioglu]

also within usual vb, you can create excel object ,  open the excel sheet and work with columns like any variable


just add the excel object class from objects/references then
Dim xl As New Excel.Application
Dim xlsheet As Excel.Worksheet
Dim xlwbook As Excel.Workbook
Private Sub Command1_Click()
Text1.Text = xlsheet.Cells(2, 1)
Text2.Text = xlsheet.Cells(2, 2)
xl.ActiveWorkbook.Close False, "c:\book1.xls"

xl.Quit
End Sub
Private Sub Command2_Click()
xlsheet.Cells(2, 1) = Text1.Text
xlsheet.Cells(2, 2) = Text2.Text
xlwbook.Save
xl.ActiveWorkbook.Close False, "c:\book1.xls"
xl.Quit
End Sub
Private Sub Form_Load()
Set xlwbook = xl.Workbooks.Open("c:\book1.xls")
Set xlsheet = xlwbook.Sheets.Item(1)
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set xlwbook = Nothing
Set xl = Nothing
End Sub

[jimbobmccoy]

Also, you may need the IF function, the countifs function and possibly nested IF's.
You can also use a countif as your true or false part of the if function.

So you could say, if my cell has value x then count how many yes's and if it doesn't return text "no value.

The thing to remember-it's excel so is almost always the opposite logic or natural inclination to SQL. And it's designed to screw with any sane persons brain
 

[jimbobmccoy]

You need to use the COUNTIF function

http://office.microsoft.com/en-001/excel-help/count-how-often-a-value-occurs-HP001127779.aspx

Edit:  you can nest the COUNTIFs with IF statements as required

I Must type faster!

[jimbobmccoy]

Importing into a DB is a no-go I'm afraid. That was the way I wanted to go.

Can you not stick sqlexpress on your own machine, create a one table DB and do it locally on that?

Will anyone mind as long as the output is right?

Also, from experience, management tend to want this type of report to grow-get more complex, analyse the same thing more times, and still not get what it's telling them. As this happens having the info in a proper DB would be a huge help