vlookup with a difference

[jaypatel]

Hi,

i have numerous "duplicated" rows (which i need to keep), where the only duplication is column a .....eg

a b c
1 r0010 124.14 1454
2 r0010 98.15 1455
3 s012 5.25 9852
4 s012 14.54 1045
5 s012 7.25 4876......

what would be the best solution to extract all the rows which has r0010 to another spreadsheet called "extract.xls". So i would need to extract rows 1 and 2 as they both have r0010. Vlookup does not work or i might be confused.

Jay

 

[bjwillingham]

The easiest I think would be to do a quick pivot table on the data, subtotaling by column 1. Then double click on the subtotal amount and Excel will extract all the data to a new tab. You can then move the new tab to a new workbook.

Or you could do an Advanced filter and copy to another location.

 

[bjwillingham]

I read your question too fast. I translated 'best' to easiest.

The best way is using the Advanced Filter.

 

[crimlet]

I have used a advance pivot to do this type of thing before. It is possible to incorporate into a macro and then copy into a new sheet which seems to suit your needs