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

Nickelodeon Household: Apr 11, 2010; I like chocolate MIIIILK

As a prelude, let me apologize for any rambling retardedness with my post.

I'm wondering if there's any shortcut method for something I'm trying to do with excel.

So I have one master workbook and literally hundreds of other workbooks from which bits and pieces of data are needed from. Basically, I need to pull the data from a given cell on a specific sheet (say F54) from the hundreds of other workbooks and then put them on the master workbook. Then I need to repeat it for the next type of data.

All of the hundreds of workbooks that aren't the master are organized by year and file name (with the same file name shared for the same source, e.g. Folder: 2010 File: County 1; Folder: 2009 File: County 1. I have like 100 of these counties and years ranging from 2004-2010.

Currently, I've been thinking of employing a brute-force method wherein I create a reference in my master workbook of ='[c:/desktop/projectname/2010/county1]sheetXYZ'!$F$54 (please excuse me if this is plain retarded, I just typed it up off the top of my head).

Copy and paste across the row and change the year entry.
Then move down and do county 2 and follow the same steps.

I wouldn't mind the brute force method, but each of my sheets for the master workbook has a different type of data so I'd have to do this process literally 60,000 times and that just seems excessively time consuming.

So what short cut methods can I employ that will speed this up. I don't have any programming experience and haven't taken a class on office since 1999 (and even then the prof spent the entire time ranting about the virtues of RDRAM as opposed to SDRAM). I'm not sure if I can do this with a VLOOKUP or if I need to use VBA (or how to do it either will work).

Thanks for any help you can give me.

# ¿ Jan 4, 2011 22:07

Adbot: ADBOT LOVES YOU

# ¿ Apr 27, 2024 17:08

Nickelodeon Household: Apr 11, 2010; I like chocolate MIIIILK

Any clue of a good source to use as a template. I found this. but am not really sure if it's correct or how exactly to modify it.

# ¿ Jan 5, 2011 04:26

Nickelodeon Household: Apr 11, 2010; I like chocolate MIIIILK

G-Dub posted:

Is it the same cell in each workbook you are wanting to get? If you can post some sample source data and the desired sample format I will take a stab at it.

Sorry for the delayed response, I've been traveling quite a bit these past 2 days.

Hopefully this makes sense:

As you can see for by this example, the code for the 2004 value for Adair is: ='C:\Users\Jim\Desktop\3rd Class Cities\County Budgets\2005_budgets\[adair.xls]GR R'!$H$104

For the 2005 value for Adair the code is: ='C:\Users\Jim\Desktop\3rd Class Cities\County Budgets\2006_budgets\[adair.xls]GR R'!$H$104

For the next one Andrew the code is: ='C:\Users\Jim\Desktop\3rd Class Cities\County Budgets\2005_budgets\[andrew.xls]GR R'!$H$104

So is there a short-cut method to tell excel to pull the year's value from the next year's folder and from the file that matches the name in the first column. If changing my year headers in the top row makes this easier, I can do that too because these values will be copied and pasted as values into the actual working spreadsheet (the boss doesn't want any cells to directly reference another sheet as the recipients will not have access to all the sheets).

# ¿ Jan 8, 2011 04:22

Nickelodeon Household: Apr 11, 2010; I like chocolate MIIIILK

Fuoco posted:

There should be a way of doing this without the need for VBA. It's a bit inelegant though. Try the following...

In cell B2 put the following formula:
code:
="='C:\Users\Jim\Desktop\3rd Class Cities\County Budgets\" & B$1+1 & "_budgets\[" & LOWER($A2) & "]GR R'!$H$104"
This will return text to the cell of the full reference that we need. Fill the formula across and down to cover all the space required.

Save your spreadsheet at this point (but don't close it). Open up Notepad.

In your spreadsheet, select B2 down to the end, and copy the cells. Paste these into Notepad.

Then, copy the full text back from Notepad and paste into the same place in Excel. You'll notice at this point, rather than pasting back the reference as text, it's now actually reading from the reference instead.

Do the same with the remaining columns to get the remaining data.

Thanks, I tried this and it worked like a charm. Now I'm going to adapt this technique for the other values I need. On the plus side, this showed me that some of the counties disappeared over the data range (they're classified based on size so that's not huge shock) so I have a few #refs, but they're a data issue for my boss to deal with. Honestly, you just made this job infinitely easier. Thanks again.

# ¿ Jan 10, 2011 01:11

Nickelodeon Household: Apr 11, 2010; I like chocolate MIIIILK

Ok, I'm trying to convert a vector to a matrix (440x440) in excel. Normally, I could shortcut this through 1 function, but my vector is missing any zero values as the latest version of the software auto suppresses zeros (of which there's 250k so there's no practical way to fill them in. My data are organized in 3 columns, ColumnCode, RowCode, Value. What I need to do is write a function that will check the first column (ColumnCode) to see if it matches my column (B$1), then check the second column (RowCode) to see if it matches the row ($A2), and then return the value in the 3rd column. If there's no match, then I need the formula to return a zero value. Then I should be able to copy and paste it into all columns and rows and have it return all of the values I need.

As pictures usually help, here is my data worksheet:

Here is my output sheet:

For anybody wondering why the gently caress I'm dealing with a 440x440 matrix (believe me, I ask myself the same question all too often), its for an input-output model.

E: Fixed for broken tables.

# ¿ Jun 13, 2011 06:19

Nickelodeon Household: Apr 11, 2010; I like chocolate MIIIILK

Thank you.

gwar3k1 posted:

Sounds like a few nested ifs really but I thought I'd write some VB for you. You could probably work out an excel function from this.

Untested and I think the loop might be insufficient.

code:

Sub VectorToMatrix
  iDRow = 3
  iORow = 2
  iOCol = 2
  iCount = 1

  Do While iCount <= (420*420)
    iResult = 0

    'Column code = [col]1
    If Sheets("data").Cells(iDRow, 1) = Sheets("Output").Cells(1, iOCol) Then
      'RowCode = A[row]
      If Sheets("data").Cells(iDRow, 2) = Sheets("Output").Cells(iORow, 1) Then
        iResult = Sheets("data").Cells(iDRow, iDCol)
      End if
    End if
    Sheets("output").Cells(iORow, iOCol) = iResult
    
    'Loops by column then row
    iOCol = iOCol + 1
    If iOCol = 442 Then
      iOCol = 2
      iORow = iORow + 1
    End if
 
    iDRow = iDRow + 1
    iCount = iCount + 1
  Loop
End Sub

I'm getting a message saying run-time error '6': overflow. Is this telling me I have to much data for this to work? When I go to debug, I get the error at:

code:

Do While iCount <= (420 * 420)

gwar3k1 posted:

Which is this right?
code:
=if(CC=B$1,if(RC=$A1,RESULTCELL,0),0)

Should this be RC=$A2?

I'm not sure if I follow, are you saying the VB code is identical to this if function (in other words, the VB is unnecessary)? I tried this separately and it didn't work, but it could be that I'm putting the wrong thing in for resultcell (I tried as is to no avail). I have the data in that column-named "AIJ", should I just put that in for resultcell?

I was able to write a function that accomplished this but it absolutely crushes my computer's processors and takes about 2 hours to process (I had to disable auto calculate as it just kept trying to recalculate).

code:

=INDEX(AIJ,MATCH(1,($A2=row)*(B$1=Column),0))

where row and column are the names for the respective data ranges (and again AIJ is the values I want).

Sorry, I'm completely retarded with excel. I usually fumble my way through through extensive use of the help and google.

# ¿ Jun 13, 2011 10:43

Nickelodeon Household: Apr 11, 2010; I like chocolate MIIIILK

The pivot table solution worked like a charm. Thank you Aredna, Sub Par, and Gwar for your help. Now I have the fun of subtracting it from an identity matrix and inversion, but I can use SAS for that.

# ¿ Jun 13, 2011 19:04

Nickelodeon Household: Apr 11, 2010; I like chocolate MIIIILK

So I have this spreadsheet of all property sales for NJ for the years 2002-2011 (let's call it "master"). In a second spreadsheet, I have observations of interest culled from the master sheet (let's call it "observations"). What I need to do is figure out a function that will search the master spreadsheet for instances where the text in columns B (seller's name) and E (buyer's name) and the date of sale in column Q matches in both tables and return the entire matching row (that is, the data in columns A to Z) to a third spreadsheet.

Secondly, I need to write a function that will do the same as above only it needs to pull values only if the text in column L is "3B".

To the first function can I do something like

code:

=vlookup(B1&E1&Q1, master table range, column number, false)

Or would something like this be better

code:

=sumproduct((master!B:B= observations!b1)*(master!E:E=observations!e1)*(master!Q:Q=observations!q1)*master!a:a)

with one of these for each column?

What if there's 4 or 5 matches in the matches for a given set of values in my observation table? Will this affect my results (that is, will it return each of the matches or only the first)?

Am I better off building a pivot and doing a getpivot function (and if so, any pointers here would be welcome as I don't have a lot of experience with pivot functions)?

# ¿ Jul 6, 2012 14:27

Adbot: ADBOT LOVES YOU

# ¿ Apr 27, 2024 17:08

Nickelodeon Household: Apr 11, 2010; I like chocolate MIIIILK

e: Nevermind, I'm a tremendous dummy.

Nickelodeon Household fucked around with this message at 03:09 on May 17, 2013

# ¿ May 17, 2013 02:58

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