Forum: comp
Page 179
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.
 
1R9
      Leader
      ID: 02624472
      Mon, Mar 21, 2005, 04:56 		Question 1: Is there a way to copy a bunch of formulas from one page to another without having the range numbers and all that changed? Say I want to copy =SUM(A1:A3)-B50 without it changing the A1:A3 or -B50 part. What should I do? Having to redo all the numbers again when I have multiple formulas can be a real pain. Thanks!
 
2FRICK
      ID: 3410101718
      Mon, Mar 21, 2005, 06:37 		Yes, if you click on the cell and then go to the bar on top and highlight what you want and copy. That should allow to paste exactly what you have copied.

 
3R9
      Leader
      ID: 02624472
      Mon, Mar 21, 2005, 08:37 		Thanks for the response.

Thats what I've been doing for now, but sometimes I'll have twenty+ formulas in a row, and this definitely seems like the slow way of doing it.
 
4Mike D
      Sustainer
      ID: 41831612
      Mon, Mar 21, 2005, 08:43 		Can you copy the page itself before imputting page specific info? I do that every month at work. I just right click on the page tab at the bottom and choose "move or copy." Then check "create a copy" and where you want it to go in your workbook (or even into another workbook via the top drop down).

If you already know this, sorry-----figured I'd throw it out.
 
5J
      Leader
      ID: 049346417
      Mon, Mar 21, 2005, 08:43 		You can change it to:
=SUM($A$1:$A$3)-$B$50

the "$" locks in either the column or row, depending on what its next to.

If you copy that formula, nothing is going to change.
 
7JTSERB
      ID: 40135411
      Mon, Mar 21, 2005, 13:51 		Wish I would have knew about the $ tag before I did all the brackets in Excel. Would have save me hours! Thanks for the tip J
 
8C.SuperFreak
      ID: 261452514
      Mon, Mar 21, 2005, 18:12 		Shortcut alert for post 5.

highlight the cell, then position your cursor in the menu bar and press f4. Repeat to get the correct lock.

It will cycle through the locks depending on which part of the formula you want fixed.

 
9R9
      Leader
      ID: 02624472
      Mon, Mar 21, 2005, 19:54 		Perfect guys, exactly what I was hoping for. Thanks a bunch!
 
10smallwhirled
      ID: 111182021
      Thu, Mar 31, 2005, 14:07 		I never use excel and only know the basics....but say if I were doing a statistics problem, and I had a bunch of data and made a scatterplot graph. How would I use excel and the graph to find out different things such as Correlation coefficient, means, standard deviations, least squares regression lines, stuff like that.

Thanks in advance guys, I remember doing some stuff like this before but I just can't remember how to do it.
 
11biliruben
      Leader
      ID: 589301110
      Thu, Mar 31, 2005, 14:30 		Excel has an add-in that, if installed, you can find under TOOLS>Data Analysis>

Under which you can find a number of options, incuding
>Correlation, and
>Descriptive Statistics.
 
12Ref
      Donor
      ID: 539581218
      Mon, Apr 04, 2005, 15:54 		Can you do a mail sort for like label printing in excel?
 
13Cosmo's Cod Piece
      ID: 481152817
      Wed, Apr 06, 2005, 17:30 		Ref: I don't think you can actually print the labels, but you can use Excel as a database to store the mail label data.

Use the Mail Merge feature in Word and follow the steps from there once you have the Excel file created.

Make sure you give each part of the label its own column.

Ex. Column A - Name
Column B - Address
Column C - City, State Zip
 
14Ref
      Donor
      ID: 539581218
      Fri, Apr 08, 2005, 00:44 		Ah. Can I add other columns as well and then have that all ignored as I look into the mail merge? Thanks.
 
15Ref
      Donor
      ID: 539581218
      Fri, Apr 08, 2005, 00:46 		Also, since you can print lables and the like from Word, I can possible mail merge this info into Word you're saying?
 
16FRICK@Work
      ID: 220211
      Fri, Apr 08, 2005, 09:26 		That is correct, when you do the merge you select which columns of data you want to incorporate into the merge.

 
17Farn
      Sustainer
      ID: 451044109
      Wed, Apr 27, 2005, 14:44 		how do I get something like 10-4-2 to stop changing to 10/04/2002?

I've found the area to change the autochanging but I can't find a way to just turn it off.
 
18C.SuperFreak
      ID: 243849
      Wed, Apr 27, 2005, 15:16 		Format the column as text before you input the values.
 
19Farn
      Sustainer
      ID: 451044109
      Wed, Apr 27, 2005, 15:26 		Thanks. Much appreciated.
 
20 VP
      ID: 94362311
      Mon, May 23, 2005, 12:51 		Is there a way to set up an "automated formula"?

What I'm doing now is each week I'm adding a new column with how many runs, hr, etc each team has in one of my roto leagues and then taking the difference of the current week with the previous week to find the change so I can see how I did each week in each stat.

I'd like to be able to have a formula that automatically takes this difference as soon as I add the new data into the next week's column. Is this possible?
 
21FRICK@Work
      ID: 220211
      Wed, Jul 06, 2005, 15:07 		Is there a function or way to count the number of characters in a cell?

 
22Trip
      Sustainer
      ID: 13961611
      Wed, Jul 06, 2005, 15:24 		=LEN(CR)

Where C = column
and R = row
 
23FRICK@Work
      ID: 220211
      Thu, Jul 07, 2005, 13:58 		Thanks Trip.

I had done some searching in the Excel Help, but wasn't find what I needed.

Next question.

I'm trying to write a Macro that pulls data in from another sheet, here is what I have written so far.

For x = 1 To 25

ActiveCell.FormulaR1C1 = "7051"

ActiveCell.Offset(columnOffset:=1).Activate
Sheets("Corp Total").Select
Range("A3,B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("JE").Select
Range("C26").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

ActiveCell.Offset(rowOffset:=1, columnOffset:=-1).Activate

Next x

My question is how do I copy the data to the next blank line?

Is there a way to Activate the sheet I am copying to and using Offset to move the active cell, or do I need something that looks for the next blank cell.

Thank you,

Aaron
 
24ChicagoTRS
      ID: 21431823
      Thu, Jul 14, 2005, 23:36 		Excel Experts...looking for a little help.

I have a column in a spreadsheet that contains positive values, negative values, and zeros (they are dollar amounts but just displayed as numbers). I would like to be able to get a total count of all of the positives and a seperate count of all of the negatives and I want to ignore the zeroes. Basically I just want to know how many days I was positive and how many negative so I can figure my win %.
 
25Guru
      ID: 330592710
      Thu, Jul 14, 2005, 23:42 		=COUNTIF(A:A,">0")

This will count the number of entries that are greater than zero in column A.

 
26ChicagoTRS
      ID: 21431823
      Thu, Jul 14, 2005, 23:53 		Guru...sweet...that did it...figured it was pretty simple...thanks for the quick answer!
 
27ChicagoTRS
      ID: 464171213
      Fri, Jul 15, 2005, 15:42 		OK another question...same spreadsheet...kind of a simular question. If the number in row x column y is >0 SUM the number in row x column z. Basically trying to total my winnings on my winnings days and then I will do the same on the losing days <0.
 
28J
      Leader
      ID: 049346417
      Fri, Jul 15, 2005, 15:46 		You can make a column Z that says:

=IF(YX>0,YX,0)

then sum that column
 
29RecycledSpinalFluid
      Dude
      ID: 204401122
      Sat, Jul 16, 2005, 00:29 		Easier answer is along the same idea as Guru's:

For Positive Earnings in Column Z:
=SUMIF(Z:Z,">0")

For Negative Earnings in Column Z:
=SUMIF(Z:Z,"<0")

 
30ChicagoTRS
      ID: 21431823
      Sat, Jul 16, 2005, 14:58 		Thanks RSF...J...that works...

another one...this is kind of what I have so far but I know it is wrong but should give the idea of what I am trying to do...

=IF(E7:E371,">0", THEN SUM C7:C371)

Obviously I am kind of a novice at these things...I really appreciate the help.
 
31RecycledSpinalFluid
      Dude
      ID: 204401122
      Sat, Jul 16, 2005, 20:02 		Not in front of a machine with Excel, but I think this is what you want (or close):

=if(sum(e:e)>0, sum(c:c:),"")
 
32R9
      Leader
      ID: 02624472
      Mon, Oct 10, 2005, 21:14 		Here's my situation: I've got a list of people's names in column A, and then about 40 columns of info for each name. Needless to say, the last 30 or so columns are all off-screen, and when I scroll to see what they say I can no longer see the names in column A. I could just add their names half-way threw each, but that would mess with some of my formulas. So my question:

Is there a way to superimpose a column or row so that it is always on-screen? Like have the names in column A always showing, just like the numbers down the side?

Thanks for any help.
 
33R9
      Leader
      ID: 02624472
      Mon, Oct 10, 2005, 21:22 		Doom showed me the 'freeze pane' option so I'm all fixed up, thanks Doom. :)
 
34J
      Leader
      ID: 049346417
      Mon, Oct 10, 2005, 21:29 		now THAT was a simple excel question ;)
 
35Bincu
      ID: 57938132
      Thu, Oct 13, 2005, 04:42 		Hey, i have a small problem with validations.
I'm trying to list something on one sheet, but the actual data is on another file.
At the moment i'm messing with insert, name, define.
Then i enter something like... 'mylist' and give it as target a range on the other sheet, but it doesn't work...
 
36Perm Dude
      Dude
      ID: 030792616
      Tue, Oct 25, 2005, 02:57 		Any simple way to convert a Word document that is in table form into an Excel doc?
 
37RecycledSpinalFluid
      Dude
      ID: 204401122
      Tue, Oct 25, 2005, 12:22 		Could you email me an example of what you are talking about?
 
38 Perm Dude
      Dude
      ID: 030792616
      Sun, Oct 30, 2005, 21:22 		sure. I can't put my finger on your email address, though. Can you drop me a not when you get a moment?

pd
 
39 Libraryking
      ID: 18055813
      Sun, Jan 08, 2006, 14:55 		I have a question. I'm attempting to create an automated time sheet. It's complete spare one column and formula. The business does not give OT but Comp instead. In one column a person records both + and - Comp hours. If say a employee works 9 hrs one day they record +1 hrs Comp then say the next day they work 7 hrs they record -1 hrs Comp in the Comp time column. In the totals cell for comp it should reflect only the total of - Comp hours as this is the total they used during the pay period. Is there a way to write a formula to do this? Thanks for the help in advance.
 
40J
      Leader
      ID: 049346417
      Thu, Jan 12, 2006, 00:36 		i think you should be able to use the "sumif" formula.

=SUMIF(A:A,"<0",A:A)
 
41rockafellerskank
      ID: 180352016
      Mon, Jan 23, 2006, 14:18 		I need some help with a filter / advance filter question.

I have a 2000 lines of records and I want to identify each unique packet nummber once and only once. all 2000 accounts numbers are unique. Is there any way to eliminate every duplicate packet number and lerave one standing so what I am left with is something that looks like part II:

Original Data sampe:

PACKET NBR ACCOUNT# TOT BAL
99717*CHW005 1677423 98
92601*CHW005 1663986 30.86
86682*CHW005 1684458 71.4
85389*CHW005 1662085 138.5
85389*CHW005 1661841 35
84754*CHW005 1682425 82.3
84715*CHW005 1662203 116.8
84670*CHW005 1684236 140
84484*CHW005 1660486 156.75
82587*CHW005 1687581 27.2
79114*CHW005 1661106 222.75
76450*CHW005 1665006 350
76450*CHW005 1665461 175
76450*CHW005 1664403 43.75
44878*CHW005 1664739 342
42691*CHW005 1664129 220.5
42691*CHW005 1664283 195.75
388*CHW005 1682428 290.5
388*CHW005 1688570 130.9
388*CHW005 1684476 102.9
388*CHW005 1683453 102.9
388*CHW005 1687543 63
388*CHW005 1666025 63
388*CHW005 1667045 63
388*CHW005 1686521 63
388*CHW005 1685496 63
2649*CHW005 1665560 80.55
2209*CHW005 1664687 126
220597*CHW005 1666345 62.18


Final Data (how I want it too look):


PACKET NBR ACCOUNT# TOT BAL
99717*CHW005 1677423 98
92601*CHW005 1663986 30.86
86682*CHW005 1684458 71.4
85389*CHW005 1662085 138.5
85389*CHW005 1661841 35
84754*CHW005 1682425 82.3
84715*CHW005 1662203 116.8
84670*CHW005 1684236 140
84484*CHW005 1660486 156.75
82587*CHW005 1687581 27.2
79114*CHW005 1661106 222.75
76450*CHW005 1664403 43.75
44878*CHW005 1664739 342
42691*CHW005 1664129 220.5
388*CHW005 1685496 63
2649*CHW005 1665560 80.55
2209*CHW005 1664687 126
220597*CHW005 1666345 62.18

 
42biliruben
      Leader
      ID: 589301110
      Mon, Jan 23, 2006, 14:33 		I wouldn't do this in excel (and don't know how), but do you really want both records 4 and 5 in the final data?
 
43allhair allstars
      Sustainer
      ID: 50902421
      Mon, Jan 23, 2006, 14:36 		rfs,
Is your packet data in a separate column? If so, the advanced filter should solve your problem. After you open the advanced filter option, just select the column with the packet info, hit the Unique Records checkbox, and you should be good to go. Note that this really only works if all you're concerned about is unique packets. (Note that once you do this, your duplicate records are hidden, not removed, so you might want to copy paste the filtered cells (by click and dragging the actual cells (not row/column headers) to another worksheet)).

If your data is all in in a single string (as it appears in your example, you'll need to extract the packet data (a simple text to columns should serve you nicely...).
 
44allhair allstars
      Sustainer
      ID: 50902421
      Mon, Jan 23, 2006, 14:38 		Yeah, what bili said about that dup.

Also, if your data is in a single string (as I alluded to earlier), you'll need to do the text to columns, and then the advanced filter.
 
45RecycledSpinalFluid
      Dude
      ID: 204401122
      Mon, Jan 23, 2006, 16:44 		(Assuming different columns for field names)

Treat the XLS as a database (via ADOR) and request only DISTINCT records.

Don't have time right this second to write it up all here, but I can get to it later. Send me a sample and I'll work you up something nice. I use this concept quite a bit for something I do at work about twice a week (extracting a subset of data from an XLS).
 
46rockafellerskank
      ID: 180352016
      Mon, Jan 23, 2006, 18:13 		RE: #43 AA- (yes they are in seperate columns (packet #, account #, balance).

Ooops - error in my exampe, on records 4&5, should have only appeared once to be valid. I was in ahurry to post.

I tried the advance filter and it says I need to select 2 columns-- well, when I do that they are all unique records cause all the balances and account #s are different.

RSF-- I could shoot you the speadsheet if you could help? There's nothing confidentail that couldbe decoded.
 
47 Trip
      Leader
      ID: 13961611
      Mon, Jan 23, 2006, 18:29 		RSF

Can you shoot me an e-mail? I've got a not so simple excel question regarding parsing of data from an excel spreadsheet.
 
48allhair allstars
      Sustainer
      ID: 50902421
      Mon, Jan 23, 2006, 18:58 		rfs,
Let's run through it again to make sure I gave it to you correctly...
Data, Filter, Advanced Filter
Your List Range field should read "Sheet1!$A:$A" - assuming your worksheet is called "Sheet1" and your data is in Column A. The $A:$A just means that you're selecting everything in that column. Adjust accordingly.

Your Criteria Range field should be blank.

Select the Unique Records Only check box.

The only reason I can see that you're getting asked for more than one cell is that for some reason... well, when I run the advanced filter, when I first get the dialogue, everything in the spreadsheet is selected. Then, when I select the column I want, everything stays highlighted, but the column I selected now has a blinking border around it. I can say that I've never seen your error message... Feel free to send it my way if you want.
 
49RecycledSpinalFluid
      Dude
      ID: 204401122
      Mon, Jan 23, 2006, 21:45 		That'd work rockafellerskank.

Trip, email sent from a GMail account. If it get marked as spam, let me know and I'll send from another.
 
50KoGs
      ID: 52715301
      Tue, Jan 24, 2006, 02:50 		Question sorting numbers:


ok lets say I have this scenario:

A B C D E
5 3 2 9 4
now for the 3rd row I want to rank them, so it would look like:


A B C D E
5 3 2 9 4
rank:
2 4 5 1 3

Now I don't want to sort it from greatest to smallest because I want to keep the alphabetic order. How can I do this? Only way I can think of is have like 6 if statements for each column. If 5 > 3 and >2 and >9 and >4 then = 1. If 5 < 3 and >2 and >9 and >4 then = 2, etc. Needless to say this is very ugly especially when I will have more than 5 letters.
 
51Richard
      Dude
      ID: 204252420
      Tue, Jan 24, 2006, 10:07 		KoGs - use the Rank function. It will look something like this =Rank(A2,$A2:$E2).

Richard
 
52rockafellerskank
      ID: 180352016
      Tue, Jan 24, 2006, 10:17 		AA- I found the 'error' of my ways. Thanks for the tip.
 
53KoGs
      ID: 52715301
      Tue, Jan 24, 2006, 10:35 		Thanks Richard.

How do I post an Excel worksheet on a website? Something like how RSF posts the season schedules.
 
54R9
      Leader
      ID: 02624472
      Tue, Jan 24, 2006, 11:01 		First, use Save As to save your excel sheet as a webpage. Then open that page in Internet Explorer, and go View -> Source. Then copy/paste the entire source to the type box, and make sure to check "yes (for HTML table input)" at the bottom of the type box here at rotoguru.
 
55Guru
      ID: 330592710
      Tue, Jan 24, 2006, 12:24 		...and please try it first at the test forum. Soetimes tables look OK in the preview, but get screwed up in the final translation.
 
56RecycledSpinalFluid
      Dude
      ID: 204401122
      Tue, Jan 24, 2006, 12:27 		Unfortunately for me, I manually code all the HTML source. I know...I'm a masocist.
 
57deraynman
      ID: 511124121
      Tue, Feb 21, 2006, 23:28 		Let's say I have the following in a square of 4 cells:

=A1 =A2
=B1 =B2

Is there a way to select them all and change them to?:

=$A$1 =$A$2
=$B$1 =$B$2

The shortcut given in post 8 is helpful, but I can't do that for thousands of cells.
 
58The 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?
 
59J
      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$"
 
60deraynman
      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?
 
63TB
      Sherpa
      ID: 031811922
      Tue, Jun 27, 2006, 00:47 		Hey Rachael-Kimberly, maybe this link will help you out: Study abroad in Australia
 
64R9
      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
 
66R9
      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.
 
67RecycledSpinalFluid
      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.
 
68RecycledSpinalFluid
      ID: 39802115
      Sat, Nov 11, 2006, 14:38 		Well, duh...you did.
 
69RecycledSpinalFluid
      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 ,"<>—")
 
70weykool
      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?
 
71Kal 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.)
 
74KoGs
      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.
 
75Guru
      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)
 
76C.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.
 
77KoGs
      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.
 
78The 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.
 
79C.SuperFreak
      ID: 3010403013
      Fri, Dec 15, 2006, 13:28 		very tricky. I'll see what I dig up around the office.
 
80boikin
      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.
 
81KoGs
      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.
 
82KoGs
      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.
 
83boikin
      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
 
84boikin
      ID: 59831214
      Fri, Dec 15, 2006, 15:17 		solver will solve it i got it to give the correct solution.
 
85boikin
      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.
 
86KoGs
      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.
 
87Rendle
      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.
 
88Frick
      Donor
      ID: 3410101718
      Wed, Jan 17, 2007, 16:24 		Yes there is.

Tools > Options > General tab

Turning off the R1C1 formatting should do it.
 
89Rendle
      Donor
      ID: 014815714
      Wed, Jan 17, 2007, 16:28 		Wow, thank you. What an idiot I am.
 
90bibA
      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
 
91J
      Leader
      ID: 049346417
      Sun, Feb 11, 2007, 12:45 		< I >italics < / I >
< B >bold < / B >

just take out the spaces
 
92bibA
      Leader
      ID: 261028117
      Sun, Feb 11, 2007, 15:22 		Thanks much J
 
93ChicagoTRS
      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....
 
94Trip
      Leader
      ID: 13961611
      Wed, Mar 14, 2007, 11:49 		sort...then delete
 
95ChicagoTRS
      ID: 4110481415
      Wed, Mar 14, 2007, 12:01 		simple enough...thanks...
 
96RecycledSpinalFluid
      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.
 
97R9
      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?
 
98rockafellerskank
      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.
 
99RecycledSpinalFluid
      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?
 
100J
      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.
 
101Frick
      Donor
      ID: 3410101718
      Tue, May 08, 2007, 14:41 		You could also apply the AutoFilter.
 
102RecycledSpinalFluid
      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
 
103J
      Leader
      ID: 049346417
      Tue, May 08, 2007, 14:48 		holy crap, I just learned something new, nice job RSF :)
 
104rockafellerskank
      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.
 
105weykool
      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.
 
106Frick
      Donor
      ID: 3410101718
      Wed, May 09, 2007, 16:22 		RFS, what version of Excel are you using?
 
107Trip
      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?
 
108RecycledSpinalFluid
      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"
 
109Trip
      Leader
      ID: 13961611
      Fri, May 18, 2007, 15:48 		u da bomb, thanks