Excel 2007, showing dups . . .both rows . .

[Jeffrey Green]

I have about 35,000 rows, with Part Number in column B and sorted by Column B.

About 400 are duplicates and I need to see just the duplicate row AND the row above it (the original) . . .

What is the best way to do this with formulas?

Thanks

 

[anniekes]

I have about 35,000 rows, with Part Number in column B and sorted by Column B.

About 400 are duplicates and I need to see just the duplicate row AND the row above it (the original) . . .

What is the best way to do this with formulas?

Thanks

did u try to arrange the numbers by smallest / largest ? .. it should display the duplicates one after another

to do this, you need to introduce all the numbers into a table as the table would give you option to sort numbers.

if you don't know how to insert a table, i'm sure you'll be able to find help on this either through this forum or google.

 

[Jeffrey Green]

The numbers are in one column (B) and sorted.
I highlighted the dups with Excel 2007 conditional formatting, but when I try to AutoFilter by Color, it takes over 10 minutes (or more) to sort by color.

 

[Jeffrey Green]

Perhaps I wasn't clear . . . I ONLY want to see the Duplicate rows and the row above it . . . I do NOT want to see the other 30,000 rows.

 

[anniekes]

The numbers are in one column (B) and sorted.
I highlighted the dups with Excel 2007 conditional formatting, but when I try to AutoFilter by Color, it takes over 10 minutes (or more) to sort by color.

place this in column B of your table then sort column B by largest.
=COUNTIF($A$2:$A$50,A2)=1

i hope it helps in terms of speed and efficiency

Edit: if it's not helping, another thing you can do is to insert the forumla above in the column B, but then copy all the columns to a text file and paste again the content of the text file to a new sheet, where you'll sort by column b largets. this way it might be easier for the computer to process the info as there a are no formulas there.