T
tarbox
Jeez. It seems as if it should be fairly straightforward, but I jus
can't work it out neatly, and my kludgey solutions are not only slopp
and slow - naturally they break a lot.
Here is what I've got. I've got survey results coming in in csv. M
boss wants daily reports. The data flows right into excel beautifully
a tidy header in row 3 and tidy row of unique id's running down th
last column. The number of responses will change from day to day b
rather large factors, so generally I'm trying to keep the daily report
fairly light and top level and to do them with functions and formula
that are fast enough not to care if I call the array SHEET1!U:U
Perhaps there is a much smarter way to do this to, but it has elude
me.
The responses are numerical representations of a text answer. What
want to be able to do is something very like the famous index/matc
thing, but I want to be able to further modify the returned values b
ranking them according to how many of them there are. So, so far,
have countif and index/match. I can reliably get each of these to work
but not to play together. Obviously, I am decidedly in favor o
non-unique values in the returned data, but I do NOT want repeate
values in the final list. That is, I don't want "No response""80%" t
appear in all three of the top spots.
Finally, if I designate five top responses as my goal, I would like t
reliably write an ISERROR function into the thing so that nothing show
up.
The closest I seem to have come so far is a nasty mess of neste
functions with lot's of IF statements and comparing consecutive value
and stuff... and when I run out of spots to nest functions I had th
"false" value kicking down to the next adjacent cell to labor on. I
kinda worked, but it is a bonehead inelegant way to do it, and wheneve
there is the smallest change in the precise environment - the number o
variables or whatever - it makes itself a ROYAL pain in the ass to fix
And it doesn't really work that well, anyway. I was screwing around wit
that a couple of days ago, though, and I can't remember all the reason
it sucked. But I'm sure after reading the above you know it does.
Even as I type this I can't believe I haven't figured this out. I eve
started working with the VBA 'sorted' function that the man who write
all of the lovely excel books has kindly made available to th
universe
can't work it out neatly, and my kludgey solutions are not only slopp
and slow - naturally they break a lot.
Here is what I've got. I've got survey results coming in in csv. M
boss wants daily reports. The data flows right into excel beautifully
a tidy header in row 3 and tidy row of unique id's running down th
last column. The number of responses will change from day to day b
rather large factors, so generally I'm trying to keep the daily report
fairly light and top level and to do them with functions and formula
that are fast enough not to care if I call the array SHEET1!U:U
Perhaps there is a much smarter way to do this to, but it has elude
me.
The responses are numerical representations of a text answer. What
want to be able to do is something very like the famous index/matc
thing, but I want to be able to further modify the returned values b
ranking them according to how many of them there are. So, so far,
have countif and index/match. I can reliably get each of these to work
but not to play together. Obviously, I am decidedly in favor o
non-unique values in the returned data, but I do NOT want repeate
values in the final list. That is, I don't want "No response""80%" t
appear in all three of the top spots.
Finally, if I designate five top responses as my goal, I would like t
reliably write an ISERROR function into the thing so that nothing show
up.
The closest I seem to have come so far is a nasty mess of neste
functions with lot's of IF statements and comparing consecutive value
and stuff... and when I run out of spots to nest functions I had th
"false" value kicking down to the next adjacent cell to labor on. I
kinda worked, but it is a bonehead inelegant way to do it, and wheneve
there is the smallest change in the precise environment - the number o
variables or whatever - it makes itself a ROYAL pain in the ass to fix
And it doesn't really work that well, anyway. I was screwing around wit
that a couple of days ago, though, and I can't remember all the reason
it sucked. But I'm sure after reading the above you know it does.
Even as I type this I can't believe I haven't figured this out. I eve
started working with the VBA 'sorted' function that the man who write
all of the lovely excel books has kindly made available to th
universe