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 »

Ragingsheep: Nov 7, 2009

Tableau offers a free add-in to do what you want.

http://kb.tableausoftware.com/articles/knowledgebase/addin-reshaping-data-excel

# ? Jan 7, 2014 11:53

Adbot: ADBOT LOVES YOU

# ? May 24, 2024 13:05

FAN OF NICKELBACK: Apr 9, 2002

Xovaan posted:

Haha, pretty close to what I'm doing next, actually.

The workbook I'm pulling charts from is 10 megs and has 40-something tabs. But it seems a bit more fun and structured than copy --> paste as image over and over and over again. Thanks!

I hate the time it takes to make a presentation deck, but have to do it constantly.

I put this together to go through every sheet in a workbook and pull every chart object from every sheet to it's own slide in a new PowerPoint deck. It is old and not the best coding and as it changed over time the comments didn't--but it works!

I don't know if this helps you; I don't work with pivot charts. However, instead of making them interactive, could you create two/three charts pulling from the same data and displaying different things?

Anyway, you'd have more sheets/slides, but a ton less work.

EDIT: Don't forget to include the PowerPoint object library!
In the VBA window: Tools>References>Microsoft PowerPoint x.0 Object Library

In a module:

code:

Sub ChartNpoint()
    Dim AddASlideOKSheesh As Boolean
    Dim PPpowerwheels As PowerPoint.Presentation
    Dim PPowermakesemgo As PowerPoint.Application
    Dim PPowerslide As PowerPoint.Slide
    Dim ZOOM As Worksheet
    Dim Vroom As Excel.ChartObject
    AddASlideOKSheesh = True
    
'open PowerPoint and set it up
        Set PPowermakesemgo = CreateObject("PowerPoint.Application")
        PPowermakesemgo.Visible = True
        Set PPowermakesemgo = New PowerPoint.Application
        PPowermakesemgo.Presentations.Add

'Count the visible worksheets
For Each ZOOM In ActiveWorkbook.Worksheets
I = I + 1
Next
WS_Count = I

'Begin the loop through all your worksheets
   For I = 1 To WS_Count
   If Worksheets(I).Visible = True Then Worksheets(I).Activate
    If ActiveSheet.ChartObjects.Count > 0 Then
  
   
'Grab each of the charts on the current worksheet in the loop
         For Each Vroom In ActiveSheet.ChartObjects
   
'Appends slides to end of presentation and makes last slide active after copying one of the charts
            Vroom.Select
            ActiveChart.ChartArea.Copy
            PPowermakesemgo.ActivePresentation.Slides.Add PPowermakesemgo.ActivePresentation.Slides.Count + 1, ppLayoutBlank
            PPowermakesemgo.ActiveWindow.View.GotoSlide PPowermakesemgo.ActivePresentation.Slides.Count

                      
'Sets current slide to active slide
            Set PPowerslide = PPowermakesemgo.ActivePresentation.Slides(PPowermakesemgo.ActivePresentation.Slides.Count)
            Set PPowerslide = PPowermakesemgo.ActiveWindow.View.Slide
                            
'Paste New AP Range as HTML
     PPowerslide.Shapes.PasteSpecial(ppMetaFilePicture, Link:=msoFalse).Select

'Center pasted object in the slide and size it
                            
                            With PPowermakesemgo.ActiveWindow.Selection.ShapeRange
                               .Left = 150
                               .Top = 50
                               .LockAspectRatio = True
                               .Width = 500
                            End With
        PPowermakesemgo.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
        PPowermakesemgo.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

            If ActiveSheet.Index = Worksheets.Count Then
                 Exit Sub
                 End If
    Next
    End If
Next I

Set PPowerslide = Nothing
Set PPpowerwheels = Nothing
Set PPowermakesemgo = Nothing
Set ZOOM = Nothing
End Sub

FAN OF NICKELBACK fucked around with this message at 21:28 on Jan 7, 2014

# ? Jan 7, 2014 21:02

Knot My President!: Jan 10, 2005

I have to do some data scrubbing in Excel ( :negative:

) and am looking for the most efficient way to do this.

We have a list of clients with lots of our own data, and Company X has a list of clients with a specific piece of data we need. Both are in the same spreadsheet next to one another, like so:

code:

account # | ABC Vet Clinic | (555) 696-4200 | [blank]       | [tons of other info]
[empty]   | ABC Vet Clinic | 5556964200     | Last Archived |

There's about 6500 entries, or ~3250 contacts.

I need whatever exists in "Last Archived" from Company X to be copied to where you see [blank] above. The issue is that there are discrepancies in the clinic names, be it an ampersand or "and", or capitalization of "Of" and "In" versus "of" and "in", if this makes sense. Afterward, I need Company X's row that we got our archive date from removed so we are left with only our rows of clients.

Some of our own data does not have a match like above, meaning either only Company X's contact/clinic exists in this sheet or our own company's contact/clinic exists but doesn't have a pairing with Company X. In both cases, we want to skip these as we want to keep them in our database. Also, we want to make the telephone number appear with parentheses.

Is there a way to do this without making me want to kill myself?

Thanks in advance!

# ? Jan 8, 2014 22:59

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Xovaan posted:

I'll take a look at the rest later but off the top of my head you can text to column your phone numbers, then concatinate the columns into one. I bet there's an easier way but that'll work for that.

Also are the account numbers all identical between the sheets? If that's the case then everything else you can fix with Vlookups.

# ? Jan 8, 2014 23:14

Knot My President!: Jan 10, 2005

Veskit posted:

I'll take a look at the rest later but off the top of my head you can text to column your phone numbers, then concatinate the columns into one. I bet there's an easier way but that'll work for that.

Also are the account numbers all identical between the sheets? If that's the case then everything else you can fix with Vlookups.

Only our data has the account number. The only relevant information Company X has that we do not is Last Archived along with a clinic name and phone number which are partial matches to what we have in our system (with the above discrepancies noted)

Also thanks so much :gonk:

# ? Jan 8, 2014 23:20

EAT THE EGGS RICOLA: May 29, 2008

Xovaan posted:

I have to do some data scrubbing in Excel ( ) and am looking for the most efficient way to do this.

We have a list of clients with lots of our own data, and Company X has a list of clients with a specific piece of data we need. Both are in the same spreadsheet next to one another, like so:
code:
account # | ABC Vet Clinic | (555) 696-4200 | [blank]       | [tons of other info]
[empty]   | ABC Vet Clinic | 5556964200     | Last Archived |
There's about 6500 entries, or ~3250 contacts.

I need whatever exists in "Last Archived" from Company X to be copied to where you see [blank] above. The issue is that there are discrepancies in the clinic names, be it an ampersand or "and", or capitalization of "Of" and "In" versus "of" and "in", if this makes sense. Afterward, I need Company X's row that we got our archive date from removed so we are left with only our rows of clients.

Some of our own data does not have a match like above, meaning either only Company X's contact/clinic exists in this sheet or our own company's contact/clinic exists but doesn't have a pairing with Company X. In both cases, we want to skip these as we want to keep them in our database. Also, we want to make the telephone number appear with parentheses.

Is there a way to do this without making me want to kill myself?

Thanks in advance!

Try using Open Refine? This is kind of a small dataset for it but it's a really awesome tool.

http://openrefine.org

I've given a few tutorials on it if you need something to walk you through it, just pm me or something.

(Open Refine is super awesome for any sort of data cleanup you might want to do)

Also: you want to use masking for the phone number, just store it as 1231231234 and apply a mask to make it display however you want.

EAT THE EGGS RICOLA fucked around with this message at 23:30 on Jan 8, 2014

# ? Jan 8, 2014 23:23

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Xovaan posted:

Only our data has the account number. The only relevant information Company X has that we do not is Last Archived along with a clinic name and phone number which are partial matches to what we have in our system (with the above discrepancies noted)

Also thanks so much

So in that case can't you just add the account number to both sets on different sheets, sort by unique values and then run a VLOOKUP? Am I understanding this correctly?

# ? Jan 8, 2014 23:26

Knot My President!: Jan 10, 2005

Veskit posted:

So in that case can't you just add the account number to both sets on different sheets, sort by unique values and then run a VLOOKUP? Am I understanding this correctly?

If that's the case, then how do I move data to a different sheet? Do I make a duplicate of the sheet, vlookup to transfer account numbers, then vlookup again to copy archive dates?

EAT THE EGGS RICOLA posted:

Try using Open Refine? This is kind of a small dataset for it but it's a really awesome tool.

http://openrefine.org

I've given a few tutorials on it if you need something to walk you through it, just pm me or something.

(Open Refine is super awesome for any sort of data cleanup you might want to do)

Also: you want to use masking for the phone number, just store it as 1231231234 and apply a mask to make it display however you want.

Oh man, I'm gonna take you up on this. I want to/need to know the Excel way first but when I get it sorted I'm gonna do the same thing with this program so I can gauge the differences.

# ? Jan 8, 2014 23:35

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Xovaan posted:

If that's the case, then how do I move data to a different sheet? Do I make a duplicate of the sheet, vlookup to transfer account numbers, then vlookup again to copy archive dates?

We'll call your sheets A (the one you have) and B (the other one with blanks).

You go to sheet b, and do a group by store name by using the filter option. You only show the unique ones, then add the account number from group A to each one respectively, and fill it out so all of the account numbers are filled on sheet b.

THEN you can run a vlookup on sheet A to pull any data from sheet b.

# ? Jan 8, 2014 23:44

Knot My President!: Jan 10, 2005

EAT THE EGGS RICOLA posted:

Try using Open Refine? This is kind of a small dataset for it but it's a really awesome tool.

http://openrefine.org

I've given a few tutorials on it if you need something to walk you through it, just pm me or something.

(Open Refine is super awesome for any sort of data cleanup you might want to do)

Also: you want to use masking for the phone number, just store it as 1231231234 and apply a mask to make it display however you want.

Your mailbox is full so:

I love it! Thanks for the help, dude. I'll be reading up on this tomorrow after my workload dies down. Wish I had this when I was mapping out demographics a few months ago. :negative:

# ? Jan 8, 2014 23:59

EAT THE EGGS RICOLA: May 29, 2008

Xovaan posted:

Your mailbox is full so:

I love it! Thanks for the help, dude. I'll be reading up on this tomorrow after my workload dies down. Wish I had this when I was mapping out demographics a few months ago.

Whoops. Deleted a bunch. It's honestly super good but kind of weirdly limited in what it does, but for data cleanup it's as good as anything I've ever seen.

# ? Jan 9, 2014 00:02

Knot My President!: Jan 10, 2005

Veskit posted:

We'll call your sheets A (the one you have) and B (the other one with blanks).

You go to sheet b, and do a group by store name by using the filter option. You only show the unique ones, then add the account number from group A to each one respectively, and fill it out so all of the account numbers are filled on sheet b.

THEN you can run a vlookup on sheet A to pull any data from sheet b.

I don't quite understand this.

There's only one data sheet, which has the clients from both companies mashed into it. Do I make a clone of this one datasheet then do what you've said? How specifically do I group by store name and filter? Sorry if this is a dumb question :saddowns:

# ? Jan 9, 2014 00:06

EAT THE EGGS RICOLA: May 29, 2008

Xovaan posted:

I don't quite understand this. There's only one data sheet, which has the clients from both companies mashed into it. Do I make a clone of this one datasheet then do what you've said? How specifically do I group by store name and filter? Sorry if this is a dumb question

I'm not sure I'd go about this the same way as Veskit, but you can split the sheets into "your" data and "their" data by sorting it on the account number then just copying all rows with a blank account number to a new sheet.

# ? Jan 9, 2014 00:10

Knot My President!: Jan 10, 2005

WAIT A SECOND why don't I just vlookup by phone number and paste the relevant account number and other useful data?

# ? Jan 9, 2014 00:18

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Xovaan posted:

I don't quite understand this. There's only one data sheet, which has the clients from both companies mashed into it. Do I make a clone of this one datasheet then do what you've said? How specifically do I group by store name and filter? Sorry if this is a dumb question

Ohh I thought there were 2 sheets this whole time. Let me finish this really quick and I'll revisit this.

Xovaan posted:

WAIT A SECOND why don't I just vlookup by phone number and paste the relevant account number and other useful data?

Well unless you answered your own question. Just make sure that the phone numbers are on the left most column, and that they're formatted the same.

I think the hardest part of this thread for me in answering questions is understanding what the spreadsheet looks like without seeing it. That was my bad though for not reading it properly. Shine on you crazy diamond.

Veskit fucked around with this message at 00:25 on Jan 9, 2014

# ? Jan 9, 2014 00:19

Knot My President!: Jan 10, 2005

^^ it's literally this clusterfucked list of contacts from two sources merged into one sheet. One of the merged lists has relevant information that the other doesn't, and the other one has an account number the other doesn't. My head is gonna explode. I don't get paid enough to do this poo poo. :rant:

I guess my question is now changed to "how do I make the phone numbers look the same". When I apply a format of "phone number" it doesn't actually alter the data. :raise:

Also, when this is all said and done, I still need to make sure that I can keep unique entries of each (meaning either only Company X or our company's information exists). When sorting by account number, it's not taking into consideration single entries.

edit: I did it!

1. Sorted by account number
2. Copied account numbered files to new sheet
3. Used =--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")," ",""),"-","") to convert phone numbers to raw numbers
4. Vlookup'd based on phone number to copy over archive dates
5. Did the same on other sheet to copy over account numbers

Now I just have to figure out how to remove duplicates with priority being it removes red before black. :geno:

Knot My President! fucked around with this message at 01:41 on Jan 9, 2014

# ? Jan 9, 2014 00:24

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Use COUNTIF to create a column that indicates how many times that value appears in the data table. Then filter by color and COUNTIF value > 1.

# ? Jan 9, 2014 20:41

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Xovaan posted:

Now I just have to figure out how to remove duplicates with priority being it removes red before black.

Then you can remove highlighted values, OR use the filter option and filter by color and by alpha so that it groups everything.

Still coulda used text to columns for your phone number issue :colbert:

# ? Jan 9, 2014 20:47

Smithersnz: May 10, 2005; We freaked out yesterday. Let's just freak in tonight; Soiled Meat

Xovaan posted:

Now I just have to figure out how to remove duplicates with priority being it removes red before black.

You can sort by cell color if that helps at all.

# ? Jan 9, 2014 21:25

Knot My President!: Jan 10, 2005

Thanks for the replies everyone!

The main issue is that there's discrepancies between each vendor so a data merge wouldn't work: their names for clinics were entirely different than ours in the "name" column and they call our data plan different from ours (gold vs silver vs platinum) and I have no clue if they're the same or not. I ended up separating their data from ours, copying relevant data to each sheet with vlookup, and presenting it to him with this look of disdain and confusion and he understood

# ? Jan 9, 2014 23:35

swagger like us: Oct 27, 2005; Don't mind me. We must protect rapists and misogynists from harm. If they're innocent they must not be named. Surely they'll never harm their sleeping, female patients. Watch me defend this in great detail. I am not a mens rights activist either.

I tried googling this but I'm not really sure what to google. I have an excel spreadsheet that has an empty box for a "scoring" system, so for different categories, I put in 15, 5, 1 or 0, then it adds up the total of all the different points for a given section. So, if the category before it is say for example, "FOOTBALL", then its 15, if its "SOCCER, JOGGING or TENNIS" its 5 etc. Right now I'm entering this in manually but there has to be a way to automate this with a function in excel. How do I tell excel basically "Where n = "FOOTBALL" or "RUGBY" fill in the next box with the score value of 15, where n = "SOCCER" or "JOGGING" or "TENNIS", fill in the value for 5.

# ? Jan 10, 2014 19:49

stuxracer: May 4, 2006

VLOOKUP is one easy answer.

Create a table with the sport to point mapping - say in range D3:E7

football 15
soccer 5
jogging 5
tennis 5
rugby 15

your lookup to put points in column B where you type the sport in column A would be

code:

=VLOOKUP(A3,$D$3:$E$7,2,FALSE)

# ? Jan 10, 2014 22:15

Locke Dunnegan: Apr 25, 2005; Respectable Bespectacled Receptacle

I am relearning Excel for the first time since using '03 back in '06 or so, so this is my first time with '10. I am helping my cousin out who is a freelance asset liquidator, and he has a drat confusing way he splits his commission up. First, it depends on the client. Then some clients have a flat commission rate, so that is easy enough, while others have a break point tiered system such as <$500 is 25% commission, <$5000 is 20%, etc. Now, the best part is that even these clients don't all have the same break points or commission percentages for those points, so I am wracking my brain on how to set up my data reference sheet to easily pull data from a dropdown menu of client names on the main sheets. That isn't even getting to the "how the hell do I logic up the if/then needed to parse an entered dollar value to find the correct commission" thing.

Here are a few example clients if I am not clear enough:

Client A: Flat rate of 20%
Client B: <$500 is 20%, $500-$1000 is 15%, else 10%
Client C: <$1000 is 30%, $1000-$2500 is 25%, $2500-$5000 is 20%, else 15%

Help :psyduck:

EDIT: He just emailed me and told me that merchant (eBay, Etsy, Amazon, etc.) will also affect commission rates. Oh god :psyboom:

# ? Jan 10, 2014 22:46

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Locke Dunnegan posted:

This is me talking not understand the issue 100% and not being able to see anything.

Why not just do if() for each value and then if some are the same copy and paste them? Or come up with the values on a different sheet, lable them and copy/paste accordingly? How many rows we talking?

I guess the first step is to group them up by pay scale, and then write out formulas for each one. Am i correct in these guesses?

# ? Jan 10, 2014 23:54

Locke Dunnegan: Apr 25, 2005; Respectable Bespectacled Receptacle

Veskit posted:

This is me talking not understand the issue 100% and not being able to see anything.

Why not just do if() for each value and then if some are the same copy and paste them? Or come up with the values on a different sheet, lable them and copy/paste accordingly? How many rows we talking?

I guess the first step is to group them up by pay scale, and then write out formulas for each one. Am i correct in these guesses?

Okay, sorry, but I don't understand the middle statements. I do need to write up the basic formulas for the clients, true, but I won't know exactly what that is until I get the merchant info, which he is working on. What I am making is a blank spreadsheet that he can fill in with item information, sale price, and order #, then choose the merchant and client using two drop-down menus. The Client drop-down choice will affect Our Commission %, and the Merchant drop-down will affect that also, as well as the Final Value Fee that most merchants take out on a successful sale. I am slowly piecing together how I will write up our Commission spot, but I don't know how I can look first at a client name, then look at the Price Sold (or I guess Gross Price might make more sense? The amount we get before fees), check if the client has a piecemeal/tiered/whatever-you-call-it system, evaluate the tier, then calculate our commission.

# ? Jan 11, 2014 00:20

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Locke Dunnegan posted:

Okay, sorry, but I don't understand the middle statements. I do need to write up the basic formulas for the clients, true, but I won't know exactly what that is until I get the merchant info, which he is working on. What I am making is a blank spreadsheet that he can fill in with item information, sale price, and order #, then choose the merchant and client using two drop-down menus. The Client drop-down choice will affect Our Commission %, and the Merchant drop-down will affect that also, as well as the Final Value Fee that most merchants take out on a successful sale. I am slowly piecing together how I will write up our Commission spot, but I don't know how I can look first at a client name, then look at the Price Sold (or I guess Gross Price might make more sense? The amount we get before fees), check if the client has a piecemeal/tiered/whatever-you-call-it system, evaluate the tier, then calculate our commission.

So you're going to need to use the data validation validation tool to make your drop downs so you can choose the merchant/client. You can either manually key those or have them refer to another spreadsheet or whatever, that's easy.

THEN, after everything is selected, you can have a column with an if() statement with all the things you want with or. IE

if(or(A1='Bob', A1='joe')Formula for commission, formula for commission)

Which would say if this column is bob or joe, then take this formula, if not take this formula. From what I understand it'll be complicated, but it's doable. Research if(or()) and if(and()) statements and see if that'll help you with your calculations.

# ? Jan 11, 2014 01:35

Locke Dunnegan: Apr 25, 2005; Respectable Bespectacled Receptacle

Thank you! I am starting to get a clearer picture of what to do. What function would I use to check if a certain entered amount is within a tiered set? Just if(or)?

# ? Jan 11, 2014 02:14

Veskit: Mar 2, 2005; I love capitalism!! DM me for the best investing advice!

Locke Dunnegan posted:

Thank you! I am starting to get a clearer picture of what to do. What function would I use to check if a certain entered amount is within a tiered set? Just if(or)?

Generally speaking if you use the word if, it's going ot be an if(), so if(or())

# ? Jan 11, 2014 02:19

swagger like us: Oct 27, 2005; Don't mind me. We must protect rapists and misogynists from harm. If they're innocent they must not be named. Surely they'll never harm their sleeping, female patients. Watch me defend this in great detail. I am not a mens rights activist either.

stuxracer posted:

VLOOKUP is one easy answer.

Create a table with the sport to point mapping - say in range D3:E7

football 15
soccer 5
jogging 5
tennis 5
rugby 15

your lookup to put points in column B where you type the sport in column A would be
code:
=VLOOKUP(A3,$D$3:$E$7,2,FALSE)

The problem I'm finding doing it this way is that VLOOKUP only lets you use the left most column of a table. I have an already made spreadsheet, that along with rows like DATE, PERSON'S NAME, it has the SPORTS row at E3:E46. What do I use then instead of VLOOKUP to automatically put the points?

Just to be clear what I mean. I have a spreadsheet that has columns for DATE, PERSON'S NAME, SPORT, SPORT WEIGHT. SPORT is at E3:E46, and its filled with all the sports names. SPORT WEIGHT is empty, and I want it to automatically be filled in with whatever value its supposed to be, instead of going through it manually. So, I want a formula where it references a table that has the points/weights next to them, and then it just puts it into that box. Sorry if this doesn't make sense, I'm still really new to Excel

# ? Jan 13, 2014 01:15

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

swagger like us posted:

The problem I'm finding doing it this way is that VLOOKUP only lets you use the left most column of a table. I have an already made spreadsheet, that along with rows like DATE, PERSON'S NAME, it has the SPORTS row at E3:E46. What do I use then instead of VLOOKUP to automatically put the points?

Just to be clear what I mean. I have a spreadsheet that has columns for DATE, PERSON'S NAME, SPORT, SPORT WEIGHT. SPORT is at E3:E46, and its filled with all the sports names. SPORT WEIGHT is empty, and I want it to automatically be filled in with whatever value its supposed to be, instead of going through it manually. So, I want a formula where it references a table that has the points/weights next to them, and then it just puts it into that box. Sorry if this doesn't make sense, I'm still really new to Excel

http://www.mbaexcel.com/excel/how-to-use-index-match-match/

# ? Jan 13, 2014 01:37

The MUMPSorceress: Jan 6, 2012; ^SHTPSTS; Gary’s Answer

I need to have Excel connect to a UNIX server, execute several commands, and then dump the output into a cell. The server is reachable only via SSH. Some background:

There's a certain task that several folks at my company have to do on a regular basis that, at present, requires them to look in one database to find a list of "widgets" that have changed, then look in another database to get detailed reference about those widgets. They then have to type those details into a third place because the GUI for the second database is poorly coded and you can't copy/paste from it. This process can often involve hundreds of widgets and take 2-3 hours to do. It's annoying.

I've managed to automate this somewhat. The first database has much of it's info extracted to an analytical reporting server running some flavor of SQL. The second database runs on an obscure database platform on UNIX. I've written a VBA function in a spreadsheet that can automatically get the list of widgets that have changed and dump a special query string into a cell. I've also written a function on the second database server that can take that string and find all the details we have to type up, printing them to the terminal in the needed format. They can then copy the text from the terminal window and paste it into the final destination.

This already cuts the 3 hour process down to 10 minutes or so. BUT IT CAN BE BETTER! If VBA supports SSH connections, I could add another button to my spreadsheet to log into the second database server, run the lookup function, and then paste the beautifully formatted results into the spreadsheet. This would make the process take 30 seconds to 1 minute. Googling around just finds a lot of forum posts in various places with lots of responses saying "I don't know how to use UNIX, but I do know how to launch the Windows CMD window from VBA".

I am hoping you guys know more than the internet. To TL;DR that, I need to:

Possible, or pipe dream?

# ? Jan 22, 2014 23:25

Tricerapowerbottom: Jun 16, 2008; WILL MY PONY RECOGNIZE MY VOICE IN HELL

Any good way to get a pivot table to order a column in a preexisting, specified order, without manually c/p'ing everything around? I'm trying to get a phylogenetic listing to go in phylogenetic order, versus the alphabetic order I got when I made the table... I could chop and drop around, but there's 131 genera, so I was hoping I could do it an easier way.

Screenshot to illustrate, alphabetic on the left, phylogenetic in the bordered cells on the right:

# ? Jan 23, 2014 00:19

RICHUNCLEPENNYBAGS: Dec 21, 2010

LeftistMuslimObama posted:

I need to have Excel connect to a UNIX server, execute several commands, and then dump the output into a cell. The server is reachable only via SSH. Some background:

There's a certain task that several folks at my company have to do on a regular basis that, at present, requires them to look in one database to find a list of "widgets" that have changed, then look in another database to get detailed reference about those widgets. They then have to type those details into a third place because the GUI for the second database is poorly coded and you can't copy/paste from it. This process can often involve hundreds of widgets and take 2-3 hours to do. It's annoying.

I've managed to automate this somewhat. The first database has much of it's info extracted to an analytical reporting server running some flavor of SQL. The second database runs on an obscure database platform on UNIX. I've written a VBA function in a spreadsheet that can automatically get the list of widgets that have changed and dump a special query string into a cell. I've also written a function on the second database server that can take that string and find all the details we have to type up, printing them to the terminal in the needed format. They can then copy the text from the terminal window and paste it into the final destination.

This already cuts the 3 hour process down to 10 minutes or so. BUT IT CAN BE BETTER! If VBA supports SSH connections, I could add another button to my spreadsheet to log into the second database server, run the lookup function, and then paste the beautifully formatted results into the spreadsheet. This would make the process take 30 seconds to 1 minute. Googling around just finds a lot of forum posts in various places with lots of responses saying "I don't know how to use UNIX, but I do know how to launch the Windows CMD window from VBA".

I am hoping you guys know more than the internet. To TL;DR that, I need to:

*Connect to a UNIX server via SSH from a VBA macro.
*Issue several commands to the server to log into the database and launch my utility.
*Copy a string from a cell in the spreadsheet and enter it at the server utility's prompt.
*Paste all output in the terminal after that point into a cell int he spreadsheet.
*Issue a few more commands to the server to log out of the database, terminal session, etc.

Possible, or pipe dream?

Have you considered VSTO?

Alternatively, if you can launch cmd surely you can use that to launch cygwin and then run some commands. Or you could open up a service on the Linux server that will launch your task and return the kind of file you need.

# ? Jan 23, 2014 00:45

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

Tricerapowerbottom posted:

Any good way to get a pivot table to order a column in a preexisting, specified order, without manually c/p'ing everything around? I'm trying to get a phylogenetic listing to go in phylogenetic order, versus the alphabetic order I got when I made the table... I could chop and drop around, but there's 131 genera, so I was hoping I could do it an easier way.

Screenshot to illustrate, alphabetic on the left, phylogenetic in the bordered cells on the right:

Add a column to the source data using the match() function to return the place in the list. Then make that one of your pivot columns and sort on it. You will have a number before each phylogenetic listing.

# ? Jan 23, 2014 00:57

The MUMPSorceress: Jan 6, 2012; ^SHTPSTS; Gary’s Answer

RICHUNCLEPENNYBAGS posted:

Have you considered VSTO?

Alternatively, if you can launch cmd surely you can use that to launch cygwin and then run some commands. Or you could open up a service on the Linux server that will launch your task and return the kind of file you need.

The thing is it has to be used by a bunch of technical writers who probably won't have any external terminal emulator that is compatible with cmd and won't know how to download one.

# ? Jan 23, 2014 03:19

RICHUNCLEPENNYBAGS: Dec 21, 2010

LeftistMuslimObama posted:

The thing is it has to be used by a bunch of technical writers who probably won't have any external terminal emulator that is compatible with cmd and won't know how to download one.

Maybe look into the VSTO route, then.

# ? Jan 23, 2014 04:13

The MUMPSorceress: Jan 6, 2012; ^SHTPSTS; Gary’s Answer

RICHUNCLEPENNYBAGS posted:

Maybe look into the VSTO route, then.

Looks promising. Just to make sure my googling is correct, there's no extra cost/licensing to use VSTO if my company already has a site license for Visual Studio?

# ? Jan 23, 2014 06:29

RICHUNCLEPENNYBAGS: Dec 21, 2010

LeftistMuslimObama posted:

Looks promising. Just to make sure my googling is correct, there's no extra cost/licensing to use VSTO if my company already has a site license for Visual Studio?

Yes, I believe you're right. You can get the package from Microsoft's Web site. Finding good documentation and examples can be a bit of a challenge, though.

# ? Jan 23, 2014 06:31

No Safe Word: Feb 26, 2005

LeftistMuslimObama posted:

Looks promising. Just to make sure my googling is correct, there's no extra cost/licensing to use VSTO if my company already has a site license for Visual Studio?

Having written a few VSTO things, no there's not. And while VSTO is pretty neat and powerful man oh man getting into Office interop is a hairy stinky beast sometimes.

# ? Jan 23, 2014 16:21

Adbot: ADBOT LOVES YOU

# ? May 24, 2024 13:05

The MUMPSorceress: Jan 6, 2012; ^SHTPSTS; Gary’s Answer

No Safe Word posted:

Having written a few VSTO things, no there's not. And while VSTO is pretty neat and powerful man oh man getting into Office interop is a hairy stinky beast sometimes.

Yeah, some more research is showing me that the implementation would be a bit complex and our IT folks are pushing back on letting me have a VS license, so I may just tell the tech writers they have to deal with it as far as working in two programs. 10 minutes is still better than 2 hours, right

# ? Jan 23, 2014 16:41

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

«‹›82 »