E90Post
 


Coby Wheel
 
BMW 3-Series (E90 E92) Forum > BMW E90/E92/E93 3-series General Forums > Regional Forums > UK > UK Off-Topic Discussions > OT - need excel help



Reply
 
Thread Tools Search this Thread
      09-11-2007, 09:09 AM   #1
- Paul -
Major General
- Paul -'s Avatar
England
688
Rep
7,308
Posts

Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK

iTrader: (5)

Garage List
2005 320D SE  [10.00]
2005 645  [9.00]
OT - need excel help

I'm pretty handy with excel stuff normally but I'm stumped on my latest request from the boss. Google hasn't helped either.

I have to count the number of cells by colour?

I've posted it up here

http://spreadsheets.google.com/ccc?k...j94XA&hl=en_GB

So for example on the first sheet (data) I need a total somewhere that counts how many yellow rows there are, how many grey etc etc.

HELP
__________________
Appreciate 0
      09-11-2007, 09:12 AM   #2
NPDAN
Colonel
NPDAN's Avatar
95
Rep
2,573
Posts

Drives: AW DCT M3
Join Date: Feb 2007
Location: So.Cal

iTrader: (7)

Garage List
2008 E92 M3  [8.00]
07' E92 335i  [0.00]
Cant you do an "If" statement formula?
__________________
Appreciate 0
      09-11-2007, 09:13 AM   #3
silverbmwz3
Colonel
47
Rep
2,022
Posts

Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK

iTrader: (0)

Garage List
Is someone manually colouring the rows? Would be much easier to use conditional formatting and a code to indicate the failure level?

e.g. a scale of 1 to 7 and then use conditional formatting to format the whole row based on that value.

In the absence of that I think you'll be down to VBA to count the colours. I can have a go if you want? I'm not aware of any formula to detect colour.
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS
Previous: z3 3.0 Silver with Black&Red Leather
Appreciate 0
      09-11-2007, 09:16 AM   #4
- Paul -
Major General
- Paul -'s Avatar
England
688
Rep
7,308
Posts

Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK

iTrader: (5)

Garage List
2005 320D SE  [10.00]
2005 645  [9.00]
Yes - the colour is done manually - I've had a quick play in VB with this UDF

Function SumByColour(CellColour As Range, SumRange As Range)

Dim cell As Range

Dim SumColour As Double

Dim MySum



Application.Volatile

'If CellColour.Cells > 1 Then Exit Function

SumColour = CellColour.Interior.ColorIndex



For Each cell In SumRange

If cell.Interior.ColorIndex = SumColour Then

MySum = MySum + cell.Offset(0, -2)

End If

Next cell

SumByColour = MySum



End Function

found here:-
http://www.mrexcel.com/archive2/7400/8232.htm

but couldn't get it to work
__________________
Appreciate 0
      09-11-2007, 09:20 AM   #5
silverbmwz3
Colonel
47
Rep
2,022
Posts

Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK

iTrader: (0)

Garage List
OK, here's one, glad you know VBA!

Public Function mycount(oColourToLookFor As Range, oRangeToSearch As Range)

Dim oCell As Range
Dim iFound As Long

iFound = 0

For Each oCell In oRangeToSearch

If oCell.Interior.ColorIndex = oColourToLookFor.Interior.ColorIndex Then
iFound = iFound + 1
End If

Next

mycount = iFound

End Function


Basically you point it at the colour you want to look for (i'd put this say next to your key and point it at each one -ie repeat the call for each one, and point it at the range you want to search (i.e. a single column of those resuls)
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS
Previous: z3 3.0 Silver with Black&Red Leather
Appreciate 0
      09-11-2007, 09:23 AM   #6
silverbmwz3
Colonel
47
Rep
2,022
Posts

Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK

iTrader: (0)

Garage List
Here's my example with formula showing:
Attached Images
 
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS
Previous: z3 3.0 Silver with Black&Red Leather
Appreciate 0
      09-11-2007, 09:29 AM   #7
silverbmwz3
Colonel
47
Rep
2,022
Posts

Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK

iTrader: (0)

Garage List
Of course, it means you have to keep the range size updated unless you somehow can call it with a named range...otherwise when you add a row the range won't include that row. Might be other ways if that proves a problem.
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS
Previous: z3 3.0 Silver with Black&Red Leather
Appreciate 0
      09-11-2007, 09:34 AM   #8
- Paul -
Major General
- Paul -'s Avatar
England
688
Rep
7,308
Posts

Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK

iTrader: (5)

Garage List
2005 320D SE  [10.00]
2005 645  [9.00]
Silver - thanks very much - that works a treat!! I owe you a beer
__________________
Appreciate 0
      09-11-2007, 09:36 AM   #9
silverbmwz3
Colonel
47
Rep
2,022
Posts

Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK

iTrader: (0)

Garage List
NP. Glad to help, anytime.
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS
Previous: z3 3.0 Silver with Black&Red Leather
Appreciate 0
      09-12-2007, 01:01 PM   #10
- Paul -
Major General
- Paul -'s Avatar
England
688
Rep
7,308
Posts

Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK

iTrader: (5)

Garage List
2005 320D SE  [10.00]
2005 645  [9.00]
More help needed - I'm rustier than I thought.

Ok - I've done this vb to select rows dependant on a string search then copy teh row to another sheet but I want to modify it to search for multiple strings and then copy those rows to the other sheet.


Sub yellowtoescalatedfaultsbyequipmenttype()

' messagebox
MsgBox "The magic will now start. The screen is going to flash repeatedly now!", vbInformation, "Health & Safety warning"


Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 19
LSearchRow = 19

'Start copying data to row 54 in Sheet2 (row counter variable)
LCopyToRow = 54

While Len(Range("AW" & CStr(LSearchRow)).Value) > 0

'If value in column AW = "Critical Fault reported to ADST",
If Range("AW" & CStr(LSearchRow)).Value = "Critical Fault reported to ADST" Then

'Select row in Data to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Escalated faults by Equip Type in next row
Sheets("Escalated faults by Equip Type").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Data to continue searching
Sheets("Data").Select

End If

LSearchRow = LSearchRow + 1

Wend



'Position on cell A1
Application.CutCopyMode = False
Range("A1").Select


Exit Sub

Err_Execute:
MsgBox "An error occurred - Please call 5404 8080 and ask for ***."

End Sub
__________________
Appreciate 0
      09-13-2007, 01:49 AM   #11
silverbmwz3
Colonel
47
Rep
2,022
Posts

Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK

iTrader: (0)

Garage List
So when you look or the relevant string don't you just want to say Or.....then look for the other string(s) ?

I.E. Adding an Or statement where the string gets compared then another string comparison and repeat as many times as you need?
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS
Previous: z3 3.0 Silver with Black&Red Leather
Appreciate 0
      09-13-2007, 04:27 AM   #12
- Paul -
Major General
- Paul -'s Avatar
England
688
Rep
7,308
Posts

Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK

iTrader: (5)

Garage List
2005 320D SE  [10.00]
2005 645  [9.00]
i.e

If Range("AW" & CStr(LSearchRow)).Value = "Critical Fault reported to ADST"or"etcetc" Then

Pretty sure I tried that and it failed - not at work at the minute so can't check it again. Is that what you mean?
__________________
Appreciate 0
      09-13-2007, 04:29 AM   #13
silverbmwz3
Colonel
47
Rep
2,022
Posts

Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK

iTrader: (0)

Garage List
If (Range("AW&Cstr(LSearchRow)).Value = "Critical Fault reported to ADST") Or (Range("AW&Cstr(LSearchRow)).Value = "1stnewcarlovesipodsandheknowsit")

you can't just repeat the string bit as each bit after a logic operator (OR, AND etc.) will be evaluated on its own merit. So you were asking the question:

"etcetc" ?

which doesn't stand on its own. The computer is thinking "yeah...what about it".
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS
Previous: z3 3.0 Silver with Black&Red Leather
Appreciate 0
      09-13-2007, 04:39 AM   #14
- Paul -
Major General
- Paul -'s Avatar
England
688
Rep
7,308
Posts

Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK

iTrader: (5)

Garage List
2005 320D SE  [10.00]
2005 645  [9.00]
Mmmmm - I'll try that later - minus the ipods bit
__________________
Appreciate 0
      09-13-2007, 08:22 AM   #15
- Paul -
Major General
- Paul -'s Avatar
England
688
Rep
7,308
Posts

Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK

iTrader: (5)

Garage List
2005 320D SE  [10.00]
2005 645  [9.00]
Get a compile error:-

Expected:list separator or )

?

I tried moving the brackets around but no different.
__________________
Appreciate 0
      09-13-2007, 08:26 AM   #16
silverbmwz3
Colonel
47
Rep
2,022
Posts

Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK

iTrader: (0)

Garage List
If your code above worked then this should (may be positionging number/brackets in my example above):

If (Range("AW" & CStr(LSearchRow)).Value = "Critical Fault reported to ADST") Or (Range("AW" & CStr(LSearchRow)).Value = "some other text") Then

looks fine to me?
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS
Previous: z3 3.0 Silver with Black&Red Leather
Appreciate 0
      09-13-2007, 08:32 AM   #17
- Paul -
Major General
- Paul -'s Avatar
England
688
Rep
7,308
Posts

Drives: see above.
Join Date: Apr 2005
Location: Yorkshire, UK

iTrader: (5)

Garage List
2005 320D SE  [10.00]
2005 645  [9.00]
Yet again - I owe you another beer! I hope you never come to a meet

__________________
Appreciate 0
      09-13-2007, 08:41 AM   #18
silverbmwz3
Colonel
47
Rep
2,022
Posts

Drives: X5 g05
Join Date: Mar 2007
Location: Buckinghamshire, UK

iTrader: (0)

Garage List
Thanks very much! NP, anytime.
__________________
Current: E90 335d (May 2007) | Black/Black | Prof Hifi | Prof Nav | 6FL | Fully Electric Seats | Xenons | Folding Mirrors | PDC
Previous: 325ci Conv. Black/Black with lots of ACS
Previous: z3 3.0 Silver with Black&Red Leather
Appreciate 0
Reply

Bookmarks

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off



All times are GMT -5. The time now is 02:27 PM.




e90post
Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
1Addicts.com, BIMMERPOST.com, E90Post.com, F30Post.com, M3Post.com, ZPost.com, 5Post.com, 6Post.com, 7Post.com, XBimmers.com logo and trademark are properties of BIMMERPOST