hi - Two columns

  • Thread starter Thread starter d_h_sanjay
  • Start date Start date
D

d_h_sanjay

Excel file (sample) is shown below
A B C D E
1 a p 1 aq
2 a q 1 sd
3 b p 2 we
4 c q 2 xs
5 a r 2 as
6 d p 1 aq
7 b q 1 sd
8 a q 2 we

There is another sheet 2/ file which has the list of items from column C i.e.
X Y
p
q
r
etc.

What i require is a formula in column Y in sheet 2, which would count no. of
unique items from column B of sheet 1 where the column C in Sheet 1 is equal
to value in Column X in Sheet 2.
that is whatever values be in Column B of Sheet 1, i want just their total
number of unique items for every value in Column c which is equal to values
in Colmn X of Sheet 2
i.e. for every "p" in Sheet 1 in column C i would like the sum of unique
count of items (a,b,c) which are there in column B of sheet 1 - if there are
a, a, b, c, etc. then i want sum of unique 3 and not 4 as the result of my
formula
Can anyone please suggest a formula for the same.
 
To use standard worksheet formulas, you would need to use a helper column of formulas for each item
that you want to identify unique values for. For example, a column of formulas like

=IF(Sheet1!C2="p",Sheet1!B2,"")

copied down to match the length of your data set. Then you can count the unique items with

=SUM(1/COUNTIF(B2:BXX,B2:BXX))-1

(assuming that the first formulas are in column B of sheet2, rows 2 through XX). This formula must
be array entered (entered using Ctrl-Shift-Enter, not just enter).

If you don't have an aversion to VBA, you could easily use a User-Defined-Function, without the need
for the helper columns.

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top