Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DATA VALIDATION LIST- CAN I SELECT FROM LIST WITHOUT SCROLLING | Excel Worksheet Functions | |||
Find duplicates in data with multiple criteria | Excel Discussion (Misc queries) | |||
how do I update a list to overwrite duplicates with new data | Excel Discussion (Misc queries) | |||
data validation list should have opt. to select based on criteria | Excel Worksheet Functions | |||
Select rows of data in a worksheet on one criteria in multiple co | Excel Worksheet Functions |