identifying duplicates in a range.

[sdrawrofsisiht]

I have a list of withdrawals and within that list I have duplicates sometimes from the same dates. I want to put the withdrawals into an average per week (which I already have set up), but vlookup by itself will only find the first instance. This puts the duplicates into the averages of the first week the duplicate is found. Basically, I need a formula to find the duplicates no matter where they fall.

 

[dUBBINS]

Hard to answer with a specific formula without seeing your data. However, have you tried using the AVERAGEIFS() function?

 

[FDibbins]

Hi, welcome to the board

Without any info on what your data looks like, this is a SWAG.

=iferror(index(A:A,small(if(date-range=selected-date-full-date-range, )),rows($A$1:A1))),"")
ARRAY entered using CTRL SHIFT ENTER
then copied down and across.

If you still have a problem, provide some sample data and expected outcome

 

[sdrawrofsisiht]

Hi, welcome to the board

Without any info on what your data looks like, this is a SWAG.

=iferror(index(A:A,small(if(date-range=selected-date-full-date-range, )),rows($A$1:A1))),"")
ARRAY entered using CTRL SHIFT ENTER
then copied down and across.

If you still have a problem, provide some sample data and expected outcome

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3/10/17
[/TD]
[TD][/TD]
[TD]89.74
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3/10/17
[/TD]
[TD][/TD]
[TD]367.67
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3/10/17
[/TD]
[TD][/TD]
[TD]3.72
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3/10/17
[/TD]
[TD][/TD]
[TD]10.77
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3/10/17
[/TD]
[TD][/TD]
[TD]43.83
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3/13/17
[/TD]
[TD][/TD]
[TD]25.41
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3/13/17
[/TD]
[TD][/TD]
[TD]3.72
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3/13/17
[/TD]
[TD][/TD]
[TD]89.74
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3/13/17
[/TD]
[TD][/TD]
[TD]32.36
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



What I need is a formula to find the duplicates on their individual days and if needed find the duplicates even if they fall on the same day. I am currently using an index/match to the price with the date but it only matches the first instance.

 

[sdrawrofsisiht]

That was badly worded. I need a formula to find the correct date with the price.

 

[FDibbins]

Maybe this will work for you? I used a helper column to ID all the duplicates, then used that to pull them out...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr]
[tr][td]

1​

[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td][/td][td]3/10/2017[/td][td][/td][td]89.74[/td][td]2[/td][td][/td][td][/td][td]3/10/2017[/td][td][/td][td]89.74[/td][/tr]

[tr][td]

3​

[/td][td][/td][td]3/10/2017[/td][td][/td][td]367.67[/td][td]1[/td][td][/td][td][/td][td]3/10/2017[/td][td][/td][td]3.72[/td][/tr]

[tr][td]

4​

[/td][td][/td][td]3/10/2017[/td][td][/td][td]3.72[/td][td]2[/td][td][/td][td][/td][td]3/13/2017[/td][td][/td][td]3.72[/td][/tr]

[tr][td]

5​

[/td][td][/td][td]3/10/2017[/td][td][/td][td]10.77[/td][td]1[/td][td][/td][td][/td][td]3/13/2017[/td][td][/td][td]89.74[/td][/tr]

[tr][td]

6​

[/td][td][/td][td]3/10/2017[/td][td][/td][td]43.83[/td][td]1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td][/td][td]3/13/2017[/td][td][/td][td]25.41[/td][td]1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td][/td][td]3/13/2017[/td][td][/td][td]3.72[/td][td]2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td]3/13/2017[/td][td][/td][td]89.74[/td][td]2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td][/td][td]3/13/2017[/td][td][/td][td]32.36[/td][td]1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
E2=COUNTIF($D$2:$D$10,D2)
copied down

H2=IFERROR(INDEX(B:B,SMALL(IF($E$2:$E$10>1,ROW($B$2:$B$10)),ROWS($A$1:A1))),"")
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself."

Then copy down - skip 1 column and copy down again

 

[wetbank17]

One say is to sort the contents so all any duplicate cells are next to each other, then use and IF function to say if the current cell is equal to the cell before, output zero

 

[elemenopee]

If you don't have too much data, I've always done the above as per wetbank17's advice or I use conditional formatting.

ie
On the tool bar - Home.

Click on the Conditional Formatting drop down menu
Highlight Cell Rules
Duplicate Values
I then set "Values with" to a custom format of fill with yellow
Sort cells by colour with yellow on the top
Duplicates will be displayed at the top of the list highlighted in yellow