 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
or possibly trivial 😳
imagine a spreadsheet with a list of STW usernames and then a series of columns showing characteristics (say a hitter status column, ranging from "big hitter" to "normal person" in one column but there are maybe a thousand other possible descriptions of hitter status between these two)
Is there a way of sorting rows by hitter status but also then either colouring every different description differently from adjoining groups or else separating each group by an empty line ?
I wondered about conditional formatting but there are too many descriptors to type in manually
Thanking Yow
You'd have to use a lookup field or assign a hitter score...
But once you've done that, it would be easy
^that
First the sorting issue. Assume you want the order to be something like bad to good rather than just grouping them together?You familier with vlookup? On a separate worksheet (rename the worksheet to lookup)list all your possible statuses in order in one Column and in the next column to the right give each status a number (1, 2,3 etc). Then do a vlookup against each individuals status to return the number on the lookup worksheet. This will let you sort the statuses in order then. What version of Excel are you using for the formatting? Is it a one off job you are doing or something you will have to replicate time and time again?
Beaten to it...
If you just want to alternate colour for each group it's not difficult, you just need a formula to toggle between 1 and 0 each time the hitter status changes then format based on that.
I do know how to do lookup, and sorting by status would be trivial anyway just by using sort, wouldn't it ?
what I want is as below, can lookups do this ? :
(actually, even if they could, populating the lookup with thousands of options would take ages wouldn't it?)
bighitter
bighitter
bighitter
bighitter
bighitter
(then either a gap or else a different colour for all the next ones)
troll
troll
troll
troll
troll
troll
troll
troll
troll
troll
(then either a gap or else a different colour for all the next ones)
hardman
hardman
hardman
hardman
(then either a gap or else a different colour for all the next ones)
etc
<sits up>you just need a formula to toggle between 1 and 0 each time the hitter status changes
Why would you want to colour them? Use an if statement to assign a number to each unique type by adding 1 if not equal to the row above. Then do some countifs on the numbers for stats.
Sorting would be trivial assuming you didn't mind that you might have it arranged like awesome, awful, brilliant, crappie etc. To get in an order that makes sense like awful, crappie, brilliant, awesome you need to create a sort key.
You could with a macro, something like
Count the entries
Work down the rows (count up 1 each time) untill 2 arent the same, Insert a row
Continue untill count = number of entries.
Havent got excel infront of me and not good enought to write VBA freehand!
for easy visualisation for those who don't really know what the thing actually means but want to spout crap about how important they areWhy would you want to colour them?
<semi> 😳Use an if statement to assign a number to each unique type by adding 1 if not equal to the row above
Assuming your statuses are in column B and the data starts on row 2, put this formula in cell c2 and type a 1 into c1. =if(b2=b1,c1, if(C1=0,1,0)). Not got access to excel right now to check but think this will work. Then just conditional format based in this value.
If hitter status is in b then a2 = if(b2=b1,a1,1-a1) should do it, but I'm far from a pic and my brain is being killed by flu so don't take it as gospel
Drag that down over all rows of data.
sort order is unimportant (though I'll learn WTF sort keys are at some point)
counts probably do matter but I'm happy with that I think
(I learned MID and IF(ISERROR ... today - go me !!)
Ah, I think you people are dragging me there !
There's not a way to insert a blank row using commands, is there ?
(though different/alternating colours might actually be better, thinking about it)
You would need a macro to insert a blank row. Not really something to type into a phone while away from excel!
One way to get a gap would be to set column a quite narrow but format it to wrap text, then in a2 = if(b2=b1,"","spaces") - that needs to be enough spaces in the second part to force a line wrap.
Thanks all
just found [url= http://www.mrexcel.com/forum/excel-questions/436061-visual-basic-applications-insert-blank-row-after-repeating-cell-values-column.html ]this[/url] which seems like it'll do me too
(thinking I'll do the IFnumbery bit first, colour with conditional formatting and then insert the blanks - that'll go, right ?
Your welcome. That looks like it will work no problem. Just be aware that if the macro falls over for any reason before it has run all the way through there is no error handling built into the code. This means that some of the things it has turned off (warnings) won't be turned back on again. Not a particularly big deal (close and reopen excel) but can be really confusing if you don't realise what's happened.
If it's just to let you look at the groups then you could just sort on hitter status and then subtotal on the bighitter status (on the data tab) to get expandable groups for each. The subtotal itself is of course meaningless unless you count the number of people in each group of course rather than add up the numbers