| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Identifying First Instance of a Value Given a Criteria
My data table is like this: Date ID 20060403 123 20060403 456 20060403 789 20060403 123 20060403 222 20060403 222 20060404 123 20060404 888 20060404 777 20060404 222 I am using a formula like this in ColC =COUNTIF($B$2:B2,B2)=1 to help ID first instance of a given ID It gives a result like this: Date ID 20060403 123 TRUE 20060403 456 TRUE 20060403 789 TRUE 20060403 123 FALSE 20060403 222 TRUE 20060403 222 FALSE 20060404 123 FALSE 20060404 888 TRUE 20060404 777 TRUE 20060404 222 FALSE I would like to modify the formula to identify the first instance for each day - some of the ID are re-used on a day-to-day basis. A result like this is what I am targeting: Date ID 20060403 123 TRUE 20060403 456 TRUE 20060403 789 TRUE 20060403 123 FALSE 20060403 222 TRUE 20060403 222 FALSE 20060404 123 TRUE 20060404 888 TRUE 20060404 777 TRUE 20060404 222 TRUE Is this possible ? Thank you in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Identifying First Instance of a Value Given a Criteria
=SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2=B2))=1
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "carl" wrote in message ... My data table is like this: Date ID 20060403 123 20060403 456 20060403 789 20060403 123 20060403 222 20060403 222 20060404 123 20060404 888 20060404 777 20060404 222 I am using a formula like this in ColC =COUNTIF($B$2:B2,B2)=1 to help ID first instance of a given ID It gives a result like this: Date ID 20060403 123 TRUE 20060403 456 TRUE 20060403 789 TRUE 20060403 123 FALSE 20060403 222 TRUE 20060403 222 FALSE 20060404 123 FALSE 20060404 888 TRUE 20060404 777 TRUE 20060404 222 FALSE I would like to modify the formula to identify the first instance for each day - some of the ID are re-used on a day-to-day basis. A result like this is what I am targeting: Date ID 20060403 123 TRUE 20060403 456 TRUE 20060403 789 TRUE 20060403 123 FALSE 20060403 222 TRUE 20060403 222 FALSE 20060404 123 TRUE 20060404 888 TRUE 20060404 777 TRUE 20060404 222 TRUE Is this possible ? Thank you in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Identifying First Instance of a Value Given a Criteria
=SUMPRODUCT(--($A$2:A2&$B$2:B2=A2&B2))=1
copy down -- Regards, Peo Sjoblom http://nwexcelsolutions.com "carl" wrote in message ... My data table is like this: Date ID 20060403 123 20060403 456 20060403 789 20060403 123 20060403 222 20060403 222 20060404 123 20060404 888 20060404 777 20060404 222 I am using a formula like this in ColC =COUNTIF($B$2:B2,B2)=1 to help ID first instance of a given ID It gives a result like this: Date ID 20060403 123 TRUE 20060403 456 TRUE 20060403 789 TRUE 20060403 123 FALSE 20060403 222 TRUE 20060403 222 FALSE 20060404 123 FALSE 20060404 888 TRUE 20060404 777 TRUE 20060404 222 FALSE I would like to modify the formula to identify the first instance for each day - some of the ID are re-used on a day-to-day basis. A result like this is what I am targeting: Date ID 20060403 123 TRUE 20060403 456 TRUE 20060403 789 TRUE 20060403 123 FALSE 20060403 222 TRUE 20060403 222 FALSE 20060404 123 TRUE 20060404 888 TRUE 20060404 777 TRUE 20060404 222 TRUE Is this possible ? Thank you in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Getting Excel MDI/SDI/Excel Instance to behave more logically | Setting up and Configuration of Excel | |||
| Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria | Excel Worksheet Functions | |||
| Returning Results Based on Two Criteria | Excel Worksheet Functions | |||
| sorting more than 3 keys | Excel Discussion (Misc queries) | |||
| SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Linear Mode
