Unique Selection of Data

[Andy]

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

 

[Ron Coderre]

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]

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]

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