Hi, I hope this is the correct forum to post in but there is nothing else obvious. I want to group together all duplicates in a spreadsheet. Let's keep it simple, an ID which appears multiple times in the worksheet - let's look at ID, date, time (assume that the semi colons are cell separators)

1234;27-jan-2010;10:00:00
3579;27-jan-2010;12:30:00
1234;27-jan-2010;15:31:00

so 1234 appears 2 times today. First of all I get the IDs sorted - sort on column A:

1234;27-jan-2010;10:00:00
1234;27-jan-2010;15:31:00
3579;27-jan-2010;12:30:00

Now I want to add a column to highlight duplicates so I get a new column B, in B1 I add =if(a1=a2;"*";" ") and drag this down through the file giving me:

1234;*;27-jan-2010;10:00:00
1234; ;27-jan-2010;15:31:00
3579; ;27-jan-2010;12:30:00

Obviously the file is much bigger. I want to group all the asterisks together. On the surface I sort on column B but this column contains a formula so the response is unpredictable.

I am sure there is some function (USE VALUE?) where I can redefine the formula as the value it returns. In this case the cells would assume the values "*" or " " enabling me to sort.

Does this make any sense? Is there a USE VALUE function as I can not see it in any of the tutorials. TIA

What you are trying to do is on the edge of what a spreadsheet can do and where a database takes over. However, when I have had similar problems I have normally resorted to using another sheet. You can refer to the data onto the second sheet and sort this sheet, because the original sheet will remain in the format given and the asterisks on the second sheet should group as you require.

1 members found this post helpful.

thanks graemef. I will try the 2nd sheet and see what I can manage.