ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Identifying First Instance of a Value Given a Criteria (https://www.excelbanter.com/excel-worksheet-functions/82503-identifying-first-instance-value-given-criteria.html)

carl

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.




Bob Phillips

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.






Peo Sjoblom

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.







All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com