Here is a tough one:

I have a table with rows of information including an ID Code. The ID code is
made up of four characters, each meaning something distinct. I want to
create a flexible reporting system where the user can specify which of these
rows to include in the report. Up to here there have been no problems,
example;

Code IMDA specifies an account that is active, indicated by the "A". Most
accounts are Active but there is also the possibility for it to be Inactive,
in which case the Code would have been IMDI, for Inactive. To gather the Net
Worth of these accounts, I simply look for the distinct code.


SUMIF(A5:A1500,"IMDA",CW5:CW1500)

SUMIF(A5:A1500,"IMDI",CW5:CW1500)



Challenge:
I would like to have the ability to use wildcards (+) in the Code to
indicate that any character in that position should be considered a match.
The above code would produce a report that has two cells showing all Active
IMD accounts (IMDA), and all Inactive (IMDI).



I would like to offer the user the ability to insert the wildcard by
changing the code to be able to handle a Pseudo Code of IMD+ where the last
character is inconsequential to a match. In the real world, this "+" could
be in any position.



Does anyone have any suggestions on how to proceed?

Craig

This should give you a start point:

CODE VALUE RESULT INPUT
IMDA 10 320 COUNT IM
AAAA 20 DON’T COUNT
BBBB 30 DON’T COUNT
IMDA 40 COUNT
IMAA 50 COUNT
IMDA 60 COUNT
IMD1 70 COUNT
AAGH 80 DON’T COUNT
IMDA 90 COUNT
INDA 100 DON’T COUNT

CELL D2 SUMIF(C2:C11,"COUNT",A2:A11)
CELL E2 IF(LEFT(B2,LEN($F$2))=$F$2,"COUNT","DON’T COUNT")

I cannot format this so it is eay to read - I will explain

CODE column C VALUE column D (helper column E) INPUT coulmn F



edvwvw


Craig Brandt wrote:
>Here is a tough one:
>
>I have a table with rows of information including an ID Code. The ID code is
>made up of four characters, each meaning something distinct. I want to
>create a flexible reporting system where the user can specify which of these
>rows to include in the report. Up to here there have been no problems,
>example;
>
>Code IMDA specifies an account that is active, indicated by the "A". Most
>accounts are Active but there is also the possibility for it to be Inactive,
>in which case the Code would have been IMDI, for Inactive. To gather the Net
>Worth of these accounts, I simply look for the distinct code.
>
>SUMIF(A5:A1500,"IMDA",CW5:CW1500)
>
>SUMIF(A5:A1500,"IMDI",CW5:CW1500)
>
>Challenge:
>I would like to have the ability to use wildcards (+) in the Code to
>indicate that any character in that position should be considered a match.
>The above code would produce a report that has two cells showing all Active
>IMD accounts (IMDA), and all Inactive (IMDI).
>
>I would like to offer the user the ability to insert the wildcard by
>changing the code to be able to handle a Pseudo Code of IMD+ where the last
>character is inconsequential to a match. In the real world, this "+" could
>be in any position.
>
>Does anyone have any suggestions on how to proceed?
>
>Craig

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

edvwvw wrote:
>This should give you a start point:
>
> CODE VALUE RESULT INPUT
> IMDA 10 320 COUNT IM
> AAAA 20 DON’T COUNT
> BBBB 30 DON’T COUNT
> IMDA 40 COUNT
> IMAA 50 COUNT
> IMDA 60 COUNT
> IMD1 70 COUNT
> AAGH 80 DON’T COUNT
> IMDA 90 COUNT
> INDA 100 DON’T COUNT
>
>CELL D2 SUMIF(C2:C11,"COUNT",A2:A11)
>CELL E2 IF(LEFT(B2,LEN($F$2))=$F$2,"COUNT","DON’T COUNT")
>
>I cannot format this so it is eay to read - I will explain
>
>CODE column B VALUE column C Result column D (helper column E) INPUT coulmn F
>
>edvwvw
>
>>Here is a tough one:
>>
>[quoted text clipped - 27 lines]
>>
>>Craig

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1

On Mon, 12 May 2008 09:30:30 -0500, "Craig Brandt" <brandtcraig@att.net> wrote:

>Here is a tough one:

>
>I would like to offer the user the ability to insert the wildcard by
>changing the code to be able to handle a Pseudo Code of IMD+ where the last
>character is inconsequential to a match. In the real world, this "+" could
>be in any position.
>
>
>
>Does anyone have any suggestions on how to proceed?
>
>Craig
>

The SUMIF text criteria uses the same wildcard tokens -- * or ? -- as does
other Excel functions that can use wild cards.

IMD? for a 4 character code starting with IMD and for which the 4th character
is irrelevant.

IMD* for any length code that starts with IMD
--ron

Ron:

It never ceases to amaze me. Some things that look impossible, turn out to
be really simple solutions.

Thanks again,
craig


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:sssg245n7n77r7f5l44ts5nj75b0ih6jd2@4ax.com...
> On Mon, 12 May 2008 09:30:30 -0500, "Craig Brandt" <brandtcraig@att.net>
wrote:
>
> >Here is a tough one:
>
> >
> >I would like to offer the user the ability to insert the wildcard by
> >changing the code to be able to handle a Pseudo Code of IMD+ where the
last
> >character is inconsequential to a match. In the real world, this "+"
could
> >be in any position.
> >
> >
> >
> >Does anyone have any suggestions on how to proceed?
> >
> >Craig
> >
>
> The SUMIF text criteria uses the same wildcard tokens -- * or ? -- as does
> other Excel functions that can use wild cards.
>
> IMD? for a 4 character code starting with IMD and for which the 4th
character
> is irrelevant.
>
> IMD* for any length code that starts with IMD
> --ron