0 |
Subject: Simple Excel Questions
Posted by: R9
- Leader [02624472] Mon, Mar 21, 2005, 04:55
I'm always running into little roadblocks that I want to ask about, but usually decide isn't worth a new thread. So I figured one thread that we can all ask our small/insignifcant questions in would help. |
Only the 50 most recent replies are currently shown. Click on this text to display hidden posts as well. |
58 | The Beezer Leader
ID: 191202817 Tue, Feb 21, 2006, 23:52
|
Are these cells laid out in a sequential order? If so, you could change to R1C1 format (under Tools-Options in the General tab) then write a looping macro to set the contents of each field. What exactly are you wanting to do?
|
59 | J Leader
ID: 049346417 Wed, Feb 22, 2006, 00:32
|
you could just do a find replace (you'd have to do it twice though)
Find: "A" Replace With: "$A$"
Find: "B" Replace With: "$B$"
|
60 | deraynman
ID: 511124121 Wed, Feb 22, 2006, 00:37
|
58 The Beezer - The cells are randomly filled with formulas. I would explain what the spreadsheet needs to do but...
59 J - This will do the trick. Thanks a lot. Probably saved me hours upon hours of tedious work.
|
61 | Rachael
ID: 185372622 Mon, Jun 26, 2006, 23:37
|
Hello can you please make me a simple test on excel?? Thanks!
|
62 | Kimberly A
ID: 185372622 Mon, Jun 26, 2006, 23:38
|
hello how do you use excel? what is it?
|
63 | TB Sherpa
ID: 031811922 Tue, Jun 27, 2006, 00:47
|
Hey Rachael-Kimberly, maybe this link will help you out: Study abroad in Australia
|
64 | R9
ID: 99392020 Sat, Nov 11, 2006, 12:17
|
I have a spreadsheet that has a schedule in it. (Just a copy/pasted version of KKB's hockey schedule).
I have 4 columns that tell me the amount of games over 7, 14, 21 and 28 days. I do this by counting the number of — over a 7/14/21/28 cell range (those signify off-days), and then subtract 7/14/21/28 by that number.
I use the formula =COUNTIF($M3:$S3,"—") to do this. (This one is for a 7 day period, and row 3 is Anaheim)
My problem is that when a day has gone by, and I delete that day's column from the schedule, the formula adjusts and removes the column. So for today, once Saturday is gone and I delete column M (Sat Nov 11 in my sheet) the formula will read =COUNTIF($M3:$R3,"—") and will only count the next 6 days. Is there a way I can make it so that the formula continues to count M3:S3? The formula is only useful to me if it can update itself... I don't feel like altering 120 formulas each day. :)
Thanks for any help! R9
|
66 | R9
ID: 99392020 Sat, Nov 11, 2006, 14:32
|
I posted over at Excelforum.com and they answered my question very quickly. Saw your post RSF about using INDIRECT, that indeed did the trick.
Looks like a real quality forum over there.
|
67 | RecycledSpinalFluid
ID: 39802115 Sat, Nov 11, 2006, 14:37
|
Could you post what the working solution was for you. I worked on up that work, but seemed overly complex.
|
68 | RecycledSpinalFluid
ID: 39802115 Sat, Nov 11, 2006, 14:38
|
Well, duh...you did.
|
69 | RecycledSpinalFluid
ID: 39802115 Sat, Nov 11, 2006, 14:40
|
A quick BTW, you can eliminate the "x-" portion of the formula by changing the ,"—") portion of your countif to ,"<>—")
|
70 | weykool Leader
ID: 41750315 Thu, Nov 16, 2006, 21:24
|
R9:
I use the same type schedule for basketball. I use the same 28-21-14-7. I dont think that formula works for me because I sort the schedules after I eliminate the current day. That way I always have the teams with the best 28 day schedules at the top. Any thoughts?
|
71 | Kal Zakath
ID: 501025715 Fri, Nov 17, 2006, 18:37
|
What you might want to do is adjust your base formula.
I have a sched analyzer that I have modified to do this.
What I did was put the 7,14,21 day schedules in a formula that I did for every day (i.e. (counta(m4:s4), that I then copied across for the days (n4:t4, o4:u4, etc.). I can then hide columns as I go across and will see just the ones there.
Weykool - you can avoid the sort problem by putting a lookup in if you thing your formulae are messing up when sorted.
|
72 | R9
ID: 99392020 Sat, Nov 18, 2006, 03:41
|
Just checked back here. weykool, without seeing the sheet I'm a bit blind. I'm not as at ease with Excel as RSF and others are. Is your problem that when you delete the column for yesterday, the formulas would change the range they're searching? The INDIRECT addition solved that for me.
=7-COUNTIF(INDIRECT("$K$3:$Q$3"),"—")
If not, you can email it to me if you want and I'll take a look.
(RSF: Yah, I could dump the 7- or the 14-, etc. and just use <>-, but I've already made all the formulas. Next time. :) Thanks for pointing it out though.)
|
74 | KoGs Donor
ID: 387292818 Thu, Dec 14, 2006, 21:37
|
I used to know how to do this many years ago, but have since forgotten. Here's an example of what I want. I have a chart of random numbers:
0 a b c d e f A 2 5 4 3 8 4 B 1 3 5 9 7 3 C 5 3 4 8 2 1 D 1 3 6 4 4 9 E 5 6 1 0 9 4 F 7 6 8 2 1 6
I need to select one number from each column to get the maximum sum. But I can use each row once.
Any help? Thanks.
|
75 | Guru
ID: 330592710 Fri, Dec 15, 2006, 12:32
|
Do you think there's an excel function to solve this?
Is this a small example of a larger problem (i.e., more rows and/or columns) that requires a programmed solution? Or is this the actual problem? If it's the latter, I'd just solve it by hand. (I think it's 45)
|
76 | C.SuperFreak
ID: 3010403013 Fri, Dec 15, 2006, 13:07
|
I believe you can use the max function at the bottom of each column. ie. =Max(a2:a6) then you can put a sum function at the end of the row.
|
77 | KoGs
ID: 28401010 Fri, Dec 15, 2006, 13:17
|
But I can't use a row more than once. So the max function doesn't help me if the max of each column all comes from the same row. Yeah Guru, this is just a small sample of the question. The real question has something like 10 columns and 20 rows.
I thought I remembered doing something like this in my management science class. Where we used this to determine the maximum productivity. Perhaps I remembered wrong. I can't even think of the name of the function we used in excel, to see if it works the same.
|
78 | The Beezer Leader
ID: 191202817 Fri, Dec 15, 2006, 13:25
|
C, that solution doesn't address the one record per row requirement.
I can't prove it, but I strongly suspect that a close to optimal method would be to perform the following algorithm:
1. Find the maximum value in each column. 2. Determine the difference between each value and the next highest value in the column. 3. Select the item with the highest difference (if there is a tie, continue by using the next-highest value until a difference occurs). 4. Strike that row and column from consideration. 5. Go back to 1.
For the example posted, this results in:
f: 9 c: 8 b: 6 a: 5 d: 9 e: 8
Total: 45
Even if it's not optimal, it's the best balance of speed and optimization I can think of. The algorithm should be implementable using VBA in Excel.
|
79 | C.SuperFreak
ID: 3010403013 Fri, Dec 15, 2006, 13:28
|
very tricky. I'll see what I dig up around the office.
|
80 | boikin
ID: 59831214 Fri, Dec 15, 2006, 13:50
|
you could write the problem as linear programming problem then solve using interger setting in the excel solver. it would not be to hard to do and would give the optinum value.
|
81 | KoGs Donor
ID: 387292818 Fri, Dec 15, 2006, 14:05
|
yes Excel Solver. I do believe that's what it was called. Thx boikin. Let me see if it does what I want.
|
82 | KoGs Donor
ID: 387292818 Fri, Dec 15, 2006, 14:50
|
haha I can't figure out how to use the constraint of only using 1 row once.
|
83 | boikin
ID: 59831214 Fri, Dec 15, 2006, 15:09
|
hmmm that is little tricky you have to write contrasit such that if that spot in row is used then it equals one then you set a constrait so that the sum of use (yes/no, 1/0) is equal to 1.
you should make matrix where the values can be 0 or 1 and the value of these 1 or 0's multiply the value of number in the spot. the sum product of the numbers will be the objective to max.
i hope this helps a little kind of difficult thing to explain
|
84 | boikin
ID: 59831214 Fri, Dec 15, 2006, 15:17
|
solver will solve it i got it to give the correct solution.
|
85 | boikin
ID: 59831214 Fri, Dec 15, 2006, 15:18
|
If you can not get it to work post your email address and i will send you excel solution.
|
86 | KoGs Donor
ID: 387292818 Fri, Dec 15, 2006, 16:44
|
pmtang@gmail.com. Like I mentioned above, that was just a sample problem. That's not the actual problem. So if you could send me the excel file that has all the variables and such in solver that would be awesome. Thank you.
|
87 | Rendle Donor
ID: 014815714 Wed, Jan 17, 2007, 16:11
|
For some reason the column headers are numbers instead of letters so now the cells are titled R1C1 instead of A1. There is probably a simple solution. Thanks for your help.
|
88 | Frick Donor
ID: 3410101718 Wed, Jan 17, 2007, 16:24
|
Yes there is.
Tools > Options > General tab
Turning off the R1C1 formatting should do it.
|
89 | Rendle Donor
ID: 014815714 Wed, Jan 17, 2007, 16:28
|
Wow, thank you. What an idiot I am.
|
90 | bibA Leader
ID: 261028117 Sun, Feb 11, 2007, 11:35
|
Could I get someone to tell me how to use italics or bold when posting a message on this site?
Thanks
|
91 | J Leader
ID: 049346417 Sun, Feb 11, 2007, 12:45
|
< I >italics < / I > < B >bold < / B >
just take out the spaces
|
92 | bibA Leader
ID: 261028117 Sun, Feb 11, 2007, 15:22
|
Thanks much J
|
93 | ChicagoTRS
ID: 4110481415 Wed, Mar 14, 2007, 11:38
|
question...
I have a large .csv data file I am importing into Excel...everything works well except when imported there is a blank row between each line of data. So the spreadsheet ends up being twice as long...
Trying to figure a way to simply remove the empty rows....
|
94 | Trip Leader
ID: 13961611 Wed, Mar 14, 2007, 11:49
|
sort...then delete
|
95 | ChicagoTRS
ID: 4110481415 Wed, Mar 14, 2007, 12:01
|
simple enough...thanks...
|
96 | RecycledSpinalFluid Dude
ID: 204401122 Wed, Mar 14, 2007, 12:22
|
Programatically:
Const ForReading = 1 Const ForWriting = 2
Set objArgs = WScript.Arguments If objArgs.Count = 1 Then strInputFileName = trim(objArgs(0)) Else 'No DragDrop Set objDialog = CreateObject("SAFRCFileDlg.FileOpen") x = objDialog.OpenFileOpenDlg If x Then strInputFileName = objDialog.FileName Else WScript.Echo "No File Specified." & vbNewLine & vbNewLine& "Exiting Application." WScript.Quit End If Set objDialog = Nothing End If
strOutputFileName = Left(strInputFileName, InStrRev(strInputFileName,".") - 1) & "_mod.csv"
Set objFSO = CreateObject("Scripting.FileSystemObject") Set objReadFile = objFSO.OpenTextFile(strInputFileName, ForReading) Set objWriteFile = objFSO.CreateTextFile(strOutputFileName, ForWriting)
Do Until objReadFile.AtEndOfStream strValue = objReadFile.ReadLine If strValue <> "" Then objWriteFile.WriteLine strValue End If Loop
objReadFile.Close objWriteFile.Close
Save that as "remove_blanks.vbs", and either drag/drop the CSV on it or double click it and browse to the file.
I was looking it as if you couldn't sort the data.
|
97 | R9 Leader
ID: 02624472 Fri, Mar 30, 2007, 19:21
|
I'm using a web query to import roster value data from my rotohog team. I have to redo the query everytime I re-open the sheet though, as it loses my password. Is there a way to make the sheet or query remember my login info?
|
98 | rockafellerskank
ID: 450122417 Tue, May 08, 2007, 12:27
|
've got several thousand records.... names, addresses, phone numbers, etc.... in Columns A,B,C,B, etc...
Column C is the name column and there are "some" duplicate names mixed in. Is there any way to systematically search Column C for duplicates? (which i will want to delete) Although the name may be a duplicate, columns A,B,D,E, etc... are likely NOT to be.
|
99 | RecycledSpinalFluid
ID: 242143116 Tue, May 08, 2007, 12:52
|
Are you looking to just one name, no matter what is in the other columns? (As in, is the first record in a sort going to be the keeper?)
Also, is the current order of the records important?
|
100 | J Leader
ID: 049346417 Tue, May 08, 2007, 13:47
|
you could use a "countif" formula to count how many times each name shows up. Then delete accordingly.
|
101 | Frick Donor
ID: 3410101718 Tue, May 08, 2007, 14:41
|
You could also apply the AutoFilter.
|
102 | RecycledSpinalFluid Dude
ID: 204401122 Tue, May 08, 2007, 14:46
|
Thats where I was going to go, Frick.
Hightlight column C (with a column header) Data - Filter - Advanced Filter Check "Unique records only" Click OK
|
103 | J Leader
ID: 049346417 Tue, May 08, 2007, 14:48
|
holy crap, I just learned something new, nice job RSF :)
|
104 | rockafellerskank
ID: 450122417 Wed, May 09, 2007, 08:41
|
I tried that. When I select ONLY the column, it won't let me go to Unique Records / Auto Filter.
|
105 | weykool
ID: 34350285 Wed, May 09, 2007, 09:43
|
I think a Pivot Table would do the trick.
Just drag all the columns you want to keep to the row field. All duplicates will be summarized on one line. You may need a bogus field to put in the "data" area. Once you get your pivot table you can eliminate the multiple lines by right clicking on a "total" line and selecting "field settings". Under subtotals you will have the choices of automatic, custom, and none. Select none and repeat until all records are on a single line. This will also give you a listing of same names with different phone numbers.
|
106 | Frick Donor
ID: 3410101718 Wed, May 09, 2007, 16:22
|
RFS, what version of Excel are you using?
|
107 | Trip Leader
ID: 13961611 Fri, May 18, 2007, 14:23
|
Problem. I am joining fields to create a unique record and one of the fields is Date formatted. I have reformatted the column to number, but have to click into the cell in order for the format to take place. Any solution on doing this at once?
|
108 | RecycledSpinalFluid Dude
ID: 204401122 Fri, May 18, 2007, 15:43
|
Could you use:
A1 = "RSF" A2 = "05/18/2007" A3 = "=A1 & TEXT(A2,"d-mmm-yyyy")" [or whatever formatting]
A3 would result in "RSF18-May-2007"
|
109 | Trip Leader
ID: 13961611 Fri, May 18, 2007, 15:48
|
u da bomb, thanks
|
|
|
Post a reply to this message: (But first, how about checking out this sponsor?)
|