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 »

Xguard86: Nov 22, 2004; "You don't understand his pain. Everywhere he goes he sees women working, wearing pants, speaking in gatherings, voting. Surely they will burn in the white hot flames of Hell"

Busy Bee posted:

You might not have time for this but one thing that I've found very helpful in the past is starting a new workbook with the raw data and rebuilding everything to understand how it fits together. Its a great learning tool and can really improve those "legacy XLs" that have been passed around for years and get kind of bloated (if that is what this is).

# ? Apr 25, 2013 16:12

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 20:53

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

Boris Galerkin posted:

I have two columns filled with thousands of numbers, and I want to quickly check if column A = column B. I thought I could just do: =A:A=B:B. This gives me a single TRUE/FALSE, except that it's not correct (it's telling me TRUE but I know for a fact A does not equal B in this test). I don't want to have to check every single row individually because there are thousands of rows and multiple columns I want to check.

Surely there must be a way to do this?

I don't care to know where the columns differ, I just need to know if column A = B is true or not so that I can purge a column if true.

Another route is to insert a column, fill the first value with "=A1=B1" and autofill that. Then you can use autofilter to find rows that did / didn't match.

If you have millions of rows performance will suck compared to an array function, but this is easier and guaranteed to work.

# ? Apr 25, 2013 16:47

Hoops: Aug 19, 2005; A Black Mark For Retarded Posting

Is there a way to link cells on different sheets so that you can change the data in any of the sheets and it updates in all of them? Previously I've used a "master" sheet for model parameters with every sheet referenced to that, but I'd like to avoid having to click back to the master all the time to change something.

# ? Apr 25, 2013 18:11

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

Hoops posted:

Is there a way to link cells on different sheets so that you can change the data in any of the sheets and it updates in all of them? Previously I've used a "master" sheet for model parameters with every sheet referenced to that, but I'd like to avoid having to click back to the master all the time to change something.

Yep, just make cell references on whatever other sheets you have, but add the master sheet in front, like this:

code:

=Sheet1!A1

edit: Oh, nevermind, you're asking for linking both ways, not a one-way link. Outside of some VBA or something, I don't see a way to avoid a circular reference.

Googled around and came up with this VBA from here, feel free to try it out.

code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rFrom as Range

    If Target.Count = 1 Then
        With Target.WorkSheet
             Set rFrom = .Range("A1:A20")
             If Not Intersect(Target, rFrom) Is Nothing Then
                 Application.EnableEvents = False
                 'Include next line Just in Case something happens
                 '    You don't want to leave EnableEvents off
                 On Error Resume Next
                 rFrom.Copy Worksheets("Sheet2").Range("B10:B30")
                 If Err.Number <> 0 Then
                     Msgbox "Error Occurred"
                 End If
                 Application.EnableEvents = True
             End If
         End With
    End If
End Sub

I haven't tried it out but it looks like it should link A1:A20 on Sheet1 to B10:B30 on Sheet2. You have to put this code on Sheet1, and reverse it for Sheet2.

last edit: Tried it out, it works. Based on the logic you can extend it to any number of cells or worksheets as well.

DukAmok fucked around with this message at 18:39 on Apr 25, 2013

# ? Apr 25, 2013 18:30

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

I have no idea why this isn't working so hopefully someone can point out what's wrong. What this does is checks to see if a main folder exists by testing the length of a directory, and if it doesn't then it creates it. This part works fine.

After that, a loop starts creating folders for items in an array if they don't exist (supposedly). Here's the code (I split up the 2nd to last line to stop table breaking):

code:

    If Len(Dir(destinationfolder & newfolder)) = 0 Then fs.createfolder destinationfolder & newfolder
    destinationfolder = destinationfolder & newfolder
    For i = 1 To UBound(arrdata)
    If Dir(destinationfolder & arrdata(i, 1) & " - " & arrdata(i, 2)) = vbNullString Then fs.createfolder 
destinationfolder & arrdata(i, 1) & " - " & arrdata(i, 2)
    Next i

The first two lines work perfectly, the folder exists and it's not creating anything. I've tried both len() and have it checking for vbnullstring as well, both say the folder doesn't exist and then tries to create said folder followed by an error saying the folder already exists. I setup a temp variable to read both len() and dir() and both return with 0 and nothing. Any ideas?

Edit:
Well that was quick. Ends up I need a vbdirectory in my dir statement. No idea why the first part was working but oh well.

Harry fucked around with this message at 23:02 on Apr 26, 2013

# ? Apr 26, 2013 22:45

C-Euro: Mar 20, 2010; Soiled Meat

How do I normalize a set of X-Y data points relative to a specified point in that set? I have some spectra that I've collected for my research and I'm trying to normalize them to an internal standard, i.e. assign the standard peak to a Y-value of 1 and adjust the Y-values of all other points relative to that. Normally I do this in Origin since it has a simple function to normalize such data sets relative to the highest Y-value, except in some of my data sets the standard peak isn't the highest Y-value, so the function doesn't work as intended and I can't figure out how to make the built-in Origin function normalize to anything but the highest peak. So I guess if you've worked with Origin, I'll take an answer for how to do it there as well :v:

# ? Apr 26, 2013 23:53

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Couldn't you just divide all of the Y values by the highest Y value (or whatever you're normalizing against)?

Easiest way to do that is to copy the Y value, select the data set, Paste Special and choose Divide.

# ? Apr 27, 2013 00:05

C-Euro: Mar 20, 2010; Soiled Meat

ZerodotJander posted:

Couldn't you just divide all of the Y values by the highest Y value (or whatever you're normalizing against)?

Easiest way to do that is to copy the Y value, select the data set, Paste Special and choose Divide.

Oh duh, I can't believe I didn't think of that. Thank you!

C-Euro fucked around with this message at 01:23 on Apr 27, 2013

# ? Apr 27, 2013 00:16

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

Harry posted:

code:

    If Len(Dir(destinationfolder & newfolder)) = 0 Then fs.createfolder destinationfolder & newfolder
    destinationfolder = destinationfolder & newfolder
    For i = 1 To UBound(arrdata)
    If Dir(destinationfolder & arrdata(i, 1) & " - " & arrdata(i, 2)) = vbNullString Then fs.createfolder 
destinationfolder & arrdata(i, 1) & " - " & arrdata(i, 2)
    Next i

I'm surprised you got it working. It appears to be missing some ELSEs and END IFs that should stop the script from running.

# ? Apr 27, 2013 07:00

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

I'm also surprised at the crazy use of Dir when you seem to have a FileSystemObject right there from which to call FolderExists.

# ? Apr 27, 2013 15:04

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

Old James posted:

I'm surprised you got it working. It appears to be missing some ELSEs and END IFs that should stop the script from running.

If it's on the same line you don't need an else or end if.

ShimaTetsuo posted:

I'm also surprised at the crazy use of Dir when you seem to have a FileSystemObject right there from which to call FolderExists.

I don't deal with FileSystemObject very much obviously.

# ? Apr 27, 2013 20:22

DirtyTalk: Apr 7, 2013

Not too good at VBA, but curious if this could work:

I took a VBA class in one of my introductory Engineering courses a few years ago. I think I could dig into my brain to try to program in Excel again for a program I'd like to use. But first, I wanted to gauge if this even sounds do-able with VBA:

What I have is this - I have an excel list of data that originated from various excel files. Lets call this data "keepers". What I want to do is have my program go down the list of keepers, and search through a folder full of original Excel files and find which original file the keeper came from, and output that original file name to me, or just insert it in the spreadsheet that the keepers are in.

Let me know if I wasn't clear or if anyone still has some questions as to what I'm looking to do.

# ? Apr 29, 2013 15:25

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

DirtyTalk posted:

Not too good at VBA, but curious if this could work:

I took a VBA class in one of my introductory Engineering courses a few years ago. I think I could dig into my brain to try to program in Excel again for a program I'd like to use. But first, I wanted to gauge if this even sounds do-able with VBA:

What I have is this - I have an excel list of data that originated from various excel files. Lets call this data "keepers". What I want to do is have my program go down the list of keepers, and search through a folder full of original Excel files and find which original file the keeper came from, and output that original file name to me, or just insert it in the spreadsheet that the keepers are in.

Let me know if I wasn't clear or if anyone still has some questions as to what I'm looking to do.

So you want the program to look through your list and then search other files to find if the list value exists somewhere in the workbook? Yes that can be done. You might want to have it open each file in the folder as the outer loop and then do your search on list items in the inner loop. From my experience opening and closing external workbooks will take longer than the searches, so you would want to minimize that as much as possible.

Harry posted:

If it's on the same line you don't need an else or end if.

Good to know. I think I'll stick with the more verbose syntax just so I don't confuse myself when debugging the code.

# ? Apr 29, 2013 16:47

DirtyTalk: Apr 7, 2013

Old James posted:

So you want the program to look through your list and then search other files to find if the list value exists somewhere in the workbook? Yes that can be done. You might want to have it open each file in the folder as the outer loop and then do your search on list items in the inner loop. From my experience opening and closing external workbooks will take longer than the searches, so you would want to minimize that as much as possible.

Good to know. I think I'll stick with the more verbose syntax just so I don't confuse myself when debugging the code.

Okay, there is a way to setup the program to sift through every workbook in a certain directory? It's not a specific number or set of workbooks as the workbooks are constantly being added to.

I guess you're right, the opening and closing of the workbooks sound like this will be the biggest issue. Especially because Im talking millions of records here. Any ideas on how this could be streamlined?

# ? Apr 29, 2013 18:03

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

DirtyTalk posted:

Okay, there is a way to setup the program to sift through every workbook in a certain directory? It's not a specific number or set of workbooks as the workbooks are constantly being added to.

I guess you're right, the opening and closing of the workbooks sound like this will be the biggest issue. Especially because Im talking millions of records here. Any ideas on how this could be streamlined?

Using FSO: http://stackoverflow.com/questions/14245712/cycle-through-sub-folders-and-files-in-a-user-specified-root-directory

Using Dir: http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba

My suggestions:

Create a two-dimensional array for your list and the source file name. This will be used when you search the workbooks and working with variables in memory is quicker than reading cell values from the HDD.
Create outer loop to cycle through files
Create inner loop to run through the first dimension of the array
If there are no values in the array where the second-dimension is blank, exit all loops. You have found all your matches and do not need to run through the remaining files.
When the second dimension of the array is blank (so you are only checking items in the list where you haven't yet found a matching source file) use the Find method on the open workbook.
If the Find method returns a cell value, write the file name to the second dimension of the array
Once the outer loop completes write the array back to the original spreadsheet

# ? Apr 29, 2013 20:56

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

DirtyTalk posted:

Okay, there is a way to setup the program to sift through every workbook in a certain directory? It's not a specific number or set of workbooks as the workbooks are constantly being added to.

I guess you're right, the opening and closing of the workbooks sound like this will be the biggest issue. Especially because Im talking millions of records here. Any ideas on how this could be streamlined?

http://www.ozgrid.com/VBA/loop-through.htm
This will probably help you. It sounds possible with find, assuming the data you're looking for is somewhat unique.

# ? Apr 30, 2013 18:53

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

DirtyTalk posted:

Okay, there is a way to setup the program to sift through every workbook in a certain directory? It's not a specific number or set of workbooks as the workbooks are constantly being added to.

I guess you're right, the opening and closing of the workbooks sound like this will be the biggest issue. Especially because Im talking millions of records here. Any ideas on how this could be streamlined?

Good lord. If you want this streamlined, get some kind of sensible backend for storing your data. Look at postgresql, mariadb, or some kind of nosql. If that sounds like greek, pay someone qualified to handle this.

A database (like the above) is a data storage, hardening, and retrieval tool.

Excel is a data representation tool. It is designed for taking filtered data and presenting it to non-geeks with pretty graphs and charts. It is not a data storage tool and your application has stretched it so far beyond its limits I'm surprised it hasn't turned inside out. :goatsecx:

# ? Apr 30, 2013 21:31

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

celestial teapot posted:

Good lord. If you want this streamlined, get some kind of sensible backend for storing your data. Look at postgresql, mariadb, or some kind of nosql. If that sounds like greek, pay someone qualified to handle this.

A database (like the above) is a data storage, hardening, and retrieval tool.

Excel is a data representation tool. It is designed for taking filtered data and presenting it to non-geeks with pretty graphs and charts. It is not a data storage tool and your application has stretched it so far beyond its limits I'm surprised it hasn't turned inside out.

Haha this was my exact thought. If you have millions of rows of data all stored across dozens or hundreds of workbooks, you're stretching Excel pretty thin. Get a real database before you become the next sensational news headline about losing $2 billion dollars because of a spreadsheet error.

# ? May 1, 2013 18:31

melon cat: Jan 21, 2010; Nap Ghost

I have a pretty basic question. I have an Excel file with this form:

How do I make it so all columns right of Column C are not editable or selectable at all? I'm trying to create a form that I can use to record information and easily make new entries, and I'm just trying to make this form as idiot-proof as possible. The part that I wanted locked up has the red box around it.

melon cat fucked around with this message at 02:32 on May 7, 2013

# ? May 7, 2013 02:26

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Highlight every column to the right (select column D, Ctrl+Shift+Right) and hide them. Delete (not clear) those cells first to avoid inflating your file size with empty cells.

# ? May 7, 2013 02:45

melon cat: Jan 21, 2010; Nap Ghost

ZerodotJander posted:

Highlight every column to the right (select column D, Ctrl+Shift+Right) and hide them. Delete (not clear) those cells first to avoid inflating your file size with empty cells.

That worked perfectly. Thanks so much!

# ? May 7, 2013 03:57

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

I'd encourage you to actually lock the columns once hidden. If you had given a data entry form to a batch of apes and they immediately disassembled it and pooped all over the pieces, nobody would be surprised. Well, people are apes. That's not a metaphor. We literally are apes, and we act like it. So if you must use Excel as a data entry tool (see above; it's a data representation tool, not a data entry tool), then lock your controls.

Edit: In fact, lock every cell that the users aren't supposed to be viewing or interacting with, and make sure you get thorough data validation set up on the controls they are allowed to touch.

celestial teapot fucked around with this message at 18:05 on May 7, 2013

# ? May 7, 2013 18:02

No Safe Word: Feb 26, 2005

If you want to make something in Excel super-unfuckwithable and near-completely idiot-proof, you can always dip into VBA (or VSTO if you're using it) and use the incredibly stupidly named level of hiding called "Very Hidden"

http://geeklyadvice.com/theres-hidden-and-then-theres-very-hidden

Can't even accidentally unhide it this way.

Has to be done at the sheet level though, but doesn't seem like it'd be a problem here.

e: also jesus christ that loving blog entry's last paragraph

quote:

So in the end, what is hidden can be made visible, but if very hidden, it may take some very elaborate steps to reveal. Kind of like my ex-wife's alternate personality, but that's an entirely different story!

# ? May 7, 2013 18:16

Vivian Darkbloom: Jul 14, 2004

I want to merge these three lists. It should merge into a format where each line has one country, plus its area, pop, and gdp. Close to just writing a Python script to do this, but there has to be some kind of way without tons of visual basic?

# ? May 10, 2013 12:56

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

Vivian Darkbloom posted:

I want to merge these three lists. It should merge into a format where each line has one country, plus its area, pop, and gdp. Close to just writing a Python script to do this, but there has to be some kind of way without tons of visual basic?

http://www.techonthenet.com/excel/formulas/vlookup.php

# ? May 10, 2013 14:22

Harry: Jun 13, 2003; I do solemnly swear that in the year 2015 I will theorycraft my wallet as well as my WoW

I'm probably being really stupid here, but sort each group by country name and then put the columns next to each other.

# ? May 10, 2013 19:20

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

No. Sorting won't guarantee that the values line up in case there are any missing or duplicate values in $A, $D, or $G. Use VLookup like Old James said. Make sure you set the 4th argument to FALSE or 0. If you leave out the last argument, it will default to true, which leads to behavior you probably won't expect.

Edit: It also wouldn't be a bad idea to do a COUNT on the area names to check for duplicates, because VLookup will only return the first matching value.

celestial teapot fucked around with this message at 19:51 on May 10, 2013

# ? May 10, 2013 19:47

Vivian Darkbloom: Jul 14, 2004

Thanks, dudes. I figured that fourth argument thing out by myself. I have no idea why TRUE is default, that's awful.

# ? May 11, 2013 12:21

Xenoborg: Mar 10, 2007

Is there any way to things like Sum and Count on only the visible part of a filtered list? Preferably something that can just be in a cell and not involving macros.

# ? May 12, 2013 21:03

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

Xenoborg posted:

Is there any way to things like Sum and Count on only the visible part of a filtered list? Preferably something that can just be in a cell and not involving macros.

Formulas will count or sum anything in your range, whether the cell is visible or not. You can get around this with a macro to write a custom formula, but you said that was off limits. Depending on what you are trying to do you could create a pivot table to summarize your data or use countifs/sumifs.

# ? May 12, 2013 21:26

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Xenoborg posted:

Is there any way to things like Sum and Count on only the visible part of a filtered list? Preferably something that can just be in a cell and not involving macros.

This might not solve your problem but if you select a filtered area the totals shown in the status bar are for the visible cells only.

# ? May 12, 2013 22:04

Raven31: Feb 4, 2006

Xenoborg posted:

Is there any way to things like Sum and Count on only the visible part of a filtered list? Preferably something that can just be in a cell and not involving macros.

The subtotal function will do this for options 109 (sum) and 102 or 103 ( count and counta).

# ? May 13, 2013 00:33

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

Raven31 posted:

The subtotal function will do this for options 109 (sum) and 102 or 103 ( count and counta).

http://office.microsoft.com/en-us/excel-help/subtotal-function-HP010062463.aspx

I like it.

# ? May 13, 2013 01:34

Xenoborg: Mar 10, 2007

Old James posted:

http://office.microsoft.com/en-us/excel-help/subtotal-function-HP010062463.aspx

I like it.

Ah, great, thanks everyone.

# ? May 13, 2013 03:09

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

Xenoborg posted:

Is there any way to things like Sum and Count on only the visible part of a filtered list? Preferably something that can just be in a cell and not involving macros.

If what has already been said didn't work for you, you probably need COUNTIF or COUNTIFS. That would be the most 'parsimonious' solution, if you give a poo poo about that (not like using an auto-filtered table is that complicated).

# ? May 14, 2013 16:59

Busy Bee: Jul 13, 2004

I currently have a Pivot Table with the above data. I can filter it by the Function of Business and whether the purchase order is open or closed. There are around 10 different functions of business, 10 different company codes, a lot of people, and the purchase order numbers. I am trying to find the value of how many purchase orders each person has.

I tried to do a "Count of Purchase Order Numbers" as the value in the Pivot Table but it doesn't work for the following reason. Within each order number, there can be anywhere from 1 - 10 different line items. Meaning one purchase order number can have three line items with such descriptions such as "Bill for conference room" / "Bill for service" / "bill for cleanup" etc. So when I use the "Count of Purchase Order" value, it counts each line item so someone can have only one purchase order number, but if that number has three line items, then the value shows as 3 for "Count of Purchase Orders".

What would be the easiest way for me to see the number of purchase orders each person has?

I'm thinking I could manually create a formula field and insert it into the Pivot but what type of formual would I use?

Busy Bee fucked around with this message at 19:53 on May 15, 2013

# ? May 15, 2013 00:00

celestial teapot: Sep 9, 2003; He asked my religion and I replied "agnostic." He asked how to spell it, and remarked with a sigh: "Well, there are many religions, but I suppose they all worship the same God."

It would be easier to say for sure if I could see the data source instead of the pivot table, but a pivot table should be able to do this for you: you would add a total row for PO Number and change it from a sum to a count. Edit: Okay, it sounds like you said you tried that, which is weird. Pivot tables are designed to handle data normalized in the way that you described. Can you post the workbook?

If you want a function to do this, you probably want DCOUNTA:

http://www.ozgrid.com/Excel/excel-count-one-occurrence.htm
http://office.microsoft.com/en-us/excel-help/dcounta-HP005209050.aspx

# ? May 15, 2013 21:10

Uziel: Jun 28, 2004; Ask me about losing 200lbs, and becoming the Viking God of W&W.

This question is for Microsoft Word but I figure this is the closest thread topic.

I'm writing a guide that references a database name throughout the document.
I want the reader/user to define what they've named the database when they open the document, and then have references within the document point to that variable declaration. What is the easiest way to do this?

Example:

code:

Database Name = UserDB

When publishing the site make sure that the App Pool has read access to [Database Name]

# ? May 16, 2013 20:06

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

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 20:53

stuxracer: May 4, 2006

Uziel posted:

This question is for Microsoft Word but I figure this is the closest thread topic.

I'm writing a guide that references a database name throughout the document.
I want the reader/user to define what they've named the database when they open the document, and then have references within the document point to that variable declaration. What is the easiest way to do this?

Example:
code:
Database Name = UserDB

When publishing the site make sure that the App Pool has read access to [Database Name]

Here are 9 ways of doing this - http://gregmaxey.mvps.org/word_tip_pages/repeating_data.html

I personally use custom document properties on the rare occasion I need this.

stuxracer fucked around with this message at 17:50 on May 17, 2013

# ? May 17, 2013 17:41

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

«‹›82 »