Excel Formatting Question

[CoxTech1]

Hopefully somebody will know the answer to this. I have a SQL query in
an Excel spreadsheet that returns the following data:

jimjones 6/25/2008 7:58:35 6/25/2008 19:12
jimjones 6/27/2008 8:50:25 6/27/2008 20:00
jimjones 6/28/2008 8:47:48 6/28/2008 14:54
jimjones 6/28/2008 14:57:57 6/28/2008 20:02
jimjones 7/1/2008 8:40:45 7/1/2008 20:16
jimjones 7/2/2008 7:51:33 7/2/2008 13:06
jimjones 7/2/2008 13:06:32 7/2/2008 19:00
jimjones 7/4/2008 8:48:48 7/4/2008 20:01
bobsmith 6/25/2008 12:59:50 6/25/2008 14:13
bobsmith 6/25/2008 14:13:43 6/25/2008 14:30
bobsmith 6/25/2008 14:30:54 6/25/2008 17:01
bobsmith 6/25/2008 17:01:36 6/25/2008 17:14
bobsmith 6/25/2008 17:14:25 6/25/2008 17:27
bobsmith 6/25/2008 17:27:05 6/25/2008 18:14
bobsmith 6/25/2008 18:14:29 6/25/2008 18:18
bobsmith 6/25/2008 18:18:59 6/25/2008 19:52
bobsmith 6/25/2008 19:52:17 6/25/2008 20:15
bobsmith 6/25/2008 20:15:53 6/25/2008 20:21
bobsmith 6/25/2008 20:21:17 6/25/2008 21:27


I would like to know if there is a way to automate the sheet in such a
way that the output looks more like this:

jimjones 6/25/2008 7:58:35 6/25/2008 19:12
6/27/2008 8:50:25 6/27/2008 20:00
6/28/2008 8:47:48 6/28/2008 14:54
6/28/2008 14:57:57 6/28/2008 20:02
7/1/2008 8:40:45 7/1/2008 20:16
7/2/2008 7:51:33 7/2/2008 13:06
7/2/2008 13:06:32 7/2/2008 19:00
7/4/2008 8:48:48 7/4/2008 20:01
bobsmith 6/25/2008 12:59:50 6/25/2008 14:13
6/25/2008 14:13:43 6/25/2008 14:30
6/25/2008 14:30:54 6/25/2008 17:01
6/25/2008 17:01:36 6/25/2008 17:14
6/25/2008 17:14:25 6/25/2008 17:27
6/25/2008 17:27:05 6/25/2008 18:14
6/25/2008 18:14:29 6/25/2008 18:18
6/25/2008 18:18:59 6/25/2008 19:52
6/25/2008 19:52:17 6/25/2008 20:15
6/25/2008 20:15:53 6/25/2008 20:21
6/25/2008 20:21:17 6/25/2008 21:27


Any help would be greatly appreciated.

 

[Pete_UK]

One way to get that effect is to highlght all the cells in column A
from the second one downwards. Then click on Format | Conditional
Formatting, and then select Formula Is rather than Cell Value Is in
the first box and in the formula box put this:

=A2=A1

then click on the Format button and Color, and then choose white.
Click OK twice to exit the dialogue box.

The cells in column A that are the same as the one above will all now
appear blank (white on white), although the values are still there.

Hope this helps.

Pete

 

[CoxTech1]

So close but now I get this:

6/25/2008 7:58:35 6/25/2008 19:12
6/27/2008 8:50:25 6/27/2008 20:00
6/28/2008 8:47:48 6/28/2008 14:54
6/28/2008 14:57:57 6/28/2008 20:02
7/1/2008 8:40:45 7/1/2008 20:16
7/2/2008 7:51:33 7/2/2008 13:06
7/2/2008 13:06:32 7/2/2008 19:00
jimjones 7/4/2008 8:48:48 7/4/2008 20:01
6/25/2008 12:59:50 6/25/2008 14:13
6/25/2008 14:13:43 6/25/2008 14:30
6/25/2008 14:30:54 6/25/2008 17:01
6/25/2008 17:01:36 6/25/2008 17:14
6/25/2008 17:14:25 6/25/2008 17:27
6/25/2008 17:27:05 6/25/2008 18:14
6/25/2008 18:14:29 6/25/2008 18:18
6/25/2008 18:18:59 6/25/2008 19:52
6/25/2008 19:52:17 6/25/2008 20:15
6/25/2008 20:15:53 6/25/2008 20:21
bobsmith 6/25/2008 20:21:17 6/25/2008 21:27


Is there a way to conceal the entrys after the 1st instead of the one's
before the last in the grouping?

 

[Pete_UK]

I did say that you should highlight the cells starting with the second
one, i.e. from A2 downwards assuming you have no header row and the
first data item is in A1. Thus, A1 does not get affected, and when you
get to cell A9 this is not the same as A8 so that is not affected.
It's just a matter of selecting the correct cells. If you do have a
header row then you will need to select from A3 downwards, and use the
formula:

=A3=A2

in the CF dialogue.

Hope this helps.

Pete

 

[CoxTech1]

I see now, it's kind of an ugly way to fix but it gets the required visual
result.

 

[Pete_UK]

Yes, but sometimes it helps to keep the data there (and not be seen)
rather than delete it. But, this depends on what else you want to do
with the data - if you wanted a count of Bob Smith's activities then
you would need the data to be in column A.

Another way would be to insert a new column B and put these formulae
in:

B1: =A1
B2: =IF(A2=A1,"",A2)

then copy B2 down by double-clicking the fill handle (the small black
square in the bottom right corner of the cursor). Then you could just
hide column A, so the full data is still there if you need it.

Hope this helps.

Pete