RMF: Simple Excel Questions [RotoGuru Computer Forum]

RotoGuru Computer Forum View the Forum Registry XML Get RSS Feed for this thread	Self-edit this thread

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.

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?

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$"

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.

Rachael
ID: 185372622
Mon, Jun 26, 2006, 23:37

Hello
can you please make me a simple test on excel??
Thanks!

Kimberly A
ID: 185372622
Mon, Jun 26, 2006, 23:38

hello
how do you use excel?
what is it?

TB
Sherpa
ID: 031811922
Tue, Jun 27, 2006, 00:47

Hey Rachael-Kimberly, maybe this link will help you out: Study abroad in Australia

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

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.

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.

RecycledSpinalFluid
ID: 39802115
Sat, Nov 11, 2006, 14:38

Well, duh...you did.

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 ,"<>—")

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?

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.

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.)

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.

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)

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.

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.

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.

C.SuperFreak
ID: 3010403013
Fri, Dec 15, 2006, 13:28

very tricky. I'll see what I dig up around the office.

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.

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.

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.

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

boikin
ID: 59831214
Fri, Dec 15, 2006, 15:17

solver will solve it i got it to give the correct solution.

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.

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.

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.

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.

Rendle
Donor
ID: 014815714
Wed, Jan 17, 2007, 16:28

Wow, thank you. What an idiot I am.

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

J
Leader
ID: 049346417
Sun, Feb 11, 2007, 12:45

< I >italics < / I >
< B >bold < / B >

just take out the spaces

bibA
Leader
ID: 261028117
Sun, Feb 11, 2007, 15:22

Thanks much J

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....

Trip
Leader
ID: 13961611
Wed, Mar 14, 2007, 11:49

sort...then delete

ChicagoTRS
ID: 4110481415
Wed, Mar 14, 2007, 12:01

simple enough...thanks...

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.

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?

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.

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

RotoGuru Computer Forum View the Forum Registry XML Get RSS Feed for this thread	Self-edit this thread

Post a reply to this message: (But first, how about checking out this sponsor?)

Name:
Email:
Message:	Click here to create and insert a link Ignore line feeds? no (typical) yes (for HTML table input)

Viewing statistics for this thread
Period	# Views	# Users
Last hour	1	1
Last 24 hours	2	2
Last 7 days	3	3
Last 30 days	9	9
Since Mar 1, 2007	2450	1060