Sorting cells and deleting duplicates except for first instance

[Dan_swx]

Hi There,

I am looking to create a formula or set of formulas that sorts two sets of Data (Start & Finish Times) and then deletes duplicates, except for the original data. I have attached an example spreadsheet of my solution so far, it's almost there, except that my formulas are deleting both instances of the duplicate (the original and the duplicate). I have included more information in my spreadsheet on what my formulas do.


Thanks for any help you can provide.

Dan

[Mallycat]

Thanks for taking the time to describe what you are trying to do. But I would like to understand why are you trying to do this? What are you trying to end up with? Would you be happy with VBA code that cleaned the data up to the end state that you are after? I am a bit sceptical about using forumlae in the way you are doing, because it is so complex and iterative, and you end up with a sheet full of formulae that you can't really do anything with. Having said that, I am sure someone here can help you using this current approach if that is what you want

[estige]

Hi I changed

Please Login or Register  to view this content.

in D2 to

Please Login or Register  to view this content.

Is this the expected result?

[Dan_swx]

It's to do with a rota at work, we're only supposed to have 10 start times a day. And I was hoping that I could get Excel to find them and tell me what they are, I can do the first bit but it gives me masses of data that I need excel to sort for me. I would prefer to stick to formulas than VBA as the machines at work aren't brilliant.

Thanks,
Dan

[estige]

A simple pivot with start as row elements will give you this as well