hi!

i am having a2:a12 names; b1:g1 months; b2:g12 flags ("y" for reports
received, "blank" for not received)
is there any easy way to find out the names who have defaulted in
sending reports and the months of default
in a single report..?

NAME Jan-09 Feb-09 Mar-09 Apr-09 May-09 Jun-09
A Y Y Y Y
B Y Y Y
C Y Y Y Y
D Y Y Y Y Y Y
E Y Y Y
F Y Y Y Y
G Y
H Y Y Y Y Y
I Y Y Y Y
J Y Y Y Y Y
K




-via135

I would insert another column (use a header of All) and then put this formula in
H2 (your data went to column G):

=SUBSTITUTE(TRIM(IF(B2<"","",$B$1)&" "
&IF(C2<"","",$C$1)&" "
&IF(D2<"","",$D$1)&" "
&IF(E2<"","",$E$1)&" "
&IF(F2<"","",$F$1)&" "
&IF(G2<"","",$G$1))," ",
"--")

Then I could apply Data|filter|autofilter to that column and show the
non-blanks.



via135 wrote:

hi!

i am having a2:a12 names; b1:g1 months; b2:g12 flags ("y" for reports
received, "blank" for not received)
is there any easy way to find out the names who have defaulted in
sending reports and the months of default
in a single report..?

NAME Jan-09 Feb-09 Mar-09 Apr-09 May-09 Jun-09
A Y Y Y Y
B Y Y Y
C Y Y Y Y
D Y Y Y Y Y Y
E Y Y Y
F Y Y Y Y
G Y
H Y Y Y Y Y
I Y Y Y Y
J Y Y Y Y Y
K

-via135

--

Dave Peterson

And drag that formula down column G as far as necessary.

Dave Peterson wrote:

I would insert another column (use a header of All) and then put this formula in
H2 (your data went to column G):

=SUBSTITUTE(TRIM(IF(B2<"","",$B$1)&" "
&IF(C2<"","",$C$1)&" "
&IF(D2<"","",$D$1)&" "
&IF(E2<"","",$E$1)&" "
&IF(F2<"","",$F$1)&" "
&IF(G2<"","",$G$1))," ",
"--")

Then I could apply Data|filter|autofilter to that column and show the
non-blanks.

via135 wrote:

hi!

i am having a2:a12 names; b1:g1 months; b2:g12 flags ("y" for reports
received, "blank" for not received)
is there any easy way to find out the names who have defaulted in
sending reports and the months of default
in a single report..?

NAME Jan-09 Feb-09 Mar-09 Apr-09 May-09 Jun-09
A Y Y Y Y
B Y Y Y
C Y Y Y Y
D Y Y Y Y Y Y
E Y Y Y
F Y Y Y Y
G Y
H Y Y Y Y Y
I Y Y Y Y
J Y Y Y Y Y
K

-via135

--

Dave Peterson

--

Dave Peterson

Dave Peterson wrote:
And drag that formula down column G as far as necessary.

I would insert another column (use a header of All) and then put this formula in
H2 (your data went to column G):
[quoted text clipped - 36 lines]

Dave Peterson


stupendous..Dave..!

this is what i exactly wanted..!
but..a small hitch..!
i'm getting the values in Col H as under..!

39881--39912
39822--39912--39942
39881--39973

39822--39853--39881
39853--39912
39822--39853--39912--39942--39973
39822
39881--39912
39973
39822--39853--39881--39912--39942--39973

how can i convert them into "mmm-yy" format within the single cell..!
hlp me to fix it..!

-via135

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1

=SUBSTITUTE(TRIM(IF(B2<"","",text($B$1,mmm-yy"))&" "
&IF(C2<"","",text($C$1,mmm-yy")&" "

and so forth...

"via135 via OfficeKB.com" wrote:

Dave Peterson wrote:
And drag that formula down column G as far as necessary.

I would insert another column (use a header of All) and then put this formula in
H2 (your data went to column G):
[quoted text clipped - 36 lines]

Dave Peterson


stupendous..Dave..!

this is what i exactly wanted..!
but..a small hitch..!
i'm getting the values in Col H as under..!

39881--39912
39822--39912--39942
39881--39973

39822--39853--39881
39853--39912
39822--39853--39912--39942--39973
39822
39881--39912
39973
39822--39853--39881--39912--39942--39973

how can i convert them into "mmm-yy" format within the single cell..!
hlp me to fix it..!

-via135

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1

--

Dave Peterson

Dave Peterson wrote:
=SUBSTITUTE(TRIM(IF(B2<"","",text($B$1,mmm-yy"))&" "
&IF(C2<"","",text($C$1,mmm-yy")&" "

and so forth...

And drag that formula down column G as far as necessary.

[quoted text clipped - 30 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1

yes..got it..!

thks a lot..Dave..!!

-via135

--
Message posted via http://www.officekb.com