I have a very large spreadsheet with the first column showing dates of all
weekdays from 1/2/1900 through the present. the rest of the columns (about
500 of them are different categories. Each date may have one or more of
those categories. Eacch row consists of a date, withabout 500 cells across
which may or may not have data, (Usually a 1, or a -1, or a 0 or a 2 or a
4)depending on which category it is. I need to keep the spreadsheet this
way, but I need something else that will show me on the leftr column the
dates, and the rows across will show only those categories that have data in
it.

This is what my spreadsheet looks like now, in miniature.
DAte Category1 Category 2 Category 3
1/2/00 1
1/3/00 2 0
1/4/00 1
1/5/00 1 1

Th is is what I need to see
Date whichcategory? Which Category? Which Category?
1/2/00 Category 2
1/3/00 Category 1 Category 3
1/4/00 Category 1
1/5/00 Category 2 Category 3

I hope this is clear. Thank you.

--
newyorkjoy
thanks for the help!

You could try this against a copy of your worksheet(!).

Select the data range--no row headers, no column headers.

In your test data, I selected B2:D5
Then Edit|goto|special|Constants
(convert any formulas to constants before this step)
Notice that the selection changed to just the cells with something in them

Take note of the activecell's column.
(For me, the activecell was C2
Type this formula in the cell:
=c$1
(change the letter to match the column the activecell is in. But use $1. It
means that the formula will always point to row 1.)

Now hit ctrl-enter (instead of enter)
The selected cells now have a formula that points to row 1 of the column that
it's in.

Select the whole range again (B2:D5) for me
edit|copy
Edit|Paste special|values

With the whole range selected still
edit|goto|special|Blanks
Notice that just the empty cells are selected.

Edit|Delete
Choose shift cells left




newyorkjoy wrote:

I have a very large spreadsheet with the first column showing dates of all
weekdays from 1/2/1900 through the present. the rest of the columns (about
500 of them are different categories. Each date may have one or more of
those categories. Eacch row consists of a date, withabout 500 cells across
which may or may not have data, (Usually a 1, or a -1, or a 0 or a 2 or a
4)depending on which category it is. I need to keep the spreadsheet this
way, but I need something else that will show me on the leftr column the
dates, and the rows across will show only those categories that have data in
it.

This is what my spreadsheet looks like now, in miniature.
DAte Category1 Category 2 Category 3
1/2/00 1
1/3/00 2 0
1/4/00 1
1/5/00 1 1

Th is is what I need to see
Date whichcategory? Which Category? Which Category?
1/2/00 Category 2
1/3/00 Category 1 Category 3
1/4/00 Category 1
1/5/00 Category 2 Category 3

I hope this is clear. Thank you.

--
newyorkjoy
thanks for the help!

--

Dave Peterson

Dave, thank you , but when I do edit, go to, special, constants, it selects
the entire worksheet, not just the constants. Also, what is the "activecell"?

--
newyorkjoy
thanks for the help!


"Dave Peterson" wrote:

You could try this against a copy of your worksheet(!).

Select the data range--no row headers, no column headers.

In your test data, I selected B2:D5
Then Edit|goto|special|Constants
(convert any formulas to constants before this step)
Notice that the selection changed to just the cells with something in them

Take note of the activecell's column.
(For me, the activecell was C2
Type this formula in the cell:
=c$1
(change the letter to match the column the activecell is in. But use $1. It
means that the formula will always point to row 1.)

Now hit ctrl-enter (instead of enter)
The selected cells now have a formula that points to row 1 of the column that
it's in.

Select the whole range again (B2:D5) for me
edit|copy
Edit|Paste special|values

With the whole range selected still
edit|goto|special|Blanks
Notice that just the empty cells are selected.

Edit|Delete
Choose shift cells left




newyorkjoy wrote:

I have a very large spreadsheet with the first column showing dates of all
weekdays from 1/2/1900 through the present. the rest of the columns (about
500 of them are different categories. Each date may have one or more of
those categories. Eacch row consists of a date, withabout 500 cells across
which may or may not have data, (Usually a 1, or a -1, or a 0 or a 2 or a
4)depending on which category it is. I need to keep the spreadsheet this
way, but I need something else that will show me on the leftr column the
dates, and the rows across will show only those categories that have data in
it.

This is what my spreadsheet looks like now, in miniature.
DAte Category1 Category 2 Category 3
1/2/00 1
1/3/00 2 0
1/4/00 1
1/5/00 1 1

Th is is what I need to see
Date whichcategory? Which Category? Which Category?
1/2/00 Category 2
1/3/00 Category 1 Category 3
1/4/00 Category 1
1/5/00 Category 2 Category 3

I hope this is clear. Thank you.

--
newyorkjoy
thanks for the help!

--

Dave Peterson

Select A1:C3 (9 cells)
Notice that you have 8 cells that are shaded and one that is white.

The activecell is that white cell.

Are you sure you correctly selected the starting range?

Are you sure that those cells that look empty are really empty?

newyorkjoy wrote:

Dave, thank you , but when I do edit, go to, special, constants, it selects
the entire worksheet, not just the constants. Also, what is the "activecell"?

--
newyorkjoy
thanks for the help!

"Dave Peterson" wrote:

You could try this against a copy of your worksheet(!).

Select the data range--no row headers, no column headers.

In your test data, I selected B2:D5
Then Edit|goto|special|Constants
(convert any formulas to constants before this step)
Notice that the selection changed to just the cells with something in them

Take note of the activecell's column.
(For me, the activecell was C2
Type this formula in the cell:
=c$1
(change the letter to match the column the activecell is in. But use $1. It
means that the formula will always point to row 1.)

Now hit ctrl-enter (instead of enter)
The selected cells now have a formula that points to row 1 of the column that
it's in.

Select the whole range again (B2:D5) for me
edit|copy
Edit|Paste special|values

With the whole range selected still
edit|goto|special|Blanks
Notice that just the empty cells are selected.

Edit|Delete
Choose shift cells left




newyorkjoy wrote:

I have a very large spreadsheet with the first column showing dates of all
weekdays from 1/2/1900 through the present. the rest of the columns (about
500 of them are different categories. Each date may have one or more of
those categories. Eacch row consists of a date, withabout 500 cells across
which may or may not have data, (Usually a 1, or a -1, or a 0 or a 2 or a
4)depending on which category it is. I need to keep the spreadsheet this
way, but I need something else that will show me on the leftr column the
dates, and the rows across will show only those categories that have data in
it.

This is what my spreadsheet looks like now, in miniature.
DAte Category1 Category 2 Category 3
1/2/00 1
1/3/00 2 0
1/4/00 1
1/5/00 1 1

Th is is what I need to see
Date whichcategory? Which Category? Which Category?
1/2/00 Category 2
1/3/00 Category 1 Category 3
1/4/00 Category 1
1/5/00 Category 2 Category 3

I hope this is clear. Thank you.

--
newyorkjoy
thanks for the help!

--

Dave Peterson


--

Dave Peterson

The empty cells are absolutely empty. I am using excel 2007, so I went to
the Home tab and then at the far right went to find and select and went to
goto special constants. Then some cells were not selected anymore, but most
of the cells were, including the empty ones. Also, I don't have any row or
column headers, do I just select the entire worksheet? or do I not select
column one that has the dates, and row one that has the names of the
categories?

newyorkjoy
thanks for the help!


"Dave Peterson" wrote:

Select A1:C3 (9 cells)
Notice that you have 8 cells that are shaded and one that is white.

The activecell is that white cell.

Are you sure you correctly selected the starting range?

Are you sure that those cells that look empty are really empty?

newyorkjoy wrote:

Dave, thank you , but when I do edit, go to, special, constants, it selects
the entire worksheet, not just the constants. Also, what is the "activecell"?

--
newyorkjoy
thanks for the help!

"Dave Peterson" wrote:

You could try this against a copy of your worksheet(!).

Select the data range--no row headers, no column headers.

In your test data, I selected B2:D5
Then Edit|goto|special|Constants
(convert any formulas to constants before this step)
Notice that the selection changed to just the cells with something in them

Take note of the activecell's column.
(For me, the activecell was C2
Type this formula in the cell:
=c$1
(change the letter to match the column the activecell is in. But use $1. It
means that the formula will always point to row 1.)

Now hit ctrl-enter (instead of enter)
The selected cells now have a formula that points to row 1 of the column that
it's in.

Select the whole range again (B2:D5) for me
edit|copy
Edit|Paste special|values

With the whole range selected still
edit|goto|special|Blanks
Notice that just the empty cells are selected.

Edit|Delete
Choose shift cells left




newyorkjoy wrote:

I have a very large spreadsheet with the first column showing dates of all
weekdays from 1/2/1900 through the present. the rest of the columns (about
500 of them are different categories. Each date may have one or more of
those categories. Eacch row consists of a date, withabout 500 cells across
which may or may not have data, (Usually a 1, or a -1, or a 0 or a 2 or a
4)depending on which category it is. I need to keep the spreadsheet this
way, but I need something else that will show me on the leftr column the
dates, and the rows across will show only those categories that have data in
it.

This is what my spreadsheet looks like now, in miniature.
DAte Category1 Category 2 Category 3
1/2/00 1
1/3/00 2 0
1/4/00 1
1/5/00 1 1

Th is is what I need to see
Date whichcategory? Which Category? Which Category?
1/2/00 Category 2
1/3/00 Category 1 Category 3
1/4/00 Category 1
1/5/00 Category 2 Category 3

I hope this is clear. Thank you.

--
newyorkjoy
thanks for the help!

--

Dave Peterson


--

Dave Peterson

First, those cells that look empty that are still selected are not empty.

Try selecting a few of those cells manually and then hit the Delete key on the
keyboard to clear the contents. Then test it again. You'll see that these
cells that you just fixed are not in the selection after you used
edit|goto|special|constants.

So if you want to try that technique that I suggested, you're going to have to
really clear those cells.

Try this using an offending cell (I used A1 as my cell).

Find two empty cells and put this into those cells:
=len(a1)
=counta(a1)

If the =len() formula returns 0, but =counta() returns 1, then maybe you had
formulas that evaluated to "" that were converted to values.

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

=======
If that doesn't help, post back the results of those two formulas.

newyorkjoy wrote:

The empty cells are absolutely empty. I am using excel 2007, so I went to
the Home tab and then at the far right went to find and select and went to
goto special constants. Then some cells were not selected anymore, but most
of the cells were, including the empty ones. Also, I don't have any row or
column headers, do I just select the entire worksheet? or do I not select
column one that has the dates, and row one that has the names of the
categories?

newyorkjoy
thanks for the help!

"Dave Peterson" wrote:

Select A1:C3 (9 cells)
Notice that you have 8 cells that are shaded and one that is white.

The activecell is that white cell.

Are you sure you correctly selected the starting range?

Are you sure that those cells that look empty are really empty?

newyorkjoy wrote:

Dave, thank you , but when I do edit, go to, special, constants, it selects
the entire worksheet, not just the constants. Also, what is the "activecell"?

--
newyorkjoy
thanks for the help!

"Dave Peterson" wrote:

You could try this against a copy of your worksheet(!).

Select the data range--no row headers, no column headers.

In your test data, I selected B2:D5
Then Edit|goto|special|Constants
(convert any formulas to constants before this step)
Notice that the selection changed to just the cells with something in them

Take note of the activecell's column.
(For me, the activecell was C2
Type this formula in the cell:
=c$1
(change the letter to match the column the activecell is in. But use $1. It
means that the formula will always point to row 1.)

Now hit ctrl-enter (instead of enter)
The selected cells now have a formula that points to row 1 of the column that
it's in.

Select the whole range again (B2:D5) for me
edit|copy
Edit|Paste special|values

With the whole range selected still
edit|goto|special|Blanks
Notice that just the empty cells are selected.

Edit|Delete
Choose shift cells left




newyorkjoy wrote:

I have a very large spreadsheet with the first column showing dates of all
weekdays from 1/2/1900 through the present. the rest of the columns (about
500 of them are different categories. Each date may have one or more of
those categories. Eacch row consists of a date, withabout 500 cells across
which may or may not have data, (Usually a 1, or a -1, or a 0 or a 2 or a
4)depending on which category it is. I need to keep the spreadsheet this
way, but I need something else that will show me on the leftr column the
dates, and the rows across will show only those categories that have data in
it.

This is what my spreadsheet looks like now, in miniature.
DAte Category1 Category 2 Category 3
1/2/00 1
1/3/00 2 0
1/4/00 1
1/5/00 1 1

Th is is what I need to see
Date whichcategory? Which Category? Which Category?
1/2/00 Category 2
1/3/00 Category 1 Category 3
1/4/00 Category 1
1/5/00 Category 2 Category 3

I hope this is clear. Thank you.

--
newyorkjoy
thanks for the help!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hmmm.... that's a complicated one. Try this.

Start up a macro under visual basic editor and paste this code. Make sure
you start the code by putting the active cell on the first date in the first
column. And just in case saveas a test sheet, because if you screws up you
can't get your data back. But it should work. The one thing you gotta do is
move the Column letter to the last column in your data sheet. So I put it
down below... it looks like this...

ActiveCell.Offset(0, 1).Range("A1:G1").Select

Change G1 to your your last column of data.

Sub Test()

Do Until ActiveCell.Offset(2, 0).Value = ""

ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(NOT(R[-1]C=""""),R1C)"
ActiveCell.Select
Selection.Copy


ActiveCell.Offset(0, 1).Range("A1:G1").Select


ActiveSheet.Paste
ActiveCell.Rows("1:1").EntireRow.Select
Selection.SpecialCells(xlCellTypeFormulas, 20).Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.EntireRow.Copy

On Error Resume Next

ActiveCell.EntireRow.PasteSpecial (xlPasteValues)
ActiveCell.Offset(-1, 0).EntireRow.Delete
Loop

End Sub

"newyorkjoy" wrote:

I have a very large spreadsheet with the first column showing dates of all
weekdays from 1/2/1900 through the present. the rest of the columns (about
500 of them are different categories. Each date may have one or more of
those categories. Eacch row consists of a date, withabout 500 cells across
which may or may not have data, (Usually a 1, or a -1, or a 0 or a 2 or a
4)depending on which category it is. I need to keep the spreadsheet this
way, but I need something else that will show me on the leftr column the
dates, and the rows across will show only those categories that have data in
it.

This is what my spreadsheet looks like now, in miniature.
DAte Category1 Category 2 Category 3
1/2/00 1
1/3/00 2 0
1/4/00 1
1/5/00 1 1

Th is is what I need to see
Date whichcategory? Which Category? Which Category?
1/2/00 Category 2
1/3/00 Category 1 Category 3
1/4/00 Category 1
1/5/00 Category 2 Category 3

I hope this is clear. Thank you.

--
newyorkjoy
thanks for the help!