Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default Identifying unique dates in a row of cells containing dates...

I have dates entered in cells A1 through H1, some of which are duplicate
dates and others which are unique dates.

What I need is a formula I can write in cell I1 which will tell me if the
date in cell A1 is unique in comparison to all the other dates in the range
A1 through H1.

I also need to extend this formula across cells J1 through P1 to repeat the
analysis for each of the respective dates in the data range. (i.e. I need
the formula in cell J1 to tell me if the date in cell B1 is unique in
comparison to all the other dates in the range A1 through H1).

Thanks for the assistance in advance!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 510
Default Identifying unique dates in a row of cells containing dates...

Hi,

=IF(MATCH(A1,$A$1:$H$1,0)=COLUMN(A1),"Unique","Not Unique")

should do the job ...

HTH
Cheers
Carim

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default Identifying unique dates in a row of cells containing dates...

Hi Carim:

The formula doesn't seem to work the way I need it to. As a test, I made
the dates in cells A1 and B1 the same. When I did that, the formula in cell
I1 still claimed the date in cell A1 was "Unique".

Any thoughts?


"Carim" wrote:

Hi,

=IF(MATCH(A1,$A$1:$H$1,0)=COLUMN(A1),"Unique","Not Unique")

should do the job ...

HTH
Cheers
Carim


  #4   Report Post  
Posted to microsoft.public.excel.misc
ICE ICE is offline
external usenet poster
 
Posts: 25
Default Identifying unique dates in a row of cells containing dates...

Try this one. Hope it works.

=IF(MATCH(A1;$A$1:$H$1;0)=COLUMN($A$1);"Not Unique";"Unique")

"cdavidson" wrote:

Hi Carim:

The formula doesn't seem to work the way I need it to. As a test, I made
the dates in cells A1 and B1 the same. When I did that, the formula in cell
I1 still claimed the date in cell A1 was "Unique".

Any thoughts?


"Carim" wrote:

Hi,

=IF(MATCH(A1,$A$1:$H$1,0)=COLUMN(A1),"Unique","Not Unique")

should do the job ...

HTH
Cheers
Carim


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 510
Default Identifying unique dates in a row of cells containing dates...

Hi,

=IF(COUNTIF($A$1:$H$1,A1)=1,"Unique","Not Unique")

should be better ...

Carim



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default Identifying unique dates in a row of cells containing dates...

Still doesn't work. After reading up on the MATCH and COLUMN functions, I'm
wondering if a COUNTIF forumla would be a better approach? (i.e. if COUNTIF
only returns a 1, then it's unique, otherwise it's duplicated).

Does this make any sense?


"ice" wrote:

Try this one. Hope it works.

=IF(MATCH(A1;$A$1:$H$1;0)=COLUMN($A$1);"Not Unique";"Unique")

"cdavidson" wrote:

Hi Carim:

The formula doesn't seem to work the way I need it to. As a test, I made
the dates in cells A1 and B1 the same. When I did that, the formula in cell
I1 still claimed the date in cell A1 was "Unique".

Any thoughts?


"Carim" wrote:

Hi,

=IF(MATCH(A1,$A$1:$H$1,0)=COLUMN(A1),"Unique","Not Unique")

should do the job ...

HTH
Cheers
Carim


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 510
Default Identifying unique dates in a row of cells containing dates...

Do you mean that :

=IF(COUNTIF($A$1:$H$1,A1)=1,"Unique","Not Unique")

does not work ...???


Carim

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default Identifying unique dates in a row of cells containing dates...

Works perfect when I take out the first "$" in the COUNTIF section.

Thanks!


"Carim" wrote:

Hi,

=IF(COUNTIF($A$1:$H$1,A1)=1,"Unique","Not Unique")

should be better ...

Carim


  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 510
Default Identifying unique dates in a row of cells containing dates...

Glad you have reached your solution...

Thanks for the feedback

Carim

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 390
Default Identifying unique dates in a row of cells containing dates...

In . com, Carim
spake thusly:

=IF(MATCH(A1,$A$1:$H$1,0)=COLUMN(A1),"Unique","Not Unique")

should do the job ...


Don't you have the logic reversed there? Or am I confused?
Would have thought you meant:

=IF(MATCH(A1,$A$1:$H$1,0)=COLUMN(A1),"Not Unique","Unique")

--
dman
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking Groups of cells between workbooks vnacj-joe Excel Discussion (Misc queries) 4 June 14th 07 05:18 PM
Identify a range of cells containing dates RJSohn Excel Worksheet Functions 2 August 24th 06 02:43 AM
How to format cells to show dates as the week-ending date of that dereksmom Excel Worksheet Functions 1 July 12th 06 04:40 PM
How can I count the number of unique dates in a Pivot Table? mpsfeldt Excel Discussion (Misc queries) 1 June 14th 06 03:56 PM
Counting Unique Cells When Spread Sheet is Filtered carl Excel Worksheet Functions 1 June 3rd 05 07:20 PM


All times are GMT +1. The time now is 05:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"