Use Equations to find duplicates and sort KEEPING the duplicates

[Zanatos1986]

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:

 

[duckandcover]

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?

 

[Eric W]

I also can't see the picture, but from your description, this might be what you're looking for:

	A	B	C	D	E	F	G
1		Part		Date		Part	Date
2		a1		1-Jan		a1	1-Jan
3		b2		1-Feb		a1	1-Mar
4		a1		1-Mar		b2	1-Feb
5		z9		1-Apr		b2	1-May
6		b2		1-May		d8	1-Aug
7		z9		1-Jun		g4	1-Jul
8		g4		1-Jul		z9	1-Apr
9		d8		1-Aug		z9	1-Jun
10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula F2 {=INDEX(B:B,MIN(IF(B2:B20<>"",IF(COUNTIF(B2:B20,"<"&B2:B20)=0,ROW(B2:B20)))))} F3 {=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))))))} G2 {=IF(F2="","",INDEX(D:D,SMALL(IF($B$2:$B$20=F2,ROW($B$2:$B$20)),COUNTIF($F$2:$F2,F2))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.

 

[Zanatos1986]

I also can't see the picture, but from your description, this might be what you're looking for:

	A	B	C	D	E	F	G
1		Part		Date		Part	Date
2		a1		1-Jan		a1	1-Jan
3		b2		1-Feb		a1	1-Mar
4		a1		1-Mar		b2	1-Feb
5		z9		1-Apr		b2	1-May
6		b2		1-May		z9	1-Apr
7		z9		1-Jun		z9	1-Jun
8		g4		1-Jul		g4	1-Jul
9		d8		1-Aug		d8	1-Aug
10

<tbody>
</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.

 

[Eric W]

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:

	A	B	C	D	E	F	G
1		Part		Date		Part	Date
2		a1		1-Jan		a1	1-Jan
3		b2		1-Feb		a1	1-Mar
4		a1		1-Mar		b2	1-Feb
5		z9		1-Apr		b2	1-May
6		b2		1-May		z9	1-Apr
7		z9		1-Jun		z9	1-Jun
8		g4		1-Jul		g4	1-Jul
9		d8		1-Aug		d8	1-Aug
10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula F2 =B2 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula F3 {=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))))))} G2 {=IF(F2="","",INDEX(D:D,SMALL(IF($B$2:$B$20=F2,ROW($B$2:$B$20)),COUNTIF($F$2:$F2,F2))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The F2 and F3 formulas have changed, G2 is the same.

 

[Zanatos1986]

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(

 

[Eric W]

The F3 formula should be:

=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.

 

[Zanatos1986]

Perfect

Thanks Eric!

 

[Eric W]

You're welcome.