Select data from list without duplicates on a certain criteria
 

Kindly Help!!!

In Sheet 1 I have the following data

Clmn A 1 2 3 4 5
16.10.08 AA BB CC BB AA
17.10.08 CC DD AA AA BB
18.10.08 BB AA CC AA AA

In Sheet 2 I have a drop down list of dates. When I choose a certain date I
want excel to display the items in that particular date (without duplicates)
and the number of times that item is present.

Select data from list without duplicates on a certain criteria
 

Can you show a sample of the output you expect?

--JP

On Oct 16, 5:08*am, Niju David
wrote:
Kindly Help!!!

In Sheet 1 I have the following data

Clmn A * * * * *1 * * * *2 * * 3 * * 4 * * 5
16.10.08 * * AA * * BB *CC * BB *AA
17.10.08 * * CC * * DD *AA *AA *BB
18.10.08 * * BB * * *AA *CC *AA *AA

In Sheet 2 I have a drop down list of dates. When I choose a certain date I
want excel to display the items in that particular date (without duplicates)
and the number of times that item is present.

Select data from list without duplicates on a certain criteria
 

Dear JP,
The sample output would be like, when i select 17.10.08 in another sheet it
should show

AA-2
BB-1
CC-1
DD-1

If i select 18.10.08 then it should show

AA-3
CC-1

Hope this is clear

Thank you


"JP" wrote:

Can you show a sample of the output you expect?

--JP

On Oct 16, 5:08 am, Niju David
wrote:
Kindly Help!!!

In Sheet 1 I have the following data

Clmn A 1 2 3 4 5
16.10.08 AA BB CC BB AA
17.10.08 CC DD AA AA BB
18.10.08 BB AA CC AA AA

In Sheet 2 I have a drop down list of dates. When I choose a certain date I
want excel to display the items in that particular date (without duplicates)
and the number of times that item is present.

Select data from list without duplicates on a certain criteria
 

You would use the MATCH formula to return the row where the date is
found, then the COUNTA formula to count the number of instances of
each letter pair.

For example, if cell A1 on Sheet 2 contains the dropdown, and you
select "17.10.08", then this formula would return the row where it was
found on Sheet 1:

=MATCH(A1,Sheet1!A1:A3,0)

and this formula builds on that to count how many "AA" are in that
row:

=COUNTIF(OFFSET(Sheet1!A1,B1-1,0,1,6),"AA")


The formulas above are based on the sample data you provided in your
original post.


--JP

On Oct 16, 9:10*am, Niju David
wrote:
Dear JP,
The sample output would be like, when i select 17.10.08 in another sheet it
should show

AA-2
BB-1
CC-1
DD-1

If i select 18.10.08 then it should show

AA-3
CC-1

Hope this is clear

Thank you



"JP" wrote:
Can you show a sample of the output you expect?

--JP

On Oct 16, 5:08 am, Niju David
wrote:
Kindly Help!!!

In Sheet 1 I have the following data

Clmn A * * * * *1 * * * *2 * * 3 * * 4 * * 5
16.10.08 * * AA * * BB *CC * BB *AA
17.10.08 * * CC * * DD *AA *AA *BB
18.10.08 * * BB * * *AA *CC *AA *AA

In Sheet 2 I have a drop down list of dates. When I choose a certain date I
want excel to display the items in that particular date (without duplicates)
and the number of times that item is present.- Hide quoted text -

- Show quoted text -

Select data from list without duplicates on a certain criteria
 

Sorry I meant COUNTIF, not COUNTA.

--JP

On Oct 16, 2:42*pm, JP wrote:
You would use the MATCH formula to return the row where the date is
found, then the COUNTA formula to count the number of instances of
each letter pair.

For example, if cell A1 on Sheet 2 contains the dropdown, and you
select "17.10.08", then this formula would return the row where it was
found on Sheet 1:

=MATCH(A1,Sheet1!A1:A3,0)

and this formula builds on that to count how many "AA" are in that
row:

=COUNTIF(OFFSET(Sheet1!A1,B1-1,0,1,6),"AA")

The formulas above are based on the sample data you provided in your
original post.

--JP

Select data from list without duplicates on a certain criteria
 

Dear JP,
I am comfortable with finding the number of AA. But what I need the formula
to provide is the "AA" itself. Because in the countif function i cannot put
AA as my list is very huge. So if the formula can pull out AA from the list
then in the countif criteria i will just have to link it to that cell.

"JP" wrote:

Sorry I meant COUNTIF, not COUNTA.

--JP

On Oct 16, 2:42 pm, JP wrote:
You would use the MATCH formula to return the row where the date is
found, then the COUNTA formula to count the number of instances of
each letter pair.

For example, if cell A1 on Sheet 2 contains the dropdown, and you
select "17.10.08", then this formula would return the row where it was
found on Sheet 1:

=MATCH(A1,Sheet1!A1:A3,0)

and this formula builds on that to count how many "AA" are in that
row:

=COUNTIF(OFFSET(Sheet1!A1,B1-1,0,1,6),"AA")

The formulas above are based on the sample data you provided in your
original post.

--JP

Select data from list without duplicates on a certain criteria
 

Why not put the criteria into a cell, and reference it from the
formula?

For example in C1, put "AA" and then reference it from the COUNTIF
cell.

=COUNTIF(OFFSET(Sheet1!A1,B1-1,0,1,6),C1)

Then you could just create a list of unique search items in column C
(C1: "AA", C2: "BB", etc) and fill down the COUNTIF formula to get
your counts.

--JP

On Oct 16, 5:01*pm, Niju David
wrote:
Dear JP,
I am comfortable with finding the number of AA. But what I need the formula
to provide is the "AA" itself. Because in the countif function i cannot put
AA as my list is very huge. So if the formula can pull out AA from the list
then in the countif criteria i will just have to link it to that cell.



"JP" wrote:
Sorry I meant COUNTIF, not COUNTA.

--JP

On Oct 16, 2:42 pm, JP wrote:
You would use the MATCH formula to return the row where the date is
found, then the COUNTA formula to count the number of instances of
each letter pair.

For example, if cell A1 on Sheet 2 contains the dropdown, and you
select "17.10.08", then this formula would return the row where it was
found on Sheet 1:

=MATCH(A1,Sheet1!A1:A3,0)

and this formula builds on that to count how many "AA" are in that
row:

=COUNTIF(OFFSET(Sheet1!A1,B1-1,0,1,6),"AA")

The formulas above are based on the sample data you provided in your
original post.

--JP- Hide quoted text -

- Show quoted text -

Select data from list without duplicates on a certain criteria
 

Dear JP,
I dont want to put the list before hand because of 2 reasons;
1-I have got almost 1000 to 2000 of them
2-If there are no counts for a particular value then it will show zero or
blank, which i do no want.

Kindly help

"JP" wrote:

Why not put the criteria into a cell, and reference it from the
formula?

For example in C1, put "AA" and then reference it from the COUNTIF
cell.

=COUNTIF(OFFSET(Sheet1!A1,B1-1,0,1,6),C1)

Then you could just create a list of unique search items in column C
(C1: "AA", C2: "BB", etc) and fill down the COUNTIF formula to get
your counts.

--JP

On Oct 16, 5:01 pm, Niju David
wrote:
Dear JP,
I am comfortable with finding the number of AA. But what I need the formula
to provide is the "AA" itself. Because in the countif function i cannot put
AA as my list is very huge. So if the formula can pull out AA from the list
then in the countif criteria i will just have to link it to that cell.



"JP" wrote:
Sorry I meant COUNTIF, not COUNTA.

--JP

On Oct 16, 2:42 pm, JP wrote:
You would use the MATCH formula to return the row where the date is
found, then the COUNTA formula to count the number of instances of
each letter pair.

For example, if cell A1 on Sheet 2 contains the dropdown, and you
select "17.10.08", then this formula would return the row where it was
found on Sheet 1:

=MATCH(A1,Sheet1!A1:A3,0)

and this formula builds on that to count how many "AA" are in that
row:

=COUNTIF(OFFSET(Sheet1!A1,B1-1,0,1,6),"AA")

The formulas above are based on the sample data you provided in your
original post.

--JP- Hide quoted text -

- Show quoted text -

Select data from list without duplicates on a certain criteria
 

You only need as many COUNTIF and MATCH formulas as there are unique
entries in each column. So if you had "AA", "BB", "CC", "DD", you only
need four sets of formulas.

If you don't want to see ANYTHING if a particular value is not found,
then you need VBA. That's the only way to do it without having a cell
occupied by a formula.

--JP


On Oct 20, 9:59*am, Niju David
wrote:
Dear JP,
I dont want to put the list before hand because of 2 reasons;
1-I have got almost 1000 to 2000 of them
2-If there are no counts for a particular value then it will show zero or
blank, which i do no want.

Kindly help



"JP" wrote:
Why not put the criteria into a cell, and reference it from the
formula?

For example in C1, put "AA" and then reference it from the COUNTIF
cell.

=COUNTIF(OFFSET(Sheet1!A1,B1-1,0,1,6),C1)

Then you could just create a list of unique search items in column C
(C1: "AA", C2: "BB", etc) and fill down the COUNTIF formula to get
your counts.

--JP