Count recurring value and show the dates

[Jeffreyxx01]

Hi all,

I am trying to work out some data analysis on a currency pair.
I wanted to get the date for the same value that occurs 2 times or more.

I have counted in my files value that occurs 2 times: 78 times and value that occurs 3 times: 12 times.
But I cannot work out a formula that show me for the dates for the 78 value.

For example I have 0.6111 that occurs twice, once on 29/02/2000 and 01/04/2002.
What I would like to work out is a formula that shows me both dates.

If anyone can help, that would be good.

Thanks a lot

Jeffrey

 

[etaf]

would conditional formatting and highlighting the value/dates work for you

 

[Jeffreyxx01]

Hi Wayne and thank you for the answer,

I believe that would not be an interesting way to match the value for two different dates.
I want to see in a table what are the matching value for different dates very quickly without scrolling up/down.

Also I don't know how to add my excel files on the post so it is hard probably for you to see my query.

 

[etaf]

you cannot attach a file here
you can use an excel add-in to post here
or not preferred but using dropbox or onedrive to link to (issue is when you delete from the share sites - other people searching in the future will not see the file)

so you want to extract all the duplicates into a separate table
see next post for example from add-in

 

[etaf]

Sheet1

	A	B	C
Extracted Duplicates

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:150px;"><col style="width:135px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: center"]Data[/TD]
[TD="align: center"]Data[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]7[/TD]
[TD="align: center"]A4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]a[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]b[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]a[/TD]
[TD="align: center"]A6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]b[/TD]
[TD="align: center"]A7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]a[/TD]
[TD="align: center"]A8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]b[/TD]
[TD="align: center"]A9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]7[/TD]
[TD="align: center"]A10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]7[/TD]
[TD="align: center"]A14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]7[/TD]
[TD="align: center"]A15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: center"]12[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 {=IFERROR(INDEX(A2:A15, MATCH(0, COUNTIF(C1:$C$1, A2:A15)+IF(COUNTIF(A2:A15, A2:A15)>1, 0, 1), 0)), "")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Jeffreyxx01]

[TABLE="width: 203"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]date[/TD]
[TD] value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]03/01/2000[/TD]
[TD="align: right"]0.6253[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]31/01/2000[/TD]
[TD="align: right"]0.5993[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]01/02/2000[/TD]
[TD="align: right"]0.6014[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]29/02/2000[/TD]
[TD="align: right"]0.6111[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]01/03/2000[/TD]
[TD="align: right"]0.6133[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]31/03/2000[/TD]
[TD="align: right"]0.5999[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]03/04/2000[/TD]
[TD="align: right"]0.5982[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]28/04/2000[/TD]
[TD="align: right"]0.5874[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]01/05/2000[/TD]
[TD="align: right"]0.5892[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]31/05/2000[/TD]
[TD="align: right"]0.6243
[/TD]
[/TR]
</tbody>[/TABLE]


Hi Wayne,

Thank you for your answer.
My data looks like this and I cannot really work out your formula, it seems not working on my 400 data that I am trying to use.
I pasted the example of my spreadsheet above.

Thank you a lot

 

[Jeffreyxx01]

[TABLE="width: 267"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD] C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]date[/TD]
[TD] value[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]03/01/2000[/TD]
[TD="align: right"]0.6253[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]31/01/2000[/TD]
[TD="align: right"]0.5993[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]01/02/2000[/TD]
[TD="align: right"]0.6014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]29/02/2000[/TD]
[TD="align: right"]0.6111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]01/03/2000[/TD]
[TD="align: right"]0.6133[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]31/03/2000[/TD]
[TD="align: right"]0.5999[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]03/04/2000[/TD]
[TD="align: right"]0.5982[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]28/04/2000[/TD]
[TD="align: right"]0.5874[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]01/05/2000[/TD]
[TD="align: right"]0.5892[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[etaf]

thats not going to work
mmmmm - have a think

 

[Jeffreyxx01]

Thank you for the help though.

 

[Aladin Akyurek]

What is the expected output for the sample of post #6 ?