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

[Andy H]

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.

Been there. 2 years of hourly readings from the heat meters in a block of 90 flats.

I did manage to get some answers but it wasn't pretty.

I considered saving all the data in a csv file and writing something to suck it all into a mysql or sqlite database but never got around to it.

[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

I Must type faster!

 

I may or may not have spent a whole year in my yoof doing nothing but excel and VBA    (which is the most convoluted way to go about this problem)

[cem_devecioglu]

on complex scenarios using excel built in functions will get more complicated

[Gaffers]

on complex scenarios using excel built in functions will get more complicated

True, but this is not complicated for Excel.  In fact it is exactly what is was designed for?

[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!

 

I may or may not have spent a whole year in my yoof doing nothing but excel and VBA    (which is the most convoluted way to go about this problem)

I'm currently being tormented by reports for work that are a combo of excel, VBA and tying it together with macro scheduler to achieve some level of automation. Convoluted isn't the half of it!
I try to convince myself it's all good for my 'personal development' but when 64bit excel just decides to stop liking existing VB, it's testing. (Apparently a known issue and why Microsoft recommend using the 32 bit version unless absolutely necessary )

[Gaffers]

Ok, because I am a nice chap, here we go

Number of times name appears - =COUNTIF(C2:C16, "matt")  where C2:C16 is the range of cells and "matt" is the name
Number of times name appears with Action=Yes - =SUMPRODUCT(--(C2:C16="matt"),--(D2:D16="yes"))
Number of time name appears in last 30 days - =SUMPRODUCT(--(C2:C16="matt"),--(E2:E16<(TODAY()-30)))
Number of time name appears in last 30 days with Action=Yes =SUMPRODUCT(--(C2:C16="matt"),--(D2:D16="yes"),--(E2:E16<(TODAY()-30)))

[Gaffers]

Now give me a real challenge

[cem_devecioglu]

on complex scenarios using excel built in functions will get more complicated

True, but this is not complicated for Excel.  In fact it is exactly what is was designed for?

as the murphy rules says it will always get complicated as time goes by

[cem_devecioglu]

Now give me a real challenge

believe me I can

[TheBoy]

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

It has to be self contained and independent of anything else. You're preaching to the converted about databases, but that's not an option.

To really add insult, the data has to be updatable via Excel Webapp, as found on whatever Skydrive is called this week, and ShitPoint.

Grrrr

[TheBoy]

Ok, because I am a nice chap, here we go

Number of times name appears - =COUNTIF(C2:C16, "matt")  where C2:C16 is the range of cells and "matt" is the name
Number of times name appears with Action=Yes - =SUMPRODUCT(--(C2:C16="matt"),--(D2:D16="yes"))
Number of time name appears in last 30 days - =SUMPRODUCT(--(C2:C16="matt"),--(E2:E16<(TODAY()-30)))
Number of time name appears in last 30 days with Action=Yes =SUMPRODUCT(--(C2:C16="matt"),--(D2:D16="yes"),--(E2:E16<(TODAY()-30)))

I'm failing at a hurdle before - I have a list of names, but this list will change over time, as will the number of names in the list.

[TheBoy]

Instead of ="matt", can I have =NameList!A2 instead, would that resolve it? (assume name was stored in NameList!A2) ?

Then with an IF to ensure that NameList!A2 is valid

[Gaffers]

You can reference a single cell easily, but if you wanted to do a number of names in to one result you would need to add OR statements before the first comma by "matt".  Something like:

=SUMPRODUCT(OR(--(C2:C16="matt"), --(C2:C16="Jimbob")),--(D2:D16="yes"))

For a list of results for different names you would need to do a separate formula for each name you waned to count.  SOmething like this:
=SUMPRODUCT(--(C2:C16="matt"),--(D2:D16="yes"))
=SUMPRODUCT(--(C2:C16="Jimbob"),--(D2:D16="yes"))

[TheBoy]

I think that gives me a starter to work from

[Gaffers]

No probs, the hard part is getting the commas and brackets lined up properly

One tip is to fabric some test data to which you already know the outcome and test your formulas on that

[TheBoy]

Forgot to say thanks, ESP to Guffers, this is now in and working and producing pretty colours for mgmt to pretend they understand.

Thanks !

[Gaffers]

No probs

Glad it is working.  I knew that year in France drinking wine and guzzling french food working hard would come in handy one day

[brendan1983]

I didn't see this in June as I was away on my travels... I used to do a lot of work with array formula's in excel - one thing I would say (depending if your data is in a database somewhere) is to do all of this work on the database side, and then just create an external data source to pull the data into excel.

Or what I do a lot of at the moment is to just generate the reports and calculations in reporting services, and they just get sent out on a schedule in what ever format you want.

This type of thing can be a real pain if you end up with lots of spread sheets with data in, much easier to keep it all in a database and just link to it.

[TheBoy]

I didn't see this in June as I was away on my travels... I used to do a lot of work with array formula's in excel - one thing I would say (depending if your data is in a database somewhere) is to do all of this work on the database side, and then just create an external data source to pull the data into excel.

Or what I do a lot of at the moment is to just generate the reports and calculations in reporting services, and they just get sent out on a schedule in what ever format you want.

This type of thing can be a real pain if you end up with lots of spread sheets with data in, much easier to keep it all in a database and just link to it.

Sadly, we couldn't use a database in this instance, that would have been far too simple and obvious for dim-witted management . It had to use purely Excel, hence my need for help

[Lagondanet]

The Boy. I have an old 'paper' Excel Users Guide gathering dust on the shelf if you want it, but Version 5.0 (I did say it was old!). I could stick it in the post. Might be useful to start a fire. 

[TheBoy]

The Boy. I have an old 'paper' Excel Users Guide gathering dust on the shelf if you want it, but Version 5.0 (I did say it was old!). I could stick it in the post. Might be useful to start a fire. 

No thanks, I try to avoid the software. Office tosser's tool

[Lagondanet]

The Boy. I have an old 'paper' Excel Users Guide gathering dust on the shelf if you want it, but Version 5.0 (I did say it was old!). I could stick it in the post. Might be useful to start a fire. 

No thanks, I try to avoid the software. Office tosser's tool

Noted.