Use Equations to find duplicates and sort KEEPING the duplicates

Z

Zanatos1986

New Member
Joined
Apr 15, 2010
Messages
48
I want to do this in equation form -
Column D has a date, everything has been sorted by this date (newest to oldest)
Column B has a part number, there can be the same part number at multiple dates.
Find any duplicates in column B, and move them so that they are in order and all values are kept.

Pictorial example:

image.jpg
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
I cannot see the picture due to 3rd party settings

Is this something that can be done using conditional formatting to highlight duplicates

I'm not sure what you mean by sort them, are you meaning if the same part is bought multiple times on the same day you'd like them to show beneath each other on the spreadsheet within the date?
 
Upvote 0
I also can't see the picture, but from your description, this might be what you're looking for:

ABCDEFG
PartDatePartDate
a1a1
b2a1
a1b2
z9b2
b2d8
z9g4
g4z9
d8z9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Jan[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Feb[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Mar[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Mar[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Feb[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Apr[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-May[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-May[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Aug[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Jun[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Jul[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Jul[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Apr[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Aug[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Jun[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=INDEX(B:B,MIN(IF(B2:B20<>"",IF(COUNTIF(B2:B20,"<"&B2:B20)=0,ROW(B2:B20)))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]{=IF(COUNTIF($F$2:$F2,F2)<COUNTIF($B$2:$B$20,F2),F2,IF(ROW()>COUNTA($B:$B),"",INDEX(B:B,MIN(IF(COUNTIF($B$2:$B$20,"<"&$B$2:$B$20)=ROW()-2,ROW($B$2:$B$20))))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=IF(F2="","",INDEX(D:D,SMALL(IF($B$2:$B$20=F2,ROW($B$2:$B$20)),COUNTIF($F$2:$F2,F2))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the F2 formula in, change the ranges to match your sheet, confirm with Control+Shift+Enter. Repeat with the F3 formula, then drag down. Repeat with the G2 formula, then drag down. Let us know if this is what you have in mind.
 
Upvote 0
Eric W said:
I also can't see the picture, but from your description, this might be what you're looking for:

ABCDEFG
PartDatePartDate
a1a1
b2a1
a1b2
z9b2
b2z9
z9z9
g4g4
d8d8

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Jan[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Feb[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Mar[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Mar[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Feb[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Apr[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-May[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-May[/TD]
[TD="align: right"][/TD]

[TD="align: right"] 1-Apr [/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Jun[/TD]
[TD="align: right"][/TD]

[TD="align: right"] 1-Jun [/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Jul[/TD]
[TD="align: right"][/TD]

[TD="align: right"] 1-Jul [/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Aug[/TD]
[TD="align: right"][/TD]

[TD="align: right"] 1-Aug [/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

What you have is close, I changed the bottom 4 cells. The date is more vital to stay in order than the part number. It's just moving the duplicates up and keeping the overall date in order.
Click to expand...
 
Upvote 0
OK, it sounds like you don't want the part numbers sorted alphabetically (numerically), you want them listed in the order they're found, just grouped. Try this:

ABCDEFG
PartDatePartDate
a1a1
b2a1
a1b2
z9b2
b2z9
z9z9
g4g4
d8d8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Jan[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Jan[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Feb[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Mar[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Mar[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Feb[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Apr[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-May[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-May[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Apr[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Jun[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Jun[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Jul[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Jul[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]1-Aug[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Aug[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=B2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]{=IF(COUNTIF($F$2:$F2,F2)<COUNTIF($B$2:$B$20,F2),F2,IF(ROW()>COUNTA($B:$B),"",INDEX(B:B,MIN(IF(COUNTIF($F$2:$F2,$B$2:$B$20)=0,ROW($B$2:$B$20))))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=IF(F2="","",INDEX(D:D,SMALL(IF($B$2:$B$20=F2,ROW($B$2:$B$20)),COUNTIF($F$2:$F2,F2))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



The F2 and F3 formulas have changed, G2 is the same.
 
Upvote 0
Is there an issue with F3? I am inserting it exactly as you are but get an error "There's a problem with this formula. Not trying to type a formula? ...."
Error occurs at...
=IF(<font color="Blue" style="background-color: rgb(250, 250, 250);">COUNTIF($F$2:$F2,F2)<countif(<font color="Red">$B$2</countif(
 
Upvote 0
The F3 formula should be:

Code: 
=IF(COUNTIF($F$2:$F2,F2)< COUNTIF($B$2:$B$20,F2),F2,IF(ROW()>COUNTA($B:$B),"",INDEX(B:B,MIN(IF(COUNTIF($F$2:$F2,$B$2:$B$20)=0,ROW($B$2:$B$20))))))

The forum software here sometimes interprets a < as an HTML tag instead of part of a formula. I checked for that before posting, but somehow that one got past me. Sorry. Let me know if this works.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
take a column of daily transaction numbers with duplicates and show in different table with its corresponding date and no duplicates
2
Replies
13
Views
581
Dave Patton
D
P
  • Solved
Sorting Issue
Replies
7
Views
141
kvsrinivasamurthy
K
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
275
BenW71
B
C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
121
Chris258
C
D
  • Question Question
Check if duplicate projects with dates exist
Replies
2
Views
339
dubyarly
D

Forum statistics

Threads
1,225,481
Messages
6,185,239
Members
453,283
Latest member
Shortm88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top