Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Merging Dates in Excel 2007

Nov 9, 2010 3:12AM PST

So, I've got this issue. I have a document with about 5000 cells that have individually listed dates; I need to identify all of the concurrent periods and merge them into one cell. So this:

Last First Rank 10/01/2010
Last First Rank 10/02/2010
Last First Rank 10/03/2010
Last First Rank 10/04/2010

Needs to become one cell that looks like this:

Last First Rank 10/01/2010-10/04/2010

At the rate I'm going, manually processing all of this will take about 18 hours. I was given this assignment an hour ago and given until first thing tomorrow morning to get it done. Are there any shortcuts or anything excel can do to help me with this?

Discussion is locked

- Collapse -
It's a big task
Nov 9, 2010 4:35AM PST

but in the time you've been given to complete it, I don't know of any way to make it easier or quicker.

I assume that some sort of macro would do it, but at the moment I wouldn't know where to start, and that would take time to write and test anyway.

Your best bet is to just slug away at it. Perhaps do half an hour, take a 5 minute break, then continue.

Sorry, but that time scale makes this difficult to devise any improved method.

Mark

- Collapse -
Not sure I understand the problem but could you put
Nov 11, 2010 1:05AM PST

the worksheet data into a pivot table ... put the date data under the row label, group the data by dates as desired and label it whatever you want ?

VAPCMD

- Collapse -
What I should do:
Nov 11, 2010 7:05AM PST

add a column with the difference between successive dates. A consecutive series of 1's is rather easy to see with the naked I. Then all you've got to do is to edit the first row of such a consecutive series by adding the date of the last line, and deleting all but the first one.
Still a lot of work, but faster then checking dates.

Kees