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 »

Mak0rz: Aug 2, 2008; 😎🐗🚬

There must be a way to do this but Google really isn't much help.

I made a quick scatter plot that produced one with a numerical X axis that I want to change to three categories. Is there a way to do this without starting over? I tried just filling three cells with the names and putting those in the in the 'Series X values' section of the Select Data option but it didn't change anything.

Edit: According to the Internet it's as simple as opening the axis options panel and changing the type to text, but I don't get that option at all. According to this page it should be before the Bounds options :shrug:

Likewise, according to the Excel help file, all I have to do is go to the Select Data window and edit the "Horizontal (Category) Axis Labels" options directly, but I can't do anything with them because the edit option is grayed out.

I even tried scorched-earthing the loving thing. Deleted the chart and this time highlighted the categories before inserting a new one. It still produces a numerical axis. What the gently caress is going on Jesus Christ :psyduck:

Edit 2: God drat it never mind. Apparently categorical axes can't be done on a scatter plot. I need to make a line plot instead and kill the line. This would have been handy if any of the sites said so when I Googled them. Go die in a loving fire, Excel :argh:

Mak0rz fucked around with this message at 18:27 on Nov 22, 2016

# ? Nov 21, 2016 18:43

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 03:05

Inzombiac: Mar 19, 2007; PARTY ALL NIGHT

EAT BRAINS ALL DAY

I have two workbooks on an intranet where one has an open connection to the other that refreshes periodically. Someone deleted a column in the source book and now my data connection table is all jacked up.

They deleted the rightmost column that has data. Now when the other one updates, it send the first two all the way to the right and partially drops data from one in the middle.
When I first launch the updating book, it is fine but the first time it updates, everything goes haywire.

# ? Dec 1, 2016 01:11

coyo7e: Aug 23, 2007; by zen death robot

Inzombiac posted:

I have two workbooks on an intranet where one has an open connection to the other that refreshes periodically. Someone deleted a column in the source book and now my data connection table is all jacked up.

They deleted the rightmost column that has data. Now when the other one updates, it send the first two all the way to the right and partially drops data from one in the middle.
When I first launch the updating book, it is fine but the first time it updates, everything goes haywire.

Call IT and ask them to pull an older copy for you? It should be a piece of cake if they even have half of a backup solution in place.

# ? Dec 1, 2016 20:32

Inzombiac: Mar 19, 2007; PARTY ALL NIGHT

EAT BRAINS ALL DAY

coyo7e posted:

Call IT and ask them to pull an older copy for you? It should be a piece of cake if they even have half of a backup solution in place.

Hahahahahaha
I'm the admin, sortof. Our only IT guy in a contractor that knows only how to sit around and maybe install a printer. However he has all the authority and has the mentality of, "if it's not in my job description, you can go gently caress yourself."

# ? Dec 2, 2016 06:33

coyo7e: Aug 23, 2007; by zen death robot

Inzombiac posted:

Hahahahahaha
I'm the admin, sortof. Our only IT guy in a contractor that knows only how to sit around and maybe install a printer. However he has all the authority and has the mentality of, "if it's not in my job description, you can go gently caress yourself."

Fire him. A good printer vendor can handle maintenance and nobody should be paid for IT if they can't use Shadow Copy.

# ? Dec 2, 2016 15:29

Inzombiac: Mar 19, 2007; PARTY ALL NIGHT

EAT BRAINS ALL DAY

coyo7e posted:

Fire him. A good printer vendor can handle maintenance and nobody should be paid for IT if they can't use Shadow Copy.

Would if I could but he's a contractor for the government. The person that would make that decision has no interest in rocking the boat.

# ? Dec 3, 2016 19:12

Kibayasu: Mar 28, 2010

So as the resident "young person" who "knows computers" (which is, to be fair, partially true in this case at least when it comes to the very basics of Excel) I will getting a data dump which will list specific commodities and their quantities from a program not under my control (so I can't control the format of the data). What I will get will be something similar to this:

I'm summarizing the grand totals of each commodity on another worksheet using a SUMIF array looking for a specific text in the Commodity column and adding the value in the Quantity column if they match. Using the above example:

code:

{=SUM(IF(Sheet2!$A$2:$A$11=A1,Sheet2!$E$2:$E$11))}

Which, after changing the reference row for each specific commodity, results in this on my Summary worksheet:

So far, that's working well enough for my testing sheet but I've run into a question that could make pulling the data from the dump file much easier. The actual data file can extend several thousand rows deep and while it will always will always start at the same row it will basically never end at the same row. However columns A and E will always end at the same row as each other. So using the above example, A11 or E11 might be end point of today but tomorrow it might be A2519 and E2519.

Is there a way to enter a number value in a cell on the summary sheet and have that be the end of each range in my code above?

So if I was to enter 11 into B9 above, my code for summarizing the Commodities and their Quantities would reference the value in B9 for the end of the ranges (A2:A11, E2:E11) looking at the data dump work sheet. My mind went to INDIRECT but I'm not sure how to use it in this case, or if I even should.

# ? Dec 6, 2016 20:25

Wandering Orange: Sep 8, 2012

You can use indirect inside the sum formula: '=sum(indirect("A2:A"&B9)) , where B9 is your value of the last row. You're basically creating the range of cells in string form so you can append the last number/row from your specified cell.

# ? Dec 6, 2016 21:24

mystes: May 31, 2006

Kibayasu posted:

So as the resident "young person" who "knows computers" (which is, to be fair, partially true in this case at least when it comes to the very basics of Excel) I will getting a data dump which will list specific commodities and their quantities from a program not under my control (so I can't control the format of the data). What I will get will be something similar to this:

Why not use a pivot table?

# ? Dec 6, 2016 21:26

Kibayasu: Mar 28, 2010

Wandering Orange posted:

You can use indirect inside the sum formula: '=sum(indirect("A2:A"&B9)) , where B9 is your value of the last row. You're basically creating the range of cells in string form so you can append the last number/row from your specified cell.

I think this has solved it. Originally the problem was that I had no idea where to use the INDIRECT as part of the reference to the other worksheet but going by your post, since my range will always reference the same starting cell, I can just include the start of the range as part of the INDIRECT text. So now my function looks like:

code:

=SUM(IF(INDIRECT("Sheet2!$A$2:$A"&$B$9)=A1,Sheet2!$E$2:$E$11))

And it seems to work just fine. Thank you.

mystes posted:

Why not use a pivot table?

Because I have no idea what that is!

If that is a basic Excel thing then consider me even below that. If you think I could do this an easier way I'll take any advice I can get. Though after a brief Googling it seems like I probably wouldn't want to use one because I'd have to format it for every single export I make and currently I need to export 1 data file at a time. I didn't mention that depending on the work done in a day I could get anywhere from 1 to... a lot of these data files per day. Using the above method I want to say I can pull what I need out of the raw data of each export into another spreadsheet of my design without doing any formatting on a per file basis and use macros to do any repetitive moving from there.

# ? Dec 6, 2016 22:11

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

Just put all your data in one file, then use a pivot table. There's a few problems with your solution that you haven't noticed yet (what happens when I add a new fruit you haven't seen before?), and they will all go away if you just use a pivot table.

# ? Dec 6, 2016 23:47

Kibayasu: Mar 28, 2010

ShimaTetsuo posted:

Just put all your data in one file, then use a pivot table. There's a few problems with your solution that you haven't noticed yet (what happens when I add a new fruit you haven't seen before?), and they will all go away if you just use a pivot table.

Sorry, it's my fault for being a bit vague on what exactly I'm dealing with but after looking at it a bit more I don't think a pivot table will help me with what I need to do. New commodities aren't actually a concern and any new ones will be something we know about far in advance. Beyond that I'm also dealing with the limits of the export system this program we're using has. I can only export 1 data file at a time, for 1 customer at a time, and if I export a new file without moving/renaming the previous one it will be overwritten (we're in the same business that this system is meant to handle, but our model differs in significant ways). Those customers also need separate reports and without building an entirely new system from scratch separate reports in this case means separate spreadsheets for each customer, so no matter what the data being in a single file isn't a solution (again, without rebuilding everything which is something I am thoroughly not qualified to do).

# ? Dec 7, 2016 00:34

coyo7e: Aug 23, 2007; by zen death robot

Kibayasu posted:

So as the resident "young person" who "knows computers" (which is, to be fair, partially true in this case at least when it comes to the very basics of Excel) I will getting a data dump which will list specific commodities and their quantities from a program not under my control (so I can't control the format of the data). What I will get will be something similar to this:

Go to your boss(es) and tell them that you need to have them buy this book for you - or whatever the newest or most appropriate version is. https://www.amazon.com/Perspectives-Microsoft-Excel-2013-Introductory/dp/1285169360

You can kill a chapter in a couple hours (takes about 20-30 minutes to read, and then you can follow through the course-created spreadsheets and see how they are put together. If you go through the first like 4-8 chapters you will increase your excel vocabulary, best practises, and understanding of how to create and/or improve spreadsheets by a phenomenal amount). If you're being asked to do this poo poo at work, then the least they ought to do is pay for a decent reference/teaching book and maybe even give you a half hour a day to read it on the job, since you're literally reading it to learn how to do a job you've been tossed.

Don't loan it out unless you think you can get reimbursed for a second copy - I've only had one person ever return the book (and that was because they never opened it) when I loaned it out to give them a hand at their own work duties.

Don't touch pivot tables util you are making a summary/documentation page for every spreadsheet you create, using good color schemes, and understand the terminology. Pivot Tables are like building an ICBM when you may just need a machine gun that won't jam - too often.

coyo7e fucked around with this message at 07:28 on Dec 9, 2016

# ? Dec 9, 2016 07:23

Hughmoris: Apr 21, 2007; Let's go to the abyss!

Being a VBA novice, I about had a fit today trying to get regex to work for me.

I have a column of cells, each with a format like:

quote:

---------------------------------------
asjdfia
250
ajdfja
W123456789123
ajsdif
mzcv
hsafa
285
madmf
W111222333444
afmdfa
--------------------------------------

It's always a volume (250), a line of garbage, then the unit number (W123456789123). I want to extract the volume and its associated unit number, in pairs. Ideally, I'd end up with:

code:

| 250 | W123456789123 |
| 285 | W111222333444 |
...

I've been able to successfully do it in Python with re.findall() but I'm trying to write this as a macro for a coworker.

Any advice on how to go about this? I was following this example on stackoverflow, example #4 of the top response.

http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops

Hughmoris fucked around with this message at 01:34 on Dec 16, 2016

# ? Dec 16, 2016 01:30

mystes: May 31, 2006

Hughmoris posted:

Being a VBA novice, I about had a fit today trying to get regex to work for me.

I have a column of cells, each with a format like:

It's always a volume (250), a line of garbage, then the unit number (W123456789123). I want to extract the volume and its associated unit number, in pairs. Ideally, I'd end up with:
code:
| 250 | W123456789123 |
| 285 | W111222333444 |
...
I've been able to successfully do it in Python with re.findall() but I'm trying to write this as a macro for a coworker.

Any advice on how to go about this? I was following this example on stackoverflow, example #4 of the top response.

http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops

If the data is in exactly that format, surely you don't need a regex at all, you can just iterate over the cells (or rather the value array of the range, which is faster)?

# ? Dec 16, 2016 02:37

Hughmoris: Apr 21, 2007; Let's go to the abyss!

mystes posted:

If the data is in exactly that format, surely you don't need a regex at all, you can just iterate over the cells (or rather the value array of the range, which is faster)?

The volume and unit number always have one line of text separating them, but there is a random amount of data before and after them. The volume is always 3 digits, and the unit number is W + 12 digits.

Is there an easy solution that I'm missing?

# ? Dec 16, 2016 03:11

coyo7e: Aug 23, 2007; by zen death robot

So there can be an undefined number of lines of "garbage" and then always a three-digit number followed by exactly two lines? Yeah you can definitely do that, I'm on my phone right now though.

# ? Dec 17, 2016 20:17

SymmetryrtemmyS: Jul 13, 2013; I got super tired of seeing your avatar throwing those fuckin' glasses around in the astrology thread so I fixed it to a .jpg

Is it possible to sum the results of the product of each row in a table without using an extra column to make that calculation? Like so:

code:

Item | Number
  1  |   80
  2  |   37

How do I make a formula that calculates (1*80+2*37) and scales to an arbitrary number of rows, given a certain range or table name?

# ? Dec 17, 2016 22:07

ShimaTetsuo: Sep 9, 2001; Maximus Quietus

The function that computes sums of products is called SUMPRODUCT.

# ? Dec 17, 2016 23:35

SymmetryrtemmyS: Jul 13, 2013; I got super tired of seeing your avatar throwing those fuckin' glasses around in the astrology thread so I fixed it to a .jpg

ShimaTetsuo posted:

The function that computes sums of products is called SUMPRODUCT.

Thanks.

# ? Dec 17, 2016 23:41

Immolat1on: Sep 9, 2005

I'm trying to put together an excel sheet of my credit card transactions, which used to be very easy, but something changed and now it copies like poo poo, I tried pasting into word and converting a table, using the Import Text Wizard on excel, and nothing comes out with the correct formatting. I'm pretty sure its because the bank added a string of numbers or some poo poo that confuses everything. I'm using excel 2010 and here's what I'm dealing with:

Here's the source, a PDF with some sort of curse or hex cast on it. And a good representation of my spending, an impulse ebay purchase, gasoline, mexican food, and groceries:

These are my attempts, getting semi decent formatting in word:

But to no avail:

I was heavy with the censoring cause I have no idea if these numbers mean anything or have identifying info, please don't find me and abduct me into the sex trade.

Is there any way to do this without editing each line? I just want to make some neat pie charts on my spending but there's a lot of transactions so its not worth if I can't do it pretty quickly with each new statement.

# ? Dec 20, 2016 01:23

Fingerless Gloves: May 21, 2011; ... aaand also go away and don't come back

Looks like you might be able to do a text to columns, or set up a left/right/mid with a find space. This is assuming each line is contained in the first cell.

If you just want to extract the cost value, use =right(a1, find (" ",a1))

For the cost and the currency, use =right(a1,find(" ",a1,find(" ", a1)+1))

You can adapt that with left to get the other side

Fingerless Gloves fucked around with this message at 14:01 on Dec 20, 2016

# ? Dec 20, 2016 13:54

Ragingsheep: Nov 7, 2009

Does your card issuer's internet portal have a download to csv function?

# ? Dec 21, 2016 03:20

Convicted Bibliophile: Dec 2, 2004; I am the night.

I have a small task I need to accomplish in an existing excel spreadsheet but I'm a total newbie when it comes to vba/macros in Excel. Instead of posting it here (I'm not sure how long it would take and I don't want to waste people's time), does SA have a place where you can pay people to do small things in Excel for you?

# ? Dec 22, 2016 11:52

Hughmoris: Apr 21, 2007; Let's go to the abyss!

Convicted Bibliophile posted:

I have a small task I need to accomplish in an existing excel spreadsheet but I'm a total newbie when it comes to vba/macros in Excel. Instead of posting it here (I'm not sure how long it would take and I don't want to waste people's time), does SA have a place where you can pay people to do small things in Excel for you?

You might have some luck here: "Request a tiny custom app"

http://forums.somethingawful.com/showthread.php?threadid=2415898&userid=0&perpage=40&pagenumber=1

Hughmoris fucked around with this message at 21:57 on Dec 26, 2016

# ? Dec 26, 2016 19:13

Sri.Theo: Apr 16, 2008

Ragingsheep posted:

Does your card issuer's internet portal have a download to csv function?

Almost every bank will let you download CSV files. It might be hidden somewhere strange on the website as opposed to under statements though.

# ? Dec 27, 2016 10:56

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

This is an odd question and I'm not sure if it goes here, but I'll give it a whack.

I want to generate and print barcodes for internal inventory/scan purposes. I know how to do this in Excel/Word, but I've transitioned the office to LibreOffice Calc/Writer. So far from searching all I can find are some obscure VBA/add-ons from 2012, and was wondering if anyone knew a "known good" solution that doesn't involve running code locally (e.g. in the past I wrote a quick and dirty .NET program to do this) and can Just Work in LibreOffice? What I would need to do is create arbitrary bar codes and print them basically.

EDIT-Sorry, left one thing out. I tried all the barcode extensions/add-ons in the LibreOffice library and none of them work; they all error out with Python/Java errors during the install process.

Scaramouche fucked around with this message at 20:56 on Dec 27, 2016

# ? Dec 27, 2016 20:35

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Hmm, double posting with a workaround for now.

There's a plugin for Paint.net that generates UPC-A that I'm using for now:
http://forums.getpaint.net/index.php?/topic/31559-barcode-v14-nov-14-2015/

This isn't what I'd prefer (because it generates bitmaps) but is enough to get me started for now. If anyone has a better solution please chime in!

# ? Dec 27, 2016 21:51

Phraggah: Nov 11, 2011; A rocket fuel made of Doritos? Yeah, I could kind of see it.

Hughmoris posted:

Being a VBA novice, I about had a fit today trying to get regex to work for me.

I have a column of cells, each with a format like:

It's always a volume (250), a line of garbage, then the unit number (W123456789123). I want to extract the volume and its associated unit number, in pairs. Ideally, I'd end up with:
code:
| 250 | W123456789123 |
| 285 | W111222333444 |
...
I've been able to successfully do it in Python with re.findall() but I'm trying to write this as a macro for a coworker.

Any advice on how to go about this? I was following this example on stackoverflow, example #4 of the top response.

http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops

Not sure if you were able to figure this out. First I'd define two patterns. This assumes what you're looking for will always be in its own cell.

code:

VolumeRegex = "^\d{3}$"
UnitRegex = "^[Ww]\d{12}$"

where ^ is the start of a cell value, \d{3} is any digit repeated such that there are exactly three in a row. $ signals the end of a cell value.
[Ww] is a group of either one upper or one lower case W, replace [Ww] with W if you know it will always be caps.

Also make sure to change number in the curly braces for what your data actually has. You can use a single number if it will always be that many digits or a range {9-12} if there's variance.

Note that Excel has some RegEx quirks with how it chooses how to interpret cells as new lines and stuff. The ^ and $ may not work as intended in VBA. Do testing!

Then run the regex and store all matches in arrays. One array for Volumes and one for units.

If you know the number of volumes will always have a matching unit number (or visa versa), then simply group them by an iteration variable.

e.g. if those two assumptions are true, VolumeMatches[i] and UnitMatches[i] should always be from the same group. You should do adequate unit testing to make sure these assumptions are absolutely correct. An easy way to tell without looking is to try this and see if the arrays are different lengths.

Phraggah fucked around with this message at 23:29 on Jan 1, 2017

# ? Jan 1, 2017 23:23

Hughmoris: Apr 21, 2007; Let's go to the abyss!

Phraggah posted:

Not sure if you were able to figure this out. First I'd define two patterns. This assumes what you're looking for will always be in its own cell...

Thanks for this. This small project has taught me quite a few things: more VBA, slicers, Power Query, and the fact that Excel has a built in data form that you can use to query data. I thought I was going to have to create a VBA userform so that others could easily search the parsed data.

# ? Jan 2, 2017 15:59

Tom Tucker: Jul 19, 2003; I want to warn you fellers
And tell you one by one
What makes a gallows rope to swing
A woman and a gun

I've got a really annoying data crunching task to get through, and it seems like there should be a much simpler way to do it that I'm not finding. Basically I want to consolidate daily files into one larger file.

The mid-continent interconnect (MISO) posts prices for some markets they run every day in a big file. An example of the file is here: sample file.

The file is broken into two parts, the first rows 5 through 12, is the part I'm interested in, MISO-wide prices by hour. I downloaded Kutools, which is a great tool, but does not work well here because there's a ton of more granular data in the following rows (15- 4086) that gets pulled in with the data I want.

Ideally I would like a system that takes rows 5-12, transposes them (I want the hour to be the row and the "MCP Type" to be the column), then have 24 rows for January 1st 2016, 24 rows for January 2nd 2016, etc.

Any advice on how to do this? Ultimately I could go through all thousand-odd files I need and copy and transpose paste into one file but if there's anything I can do to prevent that much nonsense I'd really love to see it.

Thanks for anyone who has any ideas!

# ? Jan 4, 2017 21:19

ulmont: Sep 15, 2010; IF I EVER MISS VOTING IN AN ELECTION (EVEN AMERICAN IDOL) ,OR HAVE UNPAID PARKING TICKETS, PLEASE TAKE AWAY MY FRANCHISE

Tom Tucker posted:

Ideally I would like a system that takes rows 5-12, transposes them (I want the hour to be the row and the "MCP Type" to be the column), then have 24 rows for January 1st 2016, 24 rows for January 2nd 2016, etc.

Any advice on how to do this? Ultimately I could go through all thousand-odd files I need and copy and transpose paste into one file but if there's anything I can do to prevent that much nonsense I'd really love to see it.

Thanks for anyone who has any ideas!

In your place I think I would use a programming language (including VBA) to do the transposition over all the files.

Here's a proof of concept I tested on your sample file, that would just need to be somewhat revised to handle multiple files.

code:

Sub TransposeWorkbooks()
    Dim files As Variant
    files = Array("C:\Users\ulmont\Downloads\20170105_asm_exante_damcp.csv") 'insert more files, comma separated here
    
    For Each element In files
        ' open original workbook
        Dim origWb As Workbook
        Set origWb = Workbooks.Open(Filename:=element)
        Dim origWs As Worksheet
        Set origWs = origWb.Sheets(1)
        
        ' create new workbook for transposition
        Dim trWb As Workbook
        Set trWb = Workbooks.Add
        Dim trWs As Worksheet
        Set trWs = trWb.Sheets(1)
              
        ' Date is just A3
        trWs.Range("A3").Value = origWs.Range("A3").Value
        
        ' Header values, row and column
        trWs.Range("A5").Value = "Hour"
        Dim i As Integer
        For i = 1 To 24 ' all the hours
            trWs.Cells(5 + i, 1).Value = i
        Next
        
        ' Iterate over the data types and hours
        ' Hours are D-AA, Data types are in C
        Dim r As Integer
        r = 6 ' data rows begin at 6 and run through 12, but run until we hit a blank row
        c = 2 ' data entry columns will begin at B
        Do While (Not (IsEmpty(origWs.Cells(r, 1))))
            trWs.Cells(5, c).Value = origWs.Cells(r, 3) ' transpose type to header row
            
            For i = 1 To 24 ' transpose the value for each hour (starts at D6-AA6, then D7-AA7, etc.)
                trWs.Cells(5 + i, c).Value = origWs.Cells(r, 3 + i).Value
            Next
                        
            r = r + 1
            c = c + 1
        Loop
        
    Next element

End Sub

# ? Jan 5, 2017 00:10

azsedcf: Jul 21, 2006; ...a place of unlimited darkness.
"Where are the doors?" they asked nerviously.
Even my bellowing laughter couldn't fill this space.

I was handed a 'special project' in updating a old (excel 97) statistics logging sheet to a "newer version"(excel 2013).

Fortunately, there are no macros and most of it copied over with no problem except hammering on the formatting(which I can do over time) and an error in the procedure that causes the final averaging to gently caress up.

The worksheet in question has a table that should give an average number of customers per day of the week sorted by the service required. Every 4-ish months they copy the original file, rename it, and update the starting date and put new data in. Now this would not be a problem, but when they start up a new sheet they don't clean up the ending portion of the file well and either leave the empty days or select rows and delete them.

When they do this it is leaving (as far as I can tell) either a bunch of 0's that drag down the numbers, or (if they delete them) a formula that looks like =AVERAGE(B2,B15,B28,B41,B54,,,) and those commas effectively are being treated as zeros.

I've come up with a way (rear end backwards as it is) using =SUBSTITUTE(TRIM(CONCATENATE(B2,� �,B15,� �,B28,� �,B41,� �,B54," ",B67,� �,B80,� �,B93)),� �,�, �) to get a string that is the values of the cells comma separated without any empty commas, but the Average wants either cell names or just numbers, not whatever the substitute function puts out.

Is there a way to make this work? Or even better, is there a better way of trying to fix this?

# ? Jan 5, 2017 03:51

WhatsInaMojito: Dec 20, 2011

azsedcf posted:

The worksheet in question has a table that should give an average number of customers per day of the week sorted by the service required. Every 4-ish months they copy the original file, rename it, and update the starting date and put new data in. Now this would not be a problem, but when they start up a new sheet they don't clean up the ending portion of the file well and either leave the empty days or select rows and delete them.

When they do this it is leaving (as far as I can tell) either a bunch of 0's that drag down the numbers, or (if they delete them) a formula that looks like =AVERAGE(B2,B15,B28,B41,B54,,,) and those commas effectively are being treated as zeros.

1) Can you move the ending portion of the sheet to the top of the sheet?
2) Would a "Clean/Clear for start of month macro" help discourage folks from deleting rows?
3) Add a column to cleanup the input by returning an eptystring("") where there were zeros. =Average() ignores emptystrings. (Beware plots treat emptystrings as zeros)

# ? Jan 5, 2017 05:37

Tom Tucker: Jul 19, 2003; I want to warn you fellers
And tell you one by one
What makes a gallows rope to swing
A woman and a gun

ulmont posted:

code:

Sub TransposeWorkbooks()
    Dim files As Variant
    files = Array("C:\Users\ulmont\Downloads\20170105_asm_exante_damcp.csv") 'insert more files, comma separated here
    
    For Each element In files
        ' open original workbook
        Dim origWb As Workbook
        Set origWb = Workbooks.Open(Filename:=element)
        Dim origWs As Worksheet
        Set origWs = origWb.Sheets(1)
        
        ' create new workbook for transposition
        Dim trWb As Workbook
        Set trWb = Workbooks.Add
        Dim trWs As Worksheet
        Set trWs = trWb.Sheets(1)
              
        ' Date is just A3
        trWs.Range("A3").Value = origWs.Range("A3").Value
        
        ' Header values, row and column
        trWs.Range("A5").Value = "Hour"
        Dim i As Integer
        For i = 1 To 24 ' all the hours
            trWs.Cells(5 + i, 1).Value = i
        Next
        
        ' Iterate over the data types and hours
        ' Hours are D-AA, Data types are in C
        Dim r As Integer
        r = 6 ' data rows begin at 6 and run through 12, but run until we hit a blank row
        c = 2 ' data entry columns will begin at B
        Do While (Not (IsEmpty(origWs.Cells(r, 1))))
            trWs.Cells(5, c).Value = origWs.Cells(r, 3) ' transpose type to header row
            
            For i = 1 To 24 ' transpose the value for each hour (starts at D6-AA6, then D7-AA7, etc.)
                trWs.Cells(5 + i, c).Value = origWs.Cells(r, 3 + i).Value
            Next
                        
            r = r + 1
            c = c + 1
        Loop
        
    Next element

End Sub

I found a source that had good consolidating workbook code and was able to adapt this, thanks so much!

# ? Jan 5, 2017 14:59

ulmont: Sep 15, 2010; IF I EVER MISS VOTING IN AN ELECTION (EVEN AMERICAN IDOL) ,OR HAVE UNPAID PARKING TICKETS, PLEASE TAKE AWAY MY FRANCHISE

Tom Tucker posted:

I found a source that had good consolidating workbook code and was able to adapt this, thanks so much!

Glad it worked; I was just about to post the multi-workbook (every .csv in a given directory) version:

code:

Sub TransposeWorkbooks()
    Dim dirName As String
    dirName = "C:\Users\ulmont\Downloads\"
    Dim files As Variant
    files = Dir(dirName & "20170105_asm_exante_damcp*.csv")
    
    ' create new workbook for transposition
    Dim trWb As Workbook
    Set trWb = Workbooks.Add
    Dim trWs As Worksheet
    Set trWs = trWb.Sheets(1)
    
    ' initialize rows in new workbook
    Dim trRow As Integer
    trRow = 3 ' put date value in A3
    
    Do While (files <> "")
        ' open original workbook
        Dim origWb As Workbook
        Set origWb = Workbooks.Open(Filename:=(dirName & files))
        Dim origWs As Worksheet
        Set origWs = origWb.Sheets(1)
       
        ' Date is just A3 from the original spreadsheet; copy into A at whatever our current row is in the transposed one
        trWs.Cells(trRow, 1).Value = origWs.Range("A3").Value ' baseline is A3 for transposed worksheet
               
        ' Header values, row and column
        trWs.Cells(trRow + 2, 1).Value = "Hour" ' baseline A5
        Dim i As Integer
        For i = 1 To 24 ' all the hours
            trWs.Cells(trRow + 2 + i, 1).Value = i ' A6 etc.
        Next
        
        ' Iterate over the data types and hours
        ' Hours are D-AA, Data types are in C
        Dim r As Integer
        r = 6 ' data rows begin at 6 and run through 12, but run until we hit a blank row
        c = 2 ' data entry columns will begin at B
        Do While (Not (IsEmpty(origWs.Cells(r, 1))))
            trWs.Cells(trRow + 2, c).Value = origWs.Cells(r, 3) ' transpose type to header row, starting at B5
            
            For i = 1 To 24 ' transpose the value for each hour (starts at D6-AA6, then D7-AA7, etc.)
                trWs.Cells(trRow + 2 + i, c).Value = origWs.Cells(r, 3 + i).Value ' transpose data, starting at B6
            Next
                        
            r = r + 1
            c = c + 1
        Loop
        
        trRow = trRow + 25 + 3 ' skip over all the data we wrote, plus 3 rows before the next workbook
        files = Dir ' get next file
    Loop
End Sub

# ? Jan 5, 2017 16:32

mobby_6kl: Aug 9, 2009; by Fluffdaddy

I'm trying to build a report with PowerPivot that would calculate some values based on percentages allocated to different categories, and I'm almost there but can't get it to aggregate correctly. Here's a simple example that I'm using to figure this out:

So out of $1000 apple sales, 50% would be fresh apples, 50% of which would be in the western region, for example - assuming the splits are equal here, 250 will be fresh in the west. The formula I'm using right now works perfectly for the individual items and row sums, but not when summed across items or aggregated somehow.

code:

Test:=sumx(relatedtable('Type');[Allocation])*sumx(relatedtable('Region');[Allocation])*sum([Sales])

This can't be that difficult but I just can't come up with the correct set of calculations right now

# ? Jan 10, 2017 18:04

D34THROW: Jan 29, 2012; RETAIL RETAIL LISTEN TO ME BITCH ABOUT RETAIL

Okay, so I've got an Excel sheet that tracks leads for my department through the Dynamics NAV plugin; works like this, all mouse-clicks unless noted.

1) Open Service Lead Reports.xlsx
2) Activate "Data" sheet.
3) Dynamics NAV ribbon button, "Refresh" button on ribbon.
4) Activate "Report" sheet.
5) Run macro "Update Lead Tables" (this takes the hidden pivot tables that parse the data from NavDataRegion and updates them, which in turn updates the visible numbers of leads received, leads sold, and total value of leads sold).

What I'm looking to do, and Google is no help, is automate this task. In pseudocode:

code:

open <Service Lead Reports>

activate <Data Sheet>
[NAVPlugin.RefreshTables <<<<<<<<<<

activate <Report Sheet>
RefreshPivotTables

The indicated line is what I'm specifically having trouble with. I can't find how to interface with a plugin through VBA; the rest I'm well-versed in.

# ? Jan 11, 2017 17:58

mobby_6kl: Aug 9, 2009; by Fluffdaddy

No idea how to work with that plugin unfortunately.

But a quick update in case anyone finds themselves with a similar situation. It was solved with another SUMX which I think had to do with introducing the appropriate row context so that only the appropriate rows were summed up in the related tables for each fruit:

code:

fizz:=SUMX('Base'; sumx(relatedtable('Type');'Type'[Allocation]) * sumx(relatedtable('Region');'Region'[Allocation])* 'Base'[Sales])

# ? Jan 12, 2017 10:27

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 03:05

C-Euro: Mar 20, 2010; Soiled Meat

I have a multi-sheet workbook where one or more cells in Sheet 1 are used to populate a given cell in Sheet 2. However, I sometimes need to sort Sheet 1 in certain ways that then throw off the cell references in Sheet 2. Is there a way to lock in these references such that a certain cell will be always used, even if its position changes? I thought about maybe using VLOOKUP since there are some common ID cells between the two that could be used, but I'm already using a VLOOKUP function (with values from "Sheet 3") to populate the Sheet 1 values that I need to put into Sheet 2, and the ID cells aren't common across all three sheets (so Sheets 1 and 3 have the same IDs in the first column, while Sheets 1 and 2 share IDs in a different column that's not the first). I'll try posting an example later when I'm not on my phone.

# ? Feb 1, 2017 17:24

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

«‹›82 »