Re: Unique Selection of Data

First, you're very welcome....I’m glad I could help.

Next...the questions.

Question A:
Can you provide more details about the structure you want to build?

Question B:
The SUMPRODUCT function calculates on numbers,
but the (FREQUENCY()>0) phrase returns a series of TRUE/FALSE values.
However, when an arithmetic operator (+,-,*,/) is applied to boolean values
Excel converts TRUE to 1 and FALSE to 0. The double-unary (--) performs
that conversion.

Examples of how it works:
A1: =TRUE B1: =ISNUMBER(A1) C1: =SUMPRODUCT(A1)

When A1: =TRUE
B1 returns FALSE
C1 returns #VALUE!

When A1: =-TRUE
B1 returns TRUE
C1 returns -1

When A1: =--TRUE
B1 returns TRUE
C1 returns 1

When A1: =FALSE
B1 returns FALSE
C1 returns #VALUE!

When A1: =-FALSE
B1 returns TRUE
C1 returns 0

The double-unary convention clearly indicates that we are not attempting to
do anything other than convert boolean to numeric values.

This is clearly a data type conversion:
=--(A1>B1)

While this might look like a typo in the formula:
=(A1>B1)+0

I hope that helps.

Regards,

Ron
Microsoft MVP - Excel

"Andy" <Andy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A4AEE813-6B39-49F3-9D71-F11B9DF3E698@xxxxxxxxxxxxxxxx
Hello Ron:

Thank you for kind and most excellent reply. I have been playing and
tinkering with the formula all day and I have been absolutely loving it.

I do have a couple of optional follow up questions.

Optional Question A: I am trying to adapt these formula as a template so I
can cut even more corners (i.e. never adjust the formula again) by simply
pasting new data in. While the number of columns (say 3) will be
consistent
the number of rows will vary from one time period to the next but say will
never exceed 100 rows. It looks like I could set up the formula to cover
100
rows (and 3 columns) and not have to worry about the blank areas if I only
have say 75 rows of data. I know I will be able to figure this out for
sure
over the long run, but my trial tests tell me that this is correct.

Optional Question B: I was actually pretty close to figuring out the
formula
on my own but this certainly saved me a lot of investigation time. I was
wondering though what the two dashes prior to frequency mean? --->
(--(FREQUENCY

Thanks again Ron, you stirred a lot of excitement here today, the prompt
reply with the effective solution were certainly wonderful but knowing
that I
had asked the question properly was icing on the cake for me.

Kind regards,

Andy


"Ron Coderre" wrote:

Thank you for presenting a best case example of how to write a request!

With your posted data in A1:C7

Then
D1: Client Count
E1: =SUMPRODUCT((A2:A11<>"")/COUNTIF(A2:A11,A2:A11&""))

D2: 94
This ARRAY FORMULA, is committed with CTRL+SHIFT+ENTER,
instead of just ENTER:
E2: =SUMPRODUCT(--(FREQUENCY(IF($B$2:$B$10=D2,$A$2:$A$10),$A$2:$A$10)>0))

D3: 99
E3: (copy the formula from E2 to E3)

D4: 3180
This is also an ARRAY FORMULA:
E4: =SUMPRODUCT(--(FREQUENCY(IF($C$2:$C$10=D4,$A$2:$A$10),$A$2:$A$10)>0))

D5: 3110
E5: (copy the formula from E4 to E5)

Is that something you can work with?
Post back if you have more questions.

Regards,

Ron
Microsoft MVP - Excel

"Andy" <Andy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4ABB7217-2CB2-47AC-97C3-D93BDFB23864@xxxxxxxxxxxxxxxx
> Greetings:
>
> I export data from a database program into Excel. The challenge is that
> program repeats data if there are more than one type of service or
> items
> provided to the clients. Here is a sample of the data obtained:
>
> Client ID Service Item
> 172 94 3180
> 469 99 3110
> 469 99 3150
> 469 99 3220
> 470 99 3110
> 470 79 3150
>
> Control: Client 172 had service 94 and bought item 3180 (straight
> forward)
> Client 469 had service 99 and bought items 3110, 3150 and 3220
> Client 470 had service 99 and 79 and bought item 3110 and 3150.
>
> I have been able to count individual clients using the
> =SUM(IF(FREQUENCY(A1:A1,A1:A6)>0,1)) but I am kinda stumped as to a
> formula
> that would give me individual results for services and products.
>
> The results I would need for this data would be.
>
> # of Clients = 3 (this part is fine with the sum/if/frequency formula)
> # of 94 service = 1
> # of 99 service = 2 (one for client 469 and one for client 470)
> # of 3180 item = 1
> # of 3110 item = 2 (one for client 469 and one for client 470)
> # of 3150 item = 2 (one for client 469 and one for client 470)
> # of 3220 item = 1
>
> I suspect I will have to enter a unique formula for each item and
> service,
> which is fine. I do hope I explained this appropriately.
>
> Thank you for your time and expertise.
>
> Andy

.

Relevant Pages

  • Re: Unique Selection of Data
    ... Optional Question A: I am trying to adapt these formula as a template so I ... program repeats data if there are more than one type of service or items ... Client ID Service Item ... I have been able to count individual clients using the ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Launching an .exe on intranet
    ... you mentioned that your client wanted to ... "replace the functionality of his Excel file... ... share access to it, an ASP app is what you want, in which case you only need ... VB is a language for writing executables. ...
    (microsoft.public.frontpage.programming)
  • Re: My first database (long)
    ... looks similar to an Excel spreadsheet, many people tend to confuse the 2. ... designing reports, the biggest issue for you right now is actually designing ... As Pete and Bruce have mentioned, getting a good book on database design ... > The database contains client records. ...
    (microsoft.public.access.gettingstarted)
  • Re: Help creating database
    ... After the first round, the client can walk away with what they have so far, ... help you shoot yourself and your boss in the foot or feet. ... The problem with so many amateur Excel ... For a journeyman Access developer your proposed application seems to ...
    (microsoft.public.access.gettingstarted)
  • Re: Automatic swap of x - and y - axis
    ... when my client installed Excel 2007. ... My client has one option to overcome the problem, ... of the chart, and a description of the problem, someone may be able to help. ... by the time the graph is ...
    (microsoft.public.excel.charting)
Loading