Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »

esquilax: Jan 3, 2003

Heavy_D posted:

I've seen esquilax's post but isn't it just easier to do

A1: 5
B1: A1 * 0.8 + F1 * 0.2
C1: A1 * 0.6 + F1 * 0.4
D1: A1 * 0.4 + F1 * 0.6
E1: A1 * 0.2 + F1 * 0.8
F1: 10

Then just autofill the formula down the columns B to E.

I read his post as him having his values all in one row instead of two columns.

# ? Nov 4, 2011 23:38

Adbot: ADBOT LOVES YOU

# ? Apr 28, 2024 19:56

Elston Gunn: Apr 15, 2005

Esquilax, that worked great, thank you very much. And, yes, all values were in one row.

# ? Nov 5, 2011 00:55

WinnebagoWarrior: Apr 8, 2009; I eat Rotheseburgehergh's like you for breakfast

I have an issue that I can resolve in either Excel or SPSS is anyone is knowledgeable about that here. I need to standardize a shitload of data in either of those programs. I tried googling it and I am either using the wrong term to google or I am too stupid to get what its telling me.

Basically want I want to do is for all of the values to be based off of 100. For example, if a score is 150/200 I want it to be converted to 75. If its 4/5 I want it to be converted to 80. Is there a way to do this for the entire sheet at once?

# ? Nov 5, 2011 03:43

ZerodotJander: Dec 29, 2004; Chinaman, explain!

All you need to do is divide it out and multiply by 100.

# ? Nov 5, 2011 05:52

coyo7e: Aug 23, 2007; by zen death robot

Probably doesn't even need to be divided out, tbh.

WinnebagoWarrior posted:

I have an issue that I can resolve in either Excel or SPSS is anyone is knowledgeable about that here. I need to standardize a shitload of data in either of those programs. I tried googling it and I am either using the wrong term to google or I am too stupid to get what its telling me.

Basically want I want to do is for all of the values to be based off of 100. For example, if a score is 150/200 I want it to be converted to 75. If its 4/5 I want it to be converted to 80. Is there a way to do this for the entire sheet at once?

So these are actual fractions being stuck into a "text" column in a "x/y" format?

You can also easily just format the origin column to fraction (Hundredths is you wanna be fancy, this formatting may not be necessary, I think, in retrospect) and then set the cells in that column to say "=A1 * 100" or whatever the source is.

coyo7e fucked around with this message at 22:09 on Nov 5, 2011

# ? Nov 5, 2011 22:02

DEUCE SLUICE: Feb 6, 2004; I dreamt I was an old dog, stuck in a honeypot. It was horrifying.

My wife has hundreds of workbooks with 50k+ lines that she wants to dump into one consolidated database for easier reporting, but she's having trouble figuring out how to run reports from Excel once the stuff is in Access or whatever. Is there a good resource, either a book or online, that discusses how she can plug the two things together?

I saw an O'Reilly's book, Integrating Excel and Access, but it's from 2005.

# ? Nov 9, 2011 01:42

rap music: Mar 11, 2006

OK, Gentlemen, let's see if somebody can help me tackle this.

Our business basically lives off of a logistics spreadsheet and parts of it are distributed to appropriate carriers, vendors, and reps. This spread sheet is constantly in motion with things being added and removed on a daily basis. Here's what I would like to do:

In addition to this workbook's master sheet, I would like to create additional sheets that use the master as a reference to display data. For example: I want "Sheet2" to reference a specific column in "Sheet1" and display all of the data in that row and update automatically as "Sheet1" changes.

Let me know if you need any additional info, and in the meantime I'm going to continue to bang my head against a wall trying to figure this out.

Thanks in advance, sirs.

# ? Nov 9, 2011 03:30

Scientastic: Mar 1, 2010; TRULY scientastic.
🔬🍒

Carbon Deity posted:

OK, Gentlemen, let's see if somebody can help me tackle this.

Our business basically lives off of a logistics spreadsheet and parts of it are distributed to appropriate carriers, vendors, and reps. This spread sheet is constantly in motion with things being added and removed on a daily basis. Here's what I would like to do:

In addition to this workbook's master sheet, I would like to create additional sheets that use the master as a reference to display data. For example: I want "Sheet2" to reference a specific column in "Sheet1" and display all of the data in that row and update automatically as "Sheet1" changes.

Let me know if you need any additional info, and in the meantime I'm going to continue to bang my head against a wall trying to figure this out.

Thanks in advance, sirs.

That doesn't sound too difficult. Unless I'm missing something, you can just use an IF function.

If you enter the following into your sheet two cell, assuming that your data is in column A of sheet 1, it should work:
=IF(Sheet1!A1>0,Sheet1!A1," ")

So, if sheet 1, column A has an entry, it will be displayed in sheet 2. If not, it will just be blank.

# ? Nov 9, 2011 18:03

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

DEUCE SLUICE posted:

My wife has hundreds of workbooks with 50k+ lines that she wants to dump into one consolidated database for easier reporting, but she's having trouble figuring out how to run reports from Excel once the stuff is in Access or whatever. Is there a good resource, either a book or online, that discusses how she can plug the two things together?

I saw an O'Reilly's book, Integrating Excel and Access, but it's from 2005.

It sounds like she wants to use functions on the entire set of data at once. If so packing it into an Access database won't do her much good as she will have to bring it back the data into Excel before she can run her functions on it. If she will only need subsets of the data at a time she can set up an ODBC connection to any Database tool to query data and have that subset automatically dumped into Excel, then she can use her functions on that subset.

# ? Nov 10, 2011 05:09

Cast_No_Shadow: Jun 8, 2010; The Republic of Luna Equestria is a huge, socially progressive nation, notable for its punitive income tax rates. Its compassionate, cynical population of 714m are ruled with an iron fist by the dictatorship government, which ensures that no-one outside the party gets too rich.

DEUCE SLUICE posted:

My wife has hundreds of workbooks with 50k+ lines that she wants to dump into one consolidated database for easier reporting, but she's having trouble figuring out how to run reports from Excel once the stuff is in Access or whatever. Is there a good resource, either a book or online, that discusses how she can plug the two things together?

I saw an O'Reilly's book, Integrating Excel and Access, but it's from 2005.

Hundreds of workbooks, each with 50k lines is hundreds of thousands/millions of lines. You want that poo poo in a database for sure. Then you'd basically set up a database query to pull whatever data you wanted to report on from access to excel and run whatever functions you wanted from there.

Hit up google/youtube for how to set up a query. Also get look at handle on dynamic named ranges cause otherwise you'll go mental.

# ? Nov 10, 2011 20:01

HoldYourFire: Oct 16, 2006; What's the time? It's DEFCON 1!

Thank God for this thread. Is there an easy hack for doing horizontal addition (eg. aggregation of demand curves) in Excel? This type:

code:

(y = 20 - x) "plus" (y = 20 - x)
(x = 20 - y) "plus" (x = 20 - y)
x = 20 + 20 - y - y
2y = 40 - x
y = 20 - (x/2)

I put the "plus" in scare quotes because otherwise it would look like simple addition, which would make it (2x = 40 - 2y) which is wrong. I hope this makes some kind of sense.

Edit: Figured out a good enough solution for myself.

HoldYourFire fucked around with this message at 21:51 on Nov 15, 2011

# ? Nov 15, 2011 13:35

qntm: Jun 17, 2009

HoldYourFire posted:

Thank God for this thread. Is there an easy hack for doing horizontal addition (eg. aggregation of demand curves) in Excel? This type:
code:
(y = 20 - x) "plus" (y = 20 - x)
(x = 20 - y) "plus" (x = 20 - y)
x = 20 + 20 - y - y
2y = 40 - x
y = 20 - (x/2)
I put the "plus" in scare quotes because otherwise it would look like simple addition, which would make it (2x = 40 - 2y) which is wrong. I hope this makes some kind of sense.

Actually, this makes no mathematical sense to me at all. If x = 20 - y then 2x = 40 - 2y is completely correct, surely?

# ? Nov 15, 2011 17:42

HoldYourFire: Oct 16, 2006; What's the time? It's DEFCON 1!

qntm posted:

Actually, this makes no mathematical sense to me at all. If x = 20 - y then 2x = 40 - 2y is completely correct, surely?

I don't really know how to explain it, maybe it's just an economics thing. From here:

wikipedia posted:

Horizontal versus vertical summation
When adding individual demand curves it is critical that the summation be horizontal rather than vertical. The derivation of the market demand function involves adding quantities.[26] The conventional graphical representation is of the inverse demand function. Adding inverse demand equations involves adding prices. In order to add the demand functions algebraically one must first convert the inverse equation to the standard demand function where quantity demanded is a function of price.[27] For example, assume that there are two consumers in a given market and their respective demand functions are P = 30 - 2Q and P = 30 - 6Q. To sum these functions to obtain the market demand curve we must first convert to standard form, that is Q = 15 - (P/2) and Q = 5 - (P/6). Then, adding Q1 and Q2 yields 15 - (P/2) + 5 - (P/6) = 20 - (4P/6)[28]= 20 - 2P/3.

Edit: As an aside I only just learned the "$A$1" notation for absolute cell references, holy poo poo that's been bugging me for years :negative:

Edit2: OK I've figured out a good enough solution for myself involving specifying the equation for the line in multiple cells. Thanks to anyone who attempted to figure out what I was going on about, or in advance for anyone who comes up with a better solution.

HoldYourFire fucked around with this message at 21:51 on Nov 15, 2011

# ? Nov 15, 2011 19:19

Sonic H: Dec 8, 2004; Me love you long time

Not sure if this is the right place to ask this, but here goes:

I currently have a number of Excel tools that also happen to use VBA. The VBA is mostly used to power the UI however - the heavy lifting is done in the spreadsheets. One thing I'm looking to do is make the sheets more "network friendly" and power them using some kind of Excel-lite that runs over a network.

I've heard of a couple of solutions, one involving SharePoint (Excel Services) and another running on an Apache server (ApachePOI). Has anyone used either of these (or any other) solutions and if so, are they any good?

Sonic H fucked around with this message at 13:34 on Nov 18, 2011

# ? Nov 18, 2011 13:30

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

Weird bug with Excel:

As far as I can tell, this only happens:

In one particular file
In page layout view
At 100% zoom

Where if I click a cell, it will select a few cells to the right of the one I click. I can "deselect" the additional cells by dragging to the left of the original cell. If I click quickly, the additional cells are not selected. Also, if I try to move a cell, it think I'm clicking several cells to the right. That is, it'll immediately move the "destination cell border" a few cells to the right of where my mouse actually is.

It only selects extra cells to the right. It only happens in this file.

F8 is not involved.

I can get around this by changing any of the above-mentioned things but it's driving me nuts why this is happening.

TIA

totalnewbie fucked around with this message at 15:44 on Nov 29, 2011

# ? Nov 29, 2011 15:28

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Do you maybe have a merged cell and some hidden columns?

# ? Nov 29, 2011 16:42

totalnewbie: Nov 13, 2005; I was born and raised in China, lived in Japan, and now hold a US passport.

I am wrong in every way, all the damn time.

Ask me about my tattoos.

There are merged cells but no hidden columns.

I am not clicking on merged cells and when I do click on a merged cell, it'll still select more cells to the right.

I swear, it feels more and more like this particular Excel file thinks my mouse jumps to the right if I click and hold down the mouse button.

# ? Nov 29, 2011 17:49

sirbeefalot: Aug 24, 2004; Fast Learner.; Fun Shoe

I have what is (I hope) a simple task I need to do with Excel (2004 for Mac, latest updates) that I don't know where to start with. I'm helping someone re-print their physical address book that they printed for the last time in like 1997 with software on Windows 95 that no longer exists. They want it to look the same way, and are now using the Address Book app on their iMac which has a total of 2 options for printing, both of which are useless and also don't look anything like the formatting of the original print out.

I have all of the contact info exported to an Excel workbook, separated into columns labeled with the appropriate headers (first name, last name, work phone, etc.) in the 1st cell of each column. Aside from formatting errors from some entries in Address Book.app being in the wrong fields, everything is very organized (I think) for what I want to do. An entry's info is all in the same row across the workbook, and when that entry doesn't have data in a field it is blank.

I want to set up some kind of template and have Excel fill in the info for each person, making a list that resembles the old address book's layout. The end goal is something like this:

with just a line between each entry. Bold Name(s) with address(es) underneath, then phone numbers and emails on the right. Ideally it can be set up to be flexible, as many entries have one phone or email, others have 5-6 phone numbers, etc. Some have notes and some do not, so some way of having it utilize those extra fields below the standard stuff in that example, or just end the entry if those fields don't have anything in them for that person would be best.

I need to format this to print in portrait on half letter paper (5.5" x 8.5")

Hjalp!

sirbeefalot fucked around with this message at 21:51 on Dec 4, 2011

# ? Dec 4, 2011 21:33

gwar3k1: Jan 10, 2005; Someday soon

Do you know visual basic at all? You could experiment with the following code (making it more flexible for number of lines per group, reading column names and using the column number as a variable rather than fixed numbers in code...

Assuming your list of addresses is on a sheet called data and there is a sheet called formatted; and that on data, the column order is firstname, lastname, add 1, city, state, zip, tel 1, tel 2, tel 3, email

code:

  Set wdata = Sheets("data")
  Set wnice = Sheets("formatted")

  iGroup = 1
  iRows = 4
  iEnd = wdata.Range("A65536").End(xlUp).Row ' This finds the last row with data on the data sheet
  For iRow = 1 to iEnd ' Loop all rows from 1 to the last row with data
    If wdata.Cells(iRow, 1) <> "" Then ' Ignore blank rows if you have any
      ' First row of formatted output
      wnice.Cells(((iGroup-1) * iRows), 1) = wdata.Cells(iRow, 1) & ", " & wdata.Cells(iRow, 2) ' First name, Last name
      wnice.Cells(((iGroup-1) * iRows), 2) = wdata.Cells(iRow, 7) ' Tel 1
      ' Second row
      wnice.Cells(((iGroup-1) * iRows)+1, 1) = wdata.Cells(iRow, 3) ' Add 1
      wnice.Cells(((iGroup-1) * iRows)+1, 2) = wdata.Cells(iRow, 8) ' Tel 2
      ' third row
      wnice.Cells(((iGroup-1) * iRows)+2, 1) = wdata.Cells(iRow, 4 & ", " & wdata.Cells(iRow, 5) & " " & wdata.Cells(iRow, 6)) ' City, State Zip
      wnice.Cells(((iGroup-1) * iRows)+2, 2) = wdata.Cells(iRow, 8) ' Tel 3
      ' etc.

      iGroup = iGroup + 1 ' Increases the group, wouldn't work for a flexible number of rows
    End if
  Next

# ? Dec 5, 2011 00:07

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

EDIT: ^^^^ I believe macros don't work on the Mac version of Excel.

sirbeefalot posted:

It was quicker for me to mock up a file for you than abstractly explain the formulas. Hopefully you can look at the formulas and pick up what I am trying to do. Just keep copying the block for each address down till you run out of space. You could modify the formulas to use them in a second and third column to fill out your paper when you print it out.

http://www.megaupload.com/?d=ZU9UO8AJ

Old James fucked around with this message at 00:10 on Dec 5, 2011

# ? Dec 5, 2011 00:08

sirbeefalot: Aug 24, 2004; Fast Learner.; Fun Shoe

Old James posted:

EDIT: ^^^^ I believe macros don't work on the Mac version of Excel.

It was quicker for me to mock up a file for you than abstractly explain the formulas. Hopefully you can look at the formulas and pick up what I am trying to do. Just keep copying the block for each address down till you run out of space. You could modify the formulas to use them in a second and third column to fill out your paper when you print it out.

http://www.megaupload.com/?d=ZU9UO8AJ

This is super helpful, thanks! I'm not 100% sure what's going on in the formulas, though I have a vague idea. I think I can play around with it and get these printed out.

# ? Dec 6, 2011 15:53

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Can someone help with an Excel 2010 question?

I upgraded to 2010 at work today and found they've enabled wildcards in countifs and sumifs. The problem is I have reports that use formulas like "=countif($A:$A,$D1)" and some of the fields in column D have wildcard characters in their string. So when the formula is looking at a field that has "S*" instead of giving a count of exactly that string it will count all strings that start with "S". I have searched online and found that I could do "=countif($A:$A,"S~*")" to get an exact match, but since my criteria is a range and not a string that does not fix it for me. Are there any 2010 users who could help me out?

I wish they had added an optional field in the formula that would let me turn wildcards off and made that the default as I have a number of reports which will break when other users upgrade to 2010 as well.

# ? Dec 9, 2011 21:38

khazar sansculotte: May 14, 2004

I'm soon going to input grades for almost 2000 students, and I have two separate spreadsheets (from different online grade sources) with different data that I want to combine. One sheet has all the students who were registered at the beginning; the other has deleted students who dropped the course during the semester. Basically, I want to paste this:

code:

Name 1   Score 1
Name 2   Score 2
Name 3   Score 3
Name 4   Score 4
Name 5   Score 5

onto a sheet that presently looks like this:

code:

Name 1
Name 2
Name 4

so that I end up with:

code:

Name 1   Score 1
Name 2   Score 2
Name 4   Score 4

without having to manually delete the ~150-200 students who dropped the class. Please tell me there's a quick way to do this?

edit - if it helps, I am part of the way to a kinda messy solution. Is there a nice way to do something like

code:

=IF(OR(A1=A2,A1=A3,...,A1=A2000),Bk)

where k is such that A1=Ak ? If so, then my problem will be solved! I'd still be interested in a straightforward way to do it though, so that I can explain it to my peers who know even less about Excel than I do.

khazar sansculotte fucked around with this message at 19:30 on Dec 12, 2011

# ? Dec 12, 2011 19:06

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Ronald McReagan posted:

stuff...

You are looking for the vlookup formula. If the beginning of the semester names as grades are in columns A and B and the end of semester names are in column D, your formula in E1 would be

code:

=vlookup($D1,$A:$B,2,false)

The formula looks through the first column of range $A:$B looking for an exact match to the value in $D1 and if it find the exact match it spits the results in the second column of that range. If it can't find a match it returns and error.

# ? Dec 12, 2011 20:05

khazar sansculotte: May 14, 2004

Fantastic, thanks so much!

# ? Dec 12, 2011 20:43

Heavy_D: Feb 16, 2002; "rararararara" contains the meaning of everything, kept in simple rectangular structures

This article from microsoft offers an innovate alternate method of joining the two sets of data:

http://support.microsoft.com/kb/211802

I don't know if microsoft query is still around in newer versions, it was in 2003 looking like it hadn't been updated for 10 years. You'd have to adapt the method a bit, rather than using word to create the query you'd want to use excel to import data and store it in a new spreadsheet, but it should be basically the same.

# ? Dec 13, 2011 13:10

melon cat: Jan 21, 2010; Nap Ghost

Can anyone recommend some online good resources for picking up on more advanced Excel trickery? I'm trying to get more familiar with Excel as a program, but I'm just not sure where to start.

# ? Dec 15, 2011 21:41

19 o'clock: Sep 9, 2004; Excelsior!!!

melon cat posted:

Can anyone recommend some online good resources for picking up on more advanced Excel trickery? I'm trying to get more familiar with Excel as a program, but I'm just not sure where to start.

http://www.cpearson.com/Excel/MainPage.aspx

Chip Pearson's website is pure gold. I went from being a good coder and good Excel user to being the master of all Excel via his VBA tutorials. My motto around the office? "If you're using a mouse, you're doing it wrong." Arrogant? Yes, but I've automated so many processes at my work it's getting tough filling out the day... :ssh:

# ? Dec 22, 2011 18:12

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Ron DeBruin http://www.rondebruin.nl/tips.htm Also has some great tips.

# ? Dec 23, 2011 17:01

BonHair: Apr 28, 2007

Crosspost from the stupid/small questions thread in A/T: I have a bunch of numbers that I want to turn into lines in a diagram. I want essentially a hundred or so lines, each consisting of two (x,y) points. That's the easy yet tedious part. Now, I also want to make a lot of them share the same colour and, if possible legend, preferably without having to change every god drat line individually. I would also like if it could tell me which point in the line is the first and which is the second.

Can that be done in an easy way? My data are essentially in the form of one column of X's, on column of Y's and on column of text telling me what the numbers are. The text is something like "3 [word]" where "3" is what identifies one end of a line with the other end of that line. The word will be one of 21 words (in a pretty random order) that I ideally would like to group together. Does that make sense?

# ? Dec 26, 2011 16:40

Fall Sick and Die: Nov 22, 2003

I'm trying to make something in excel where I can enter a number in one place, and then that number will be used in a formula, but it's proving difficult, let me explain.

I have 55 columns, each one with different values input for about 20 rows. What I want to be able to do is to input a number in a cell like AA1, and another number in a cell like AC1, then in cell AB1 I have a formula like this which works, but I have to manually input all of the values of 2 or 3, when ideally I'd like to put the 2 or the 3 in the AA1 and AC1 cell...

Working yet tedious formula...
=SUM((C2-C3),(O2-E3),(R2-H3),(N2-N3),(AL2-AL3),RANDBETWEEN(-5,5))

Ideal formula that I can't figure out how to get it to work...
=SUM((C(AA1)-C(AC1)),(O(AA1)-E(AC1)),(R(AA1)-H(AC1)),(N(AA1)-N(AC1)),(AL(AA1)-AL(AC1)),RANDBETWEEN(-5,5))

Any help would be greatly appreciated.

# ? Dec 31, 2011 05:30

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

You could do what you want with INDIRECT() but a better way is to use OFFSET().

What offset does is takes a starting range and then moves it x columns and y rows. You can even make it change width and height so you start out defining 1 cell and with offset the formula is now looking at a block of 6 cells, but it doesn't look like you need that aspect for your formula here.

=offset($A$1,1,2) will return the value in cell $C$2 because that is 1 row down and 2 columns to the right from $A$1. =offset($A$1,0,0) returns $A$1.

So if you want to values in $AA$1 and $AC$1 to be 2 and 3 respectively and you want the offsets to show the results from those two rows then your formula would be...

=SUM(offset($C$1,$AA$1-1,0)-offset($C$1,$AC$1-1,0),offset($O$1,$AA$1-1,0)-offset($E$1,$AC$1-1,0),offset($R$1,$AA$1-1,0)-offset($H$1,$AC$1-1,0),offset($N$1,$AA$1-1,0)-offset($N$1,$AC$1-1,0),offset($AL$1,$AA$1-1,0)-offset($AL$1,$AC$1-1,0),randbetween(-5,5))

# ? Dec 31, 2011 18:45

Fall Sick and Die: Nov 22, 2003

Wow, that worked exactly as I wanted, thanks so much Old James.

# ? Jan 1, 2012 14:40

CanuckBassist: Mar 20, 2007

I'm wondering if there's a way to randomize some data. Essentially, I need to allocate some resources over a number of days. The resources should have all be used about the same number of times by the end, and each day only requires a specific number of resources.

Example:
Resource1, resource2, ..., resource6
Day1, day2, day3

Only 4 resources are required each day, and we want all the resources to been used about the same number of times (twice in this example). Of course, my data is on a much larger scale.

I tried using rand() to determine whether each resource is used on one particular day, but then it involved too many if() and countif() to arrive at exactly 4 resources per day. I also couldn't find an easy way to get each resource used the same number of times by the end without a lot of formulas.

Ideas? I ended up using rand() to randomize the list of resources, and allocate them over the days by assigning manually to days using the randomized list repeatedly. I'm sure there's a better way, and I'm curious.

Thanks

# ? Jan 5, 2012 05:40

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Try something like this in 4 columns for each day (since you are choosing 4 resources)

=CHOOSE(RANDBETWEEN(1,6),"A","B","C","D","E","F")

Over a very large dataset you should end up at roughly even distribution. But since it is random they will almost never be exact, if you need that then you aren't looking for a random allotment.

Old James fucked around with this message at 07:02 on Jan 5, 2012

# ? Jan 5, 2012 06:59

Aredna: Mar 17, 2007; Nap Ghost

This should do what you want assuming that you don't want a resource to be used more than once on a single day. I don't have a lot of time to explain how it works right now, but will try to remember to edit this it in later.

To use it, set up the formulas as shown and assuming that "Resource" is in Cell A1. Note that the Day formula is an array formula and to enter it rather than pressing ENTER you need to press CTRL-SHIFT-ENTER. Then you need to drag that formula down rather than copy/paste or you'll get an error.

After the formulas are in, then sort column B ascending and Day will be allocated to the resources randomly.

Also of note: If you have any 0s near the bottom for the day then the greedy allocation method this uses failed due to two of the same resource being left over for the last day. A simple workaround would be just resort column B until you have no Day 0s.

To adjust the number of days, change all 3 of the ROW($1:$3) to go to how many every days you need instead of 3 days. To change the number of resources per day, change the <4 to be < (desired resources + 1).

Formula so you don't have to type it for C2:

code:

=MAX((COUNTIF($C$1:C1,ROW($1:$3))<4)*(COUNTIFS($A$1:A1,A2,$C$1:C1,ROW($1:$3))=0)*ROW($1:$3))

# ? Jan 5, 2012 16:58

CanuckBassist: Mar 20, 2007

Thanks, Old James, but unfortunately that can allocate the same resource to be used more than once per day.

Aredna, that looks promising. I'll take a closer look at the formula myself, I love crazy formulas. Thanks!

# ? Jan 6, 2012 02:00

Aredna: Mar 17, 2007; Nap Ghost

To explain the logic behind this formula, it helps to first understand how an array formula works. What is happening the way I am using it below is that it is evaluated for each piece of the array. In this case, that means once each for ROW($1), ROW($2), and ROW($3).

We are using ROW($x) to represent each possible day.

So the formula we have here

code:

=MAX((COUNTIF($C$1:C1,ROW($1:$3))<4)*(COUNTIFS($A$1:A1,A2,$C$1:C1,ROW($1:$3))=0)*ROW($1:$3))

is essentially ran as

code:

=(COUNTIF($C$1:C1,1)<4)*(COUNTIFS($A$1:A1,A2,$C$1:C1,1)=0)*1)
=(COUNTIF($C$1:C1,2)<4)*(COUNTIFS($A$1:A1,A2,$C$1:C1,2)=0)*2)
=(COUNTIF($C$1:C1,3)<4)*(COUNTIFS($A$1:A1,A2,$C$1:C1,3)=0)*3)

and then the max value of those is returned.

This piece of the formula breaks down into 3 pieces:
1) (COUNTIF($C$1:C1,1)<4)
2) (COUNTIFS($A$1:A1,A2,$C$1:C1,1)=0)
3) 1

These logic for these pieces is:
1) Count how many times the day we are checking has already been allocated a resource and verify it is less than 4. If it is more than 4 then we return false (think 0).
2) Count how many times the resource on this row has been allocated to the day we are checking, and validate that the count =0, therefore unused. If it has been used we return false.
3) the day we are checking

We then multiply these all together - if either piece 1 or piece 2 evaluate to false, the formula returns 0 rather than the current day. After evaluating this formula for all 3 days we then take the max day returned to find an available and valid day to allocate the resource for this row. I used max here instead of min due to 0 being returned for invalid days. This means our resources are allocated to the days in reverse order, but the end result is essentially the same.

I wrote this all up quickly so if anyone has any questions about the logic please feel free to ask for clarification.

# ? Jan 6, 2012 04:37

hog wizard: Feb 16, 2005; by angerbeet

Like many of us, I have to work on Excel spreadsheets all day. I can do a lot of the basic and intermediate functions but I need help automating a process that I do to make my work a lot more efficient.

Basically, I have 3 tabs (Report, Client, and Schedule). Please take a look:

Report

Client

Schedule

I'm going to put in a drop down menu somewhere on the Report tab that lists all of the clients and then create a button that I can press to reflect the information from both Client and Schedule tabs according to whichever client's name I pick from the dropdown.

As you can see not every client has the same number of rows. So for example, if I choose Adam from the drop down menu, the Report tab first needs to show "Adam" where it says CLIENT NAME at the header, then all of Adam's data from the Client tab, and all 8 of his rows from the Schedule tab needs to be listed under the Schedule Info table. If it was for Cory, the info from the Client tab would be reflected, but only the 4 rows containing his name would listed.

I think a macro is best for this but I'm really not familiar with macros and if someone can please help me and write out a code for this that would be the greatest thing ever. I get a ton of these reports each day and making automating generating reports would make my life so much easier.

Can someone help me out with this? Thanks so much.

# ? Jan 12, 2012 17:16

Adbot: ADBOT LOVES YOU

# ? Apr 28, 2024 19:56

Powdered Toast Man: Jan 25, 2005; TOAST-A-RIFIC!!!

Having trouble with a sheet that is pulling from an external data source. Some details:

-The file itself is generated by an automated process on the database server, and is then emailed out to a group. It is initially generated in Excel 2003 format.

-The database connection is to MSSQL2005 through Microsoft Query and is calling a stored procedure. We have verified that the database connection itself is ok, and the stored procedure is also working properly.

-The end user opens the file in Excel 2007. At this point the imported data appears to be static, but if you select the range you can see the connection and everything looks ok, however:

-Refresh options are grayed out
-Clicking on the Refresh button does nothing. Literally nothing. You click it, and nothing happens. No errors, and the data does not update.

It is also worth noting that if you open the connection in Microsoft Query it will pull data there just fine. It simply isn't getting into the sheet.

Any ideas?

EDIT:

As an additional point, we were going to try unlinking and relinking the cells, so we found this:

Excel 2007 Help File posted:

Freeze data in an Excel table
Click the worksheet that contains the Excel table from which you want to remove the data connection.
On the formula bar, click the arrow next to the Name Box, and then click the name of the external data range from which you want to remove the data connection. The data range is then selected.
On the Tools tab, in the External Table Data group, click Unlink. The data range remains and still bears the same name, but the connection is deleted.

What the gently caress? Where is this "Tools tab" thing?

Powdered Toast Man fucked around with this message at 18:55 on Jan 12, 2012

# ? Jan 12, 2012 18:52

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »