| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
showing data in a different way based on a spreadsheet excel 07
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel 07
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel 07
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
Right you are! Most of my blank cells were created by inserting values of a
formula that returned "". Since it is such a large spreadsheet, It took the computer all night to change the blanks to $$$$$. Now I am changing the $$$$$ back to blanks. I will keep you posted. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
If I know I'm converting formulas to values, I'll usually use something like
this: =if(somethingistrue,"realformula",na()) Then I convert to values and replace the na()'s with nothing. It saves that first step of converting to $$$$$ then back to nothing. newyorkjoy wrote: Right you are! Most of my blank cells were created by inserting values of a formula that returned "". Since it is such a large spreadsheet, It took the computer all night to change the blanks to $$$$$. Now I am changing the $$$$$ back to blanks. I will keep you posted. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
HELP! I spent almost a day and anight converting the blanks to $$$$$ and
then the $$$$$ back to blanks and guess what? When I look for constants, I still get the blank cells?! Only the ones that I highlight and then press DEL show up as blanks. That will be impossible for me to do on such a large spreadsheet!! What do I do now? -- newyorkjoy thanks for the help! "Dave Peterson" wrote: If I know I'm converting formulas to values, I'll usually use something like this: =if(somethingistrue,"realformula",na()) Then I convert to values and replace the na()'s with nothing. It saves that first step of converting to $$$$$ then back to nothing. newyorkjoy wrote: Right you are! Most of my blank cells were created by inserting values of a formula that returned "". Since it is such a large spreadsheet, It took the computer all night to change the blanks to $$$$$. Now I am changing the $$$$$ back to blanks. I will keep you posted. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: 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 -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
Do you have any formulas in those cells?
Maybe you can save it as a .csv file and then reopen that .csv file? newyorkjoy wrote: HELP! I spent almost a day and anight converting the blanks to $$$$$ and then the $$$$$ back to blanks and guess what? When I look for constants, I still get the blank cells?! Only the ones that I highlight and then press DEL show up as blanks. That will be impossible for me to do on such a large spreadsheet!! What do I do now? -- newyorkjoy thanks for the help! "Dave Peterson" wrote: If I know I'm converting formulas to values, I'll usually use something like this: =if(somethingistrue,"realformula",na()) Then I convert to values and replace the na()'s with nothing. It saves that first step of converting to $$$$$ then back to nothing. newyorkjoy wrote: Right you are! Most of my blank cells were created by inserting values of a formula that returned "". Since it is such a large spreadsheet, It took the computer all night to change the blanks to $$$$$. Now I am changing the $$$$$ back to blanks. I will keep you posted. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
I originally had formulas in those cells. Immediately afterward, I changed
the formulas to values. The blank cells had that apostrophe you were talking about. I replaced all blank cells with $$$$$, and then immediately replaced all $$$$$ to blank cells. (I am in excel 2007). When I went to look for the constants, they wer still showing up as constants. Also please tell me how I do Tools;Options, Transition tab, Toggle Transition Navigation keys On. in Excel 2007? (I checked this in my smaller original version that was in excel 2003, and that is where I found the apostrophe's, then I did the entire $$$ thing to my excel 2007 file, and it is very frustrating! I can try saving it as a .csv file, but I really don't know what that means. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: Do you have any formulas in those cells? Maybe you can save it as a .csv file and then reopen that .csv file? newyorkjoy wrote: HELP! I spent almost a day and anight converting the blanks to $$$$$ and then the $$$$$ back to blanks and guess what? When I look for constants, I still get the blank cells?! Only the ones that I highlight and then press DEL show up as blanks. That will be impossible for me to do on such a large spreadsheet!! What do I do now? -- newyorkjoy thanks for the help! "Dave Peterson" wrote: If I know I'm converting formulas to values, I'll usually use something like this: =if(somethingistrue,"realformula",na()) Then I convert to values and replace the na()'s with nothing. It saves that first step of converting to $$$$$ then back to nothing. newyorkjoy wrote: Right you are! Most of my blank cells were created by inserting values of a formula that returned "". Since it is such a large spreadsheet, It took the computer all night to change the blanks to $$$$$. Now I am changing the $$$$$ back to blanks. I will keep you posted. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
I just saved it as a csv file and reopenedit as a csv file, and the blanks
are still showing up as constants. What else can I do? -- newyorkjoy thanks for the help! "Dave Peterson" wrote: Do you have any formulas in those cells? Maybe you can save it as a .csv file and then reopen that .csv file? newyorkjoy wrote: HELP! I spent almost a day and anight converting the blanks to $$$$$ and then the $$$$$ back to blanks and guess what? When I look for constants, I still get the blank cells?! Only the ones that I highlight and then press DEL show up as blanks. That will be impossible for me to do on such a large spreadsheet!! What do I do now? -- newyorkjoy thanks for the help! "Dave Peterson" wrote: If I know I'm converting formulas to values, I'll usually use something like this: =if(somethingistrue,"realformula",na()) Then I convert to values and replace the na()'s with nothing. It saves that first step of converting to $$$$$ then back to nothing. newyorkjoy wrote: Right you are! Most of my blank cells were created by inserting values of a formula that returned "". Since it is such a large spreadsheet, It took the computer all night to change the blanks to $$$$$. Now I am changing the $$$$$ back to blanks. I will keep you posted. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
I saved it as a csv file, and reopened it as a csv file, and the same problem
is showing up, just here, when I try to replace blank cells with $$$$$, it does not recognize the cells as blank. So maybe there is another step I should do to make the computer recognize these cells are blank. I really can use the help. Thank you. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: Do you have any formulas in those cells? Maybe you can save it as a .csv file and then reopen that .csv file? newyorkjoy wrote: HELP! I spent almost a day and anight converting the blanks to $$$$$ and then the $$$$$ back to blanks and guess what? When I look for constants, I still get the blank cells?! Only the ones that I highlight and then press DEL show up as blanks. That will be impossible for me to do on such a large spreadsheet!! What do I do now? -- newyorkjoy thanks for the help! "Dave Peterson" wrote: If I know I'm converting formulas to values, I'll usually use something like this: =if(somethingistrue,"realformula",na()) Then I convert to values and replace the na()'s with nothing. It saves that first step of converting to $$$$$ then back to nothing. newyorkjoy wrote: Right you are! Most of my blank cells were created by inserting values of a formula that returned "". Since it is such a large spreadsheet, It took the computer all night to change the blanks to $$$$$. Now I am changing the $$$$$ back to blanks. I will keep you posted. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
You found how to save the file as a CSV.
Office Button|Excel Options (at the bottom)|Advanced Section Near the bottom in the Lotus Compatibility Section Check/uncheck Transition Navigation keys. And it sounds like you have a lot of data to clean up. I would think that going through the cells one by one would take a long time. But maybe going through the rows in groups of 1000 (or whatever works fast) would be quicker (less slow). Try this against a small number of rows: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim myStep As Long myStep = 100 'do some testing to figure out the best number Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False FirstRow = 1 LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row 'just check the first 1000 while testing For iRow = FirstRow To 1000 Step myStep 'then use this line to get them all 'For iRow = FirstRow To LastRow Step myStep Application.StatusBar = "Processing row #:" & iRow & " at " & Now With .Rows(iRow).Resize(myStep) .Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False .Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False End With Next iRow End With 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode Application.StatusBar = False End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ps. When you were working with the formulas, did you ever use anything like: '=if(...) with that single leading apostrophe (maybe to copy the formula as text and not have excel adjust the formula????? newyorkjoy wrote: I saved it as a csv file, and reopened it as a csv file, and the same problem is showing up, just here, when I try to replace blank cells with $$$$$, it does not recognize the cells as blank. So maybe there is another step I should do to make the computer recognize these cells are blank. I really can use the help. Thank you. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: Do you have any formulas in those cells? Maybe you can save it as a .csv file and then reopen that .csv file? newyorkjoy wrote: HELP! I spent almost a day and anight converting the blanks to $$$$$ and then the $$$$$ back to blanks and guess what? When I look for constants, I still get the blank cells?! Only the ones that I highlight and then press DEL show up as blanks. That will be impossible for me to do on such a large spreadsheet!! What do I do now? -- newyorkjoy thanks for the help! "Dave Peterson" wrote: If I know I'm converting formulas to values, I'll usually use something like this: =if(somethingistrue,"realformula",na()) Then I convert to values and replace the na()'s with nothing. It saves that first step of converting to $$$$$ then back to nothing. newyorkjoy wrote: Right you are! Most of my blank cells were created by inserting values of a formula that returned "". Since it is such a large spreadsheet, It took the computer all night to change the blanks to $$$$$. Now I am changing the $$$$$ back to blanks. I will keep you posted. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
Isn't there a simple way that I can select all those blank cells that have '
in them? Also, some cells have ^ in them. Why is that? How do I prevent new columns that I will be putting in from having these marks embedded in the cells? I would select all those type of cells, and the press Clear Contents. Is that possible? Here is the formula that I have been using to put information in new columns that I add to the spreadsheet. =IF(ISERROR(VLOOKUP(A3,Sheet2!A:B,2,FALSE)),"",(VL OOKUP(A3,Sheet2!A:B,2,FALSE))) I also have a macro set up that changes the formulas to values. Here it is. Selection.Copy Application.Goto Reference:="R3C2:R29281C2" ActiveSheet.Paste Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False End Sub Maybe there is a better way to do this? -- newyorkjoy thanks for the help! "Dave Peterson" wrote: You found how to save the file as a CSV. Office Button|Excel Options (at the bottom)|Advanced Section Near the bottom in the Lotus Compatibility Section Check/uncheck Transition Navigation keys. And it sounds like you have a lot of data to clean up. I would think that going through the cells one by one would take a long time. But maybe going through the rows in groups of 1000 (or whatever works fast) would be quicker (less slow). Try this against a small number of rows: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim myStep As Long myStep = 100 'do some testing to figure out the best number Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False FirstRow = 1 LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row 'just check the first 1000 while testing For iRow = FirstRow To 1000 Step myStep 'then use this line to get them all 'For iRow = FirstRow To LastRow Step myStep Application.StatusBar = "Processing row #:" & iRow & " at " & Now With .Rows(iRow).Resize(myStep) .Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False .Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False End With Next iRow End With 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode Application.StatusBar = False End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ps. When you were working with the formulas, did you ever use anything like: '=if(...) with that single leading apostrophe (maybe to copy the formula as text and not have excel adjust the formula????? newyorkjoy wrote: I saved it as a csv file, and reopened it as a csv file, and the same problem is showing up, just here, when I try to replace blank cells with $$$$$, it does not recognize the cells as blank. So maybe there is another step I should do to make the computer recognize these cells are blank. I really can use the help. Thank you. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: Do you have any formulas in those cells? Maybe you can save it as a .csv file and then reopen that .csv file? newyorkjoy wrote: HELP! I spent almost a day and anight converting the blanks to $$$$$ and then the $$$$$ back to blanks and guess what? When I look for constants, I still get the blank cells?! Only the ones that I highlight and then press DEL show up as blanks. That will be impossible for me to do on such a large spreadsheet!! What do I do now? -- newyorkjoy thanks for the help! "Dave Peterson" wrote: If I know I'm converting formulas to values, I'll usually use something like this: =if(somethingistrue,"realformula",na()) Then I convert to values and replace the na()'s with nothing. It saves that first step of converting to $$$$$ then back to nothing. newyorkjoy wrote: Right you are! Most of my blank cells were created by inserting values of a formula that returned "". Since it is such a large spreadsheet, It took the computer all night to change the blanks to $$$$$. Now I am changing the $$$$$ back to blanks. I will keep you posted. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
showing data in a different way based on a spreadsheet excel
If you look at edit|goto|special, you won't see a way of selecting a range that
contains just what you want. If you use na() instead of "" in the formula, you will see Errors as on of the options in the edit|goto|special dialog. Those ' ^ prefix characters are left over from Lotus 123. ' means to left justify the value " means to right justify the value ^ means to center the value in the cell / means to fill/repeat the character The only way I know how to eliminate these is to go cell by cell and remove them. Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range 'test a small portion with just the selected area Set myRng = Selection 'or activesheet.usedrange For Each myCell In myRng.Cells If Trim(myCell.Value) = "" Then If myCell.PrefixCharacter < "" Then myCell.Clear End If End If Next myCell End Sub You could use na() instead of "" in the formula. newyorkjoy wrote: Isn't there a simple way that I can select all those blank cells that have ' in them? Also, some cells have ^ in them. Why is that? How do I prevent new columns that I will be putting in from having these marks embedded in the cells? I would select all those type of cells, and the press Clear Contents. Is that possible? Here is the formula that I have been using to put information in new columns that I add to the spreadsheet. =IF(ISERROR(VLOOKUP(A3,Sheet2!A:B,2,FALSE)),"",(VL OOKUP(A3,Sheet2!A:B,2,FALSE))) I also have a macro set up that changes the formulas to values. Here it is. Selection.Copy Application.Goto Reference:="R3C2:R29281C2" ActiveSheet.Paste Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False End Sub Maybe there is a better way to do this? -- newyorkjoy thanks for the help! "Dave Peterson" wrote: You found how to save the file as a CSV. Office Button|Excel Options (at the bottom)|Advanced Section Near the bottom in the Lotus Compatibility Section Check/uncheck Transition Navigation keys. And it sounds like you have a lot of data to clean up. I would think that going through the cells one by one would take a long time. But maybe going through the rows in groups of 1000 (or whatever works fast) would be quicker (less slow). Try this against a small number of rows: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim myStep As Long myStep = 100 'do some testing to figure out the best number Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet .DisplayPageBreaks = False FirstRow = 1 LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row 'just check the first 1000 while testing For iRow = FirstRow To 1000 Step myStep 'then use this line to get them all 'For iRow = FirstRow To LastRow Step myStep Application.StatusBar = "Processing row #:" & iRow & " at " & Now With .Rows(iRow).Resize(myStep) .Replace what:="", replacement:="$$$$$", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False .Replace what:="$$$$$", replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False End With Next iRow End With 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode Application.StatusBar = False End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ps. When you were working with the formulas, did you ever use anything like: '=if(...) with that single leading apostrophe (maybe to copy the formula as text and not have excel adjust the formula????? newyorkjoy wrote: I saved it as a csv file, and reopened it as a csv file, and the same problem is showing up, just here, when I try to replace blank cells with $$$$$, it does not recognize the cells as blank. So maybe there is another step I should do to make the computer recognize these cells are blank. I really can use the help. Thank you. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: Do you have any formulas in those cells? Maybe you can save it as a .csv file and then reopen that .csv file? newyorkjoy wrote: HELP! I spent almost a day and anight converting the blanks to $$$$$ and then the $$$$$ back to blanks and guess what? When I look for constants, I still get the blank cells?! Only the ones that I highlight and then press DEL show up as blanks. That will be impossible for me to do on such a large spreadsheet!! What do I do now? -- newyorkjoy thanks for the help! "Dave Peterson" wrote: If I know I'm converting formulas to values, I'll usually use something like this: =if(somethingistrue,"realformula",na()) Then I convert to values and replace the na()'s with nothing. It saves that first step of converting to $$$$$ then back to nothing. newyorkjoy wrote: Right you are! Most of my blank cells were created by inserting values of a formula that returned "". Since it is such a large spreadsheet, It took the computer all night to change the blanks to $$$$$. Now I am changing the $$$$$ back to blanks. I will keep you posted. -- newyorkjoy thanks for the help! "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data in Excel not showing up after leaving that field | Excel Discussion (Misc queries) | |||
| Excel Spreadsheet Trace Precedent Bars not showing up | Excel Worksheet Functions | |||
| Showing data as spreadsheet and a line graph. | New Users to Excel | |||
| Excel - data not showing in some cells | Excel Discussion (Misc queries) | |||
| Excel Data not showing up in Query | Excel Worksheet Functions | |||
Linear Mode
