 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2007showing 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.  Signaturenewyorkjoy 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 > 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 [quoted text clipped - 25 lines] > newyorkjoy > thanks for the help! SignatureDave 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"? Signaturenewyorkjoy thanks for the help! > You could try this against a copy of your worksheet(!). > [quoted text clipped - 56 lines] > > newyorkjoy > > thanks for the help! 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? > 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"? [quoted text clipped - 67 lines] > > > > Dave Peterson SignatureDave 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! > Select A1:C3 (9 cells) > Notice that you have 8 cells that are shaded and one that is white. [quoted text clipped - 76 lines] > > > > > > 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. > 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 [quoted text clipped - 91 lines] > > > > Dave Peterson SignatureDave Peterson 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. Signaturenewyorkjoy thanks for the help! > First, those cells that look empty that are still selected are not empty. > [quoted text clipped - 136 lines] > > > > > > Dave Peterson 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. > 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 [quoted text clipped - 148 lines] > > > > Dave Peterson SignatureDave Peterson 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? Signaturenewyorkjoy thanks for the help! > If I know I'm converting formulas to values, I'll usually use something like > this: [quoted text clipped - 157 lines] > > > > > > Dave Peterson Do you have any formulas in those cells? Maybe you can save it as a .csv file and then reopen that .csv file? > 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 [quoted text clipped - 170 lines] > > > > Dave Peterson SignatureDave Peterson 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. Signaturenewyorkjoy thanks for the help! > Do you have any formulas in those cells? > [quoted text clipped - 174 lines] > > > > > > Dave Peterson 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? Signaturenewyorkjoy thanks for the help! > Do you have any formulas in those cells? > [quoted text clipped - 174 lines] > > > > > > Dave Peterson 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. Signaturenewyorkjoy thanks for the help! > Do you have any formulas in those cells? > [quoted text clipped - 174 lines] > > > > > > Dave Peterson 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????? > 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 [quoted text clipped - 187 lines] > > > > Dave Peterson SignatureDave Peterson 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)),"",(VLOOKUP(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? Signaturenewyorkjoy thanks for the help! > You found how to save the file as a CSV. > [quoted text clipped - 259 lines] > > > > > > Dave Peterson 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. > 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 [quoted text clipped - 290 lines] > > > > Dave Peterson SignatureDave 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 > 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 [quoted text clipped - 21 lines] > > I hope this is clear. Thank you. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.