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
1	Data	Data	Extracted Duplicates
2	1		2
3	2		7
4	7	A4	a
5	4		b
6	a	A6
7	b	A7
8	a	A8
9	b	A9
10	7	A10
11	8
12	2
13	6
14	7	A14
15	7	A15
16	12

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:150px;"><col style="width:135px;"></colgroup><tbody>
</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]

A	B	C
	date	value
1	03/01/2000	0.6253
2	31/01/2000	0.5993
3	01/02/2000	0.6014
4	29/02/2000	0.6111
5	01/03/2000	0.6133
6	31/03/2000	0.5999
7	03/04/2000	0.5982
8	28/04/2000	0.5874
9	01/05/2000	0.5892
10	31/05/2000	0.6243

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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]

	A	B	C
1	date	value
2	03/01/2000	0.6253
3	31/01/2000	0.5993
4	01/02/2000	0.6014
5	29/02/2000	0.6111
6	01/03/2000	0.6133
7	31/03/2000	0.5999
8	03/04/2000	0.5982
9	28/04/2000	0.5874
10	01/05/2000	0.5892

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[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 ?