|
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 |
|
|
# ? May 24, 2024 16:35 |
|
Xovaan posted:Haha, pretty close to what I'm doing next, actually. 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:
FAN OF NICKELBACK fucked around with this message at 21:28 on Jan 7, 2014 |
# ? Jan 7, 2014 21:02 |
|
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:
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 |
|
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 |
|
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. 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
|
# ? Jan 8, 2014 23:20 |
|
Xovaan posted:I have to do some data scrubbing in Excel ( ) and am looking for the most efficient way to do this. 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 |
|
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) 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 |
|
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. 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 |
|
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 |
|
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. 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.
|
# ? Jan 8, 2014 23:59 |
|
Xovaan posted:Your mailbox is full so: 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 |
|
Veskit posted:We'll call your sheets A (the one you have) and B (the other one with blanks). 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
|
# ? Jan 9, 2014 00:06 |
|
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 |
|
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 |
|
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 |
|
^^ 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. 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. 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. Knot My President! fucked around with this message at 01:41 on Jan 9, 2014 |
# ? Jan 9, 2014 00:24 |
|
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 |
|
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
|
# ? Jan 9, 2014 20:47 |
|
Xovaan posted:
You can sort by cell color if that helps at all.
|
# ? Jan 9, 2014 21:25 |
|
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 |
|
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 |
|
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:
|
# ? Jan 10, 2014 22:15 |
|
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 EDIT: He just emailed me and told me that merchant (eBay, Etsy, Amazon, etc.) will also affect commission rates. Oh god
|
# ? Jan 10, 2014 22:46 |
|
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 |
|
Veskit posted:This is me talking not understand the issue 100% and not being able to see anything. 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 |
|
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 |
|
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 |
|
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 |
|
stuxracer posted:VLOOKUP is one easy answer. 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 |
|
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? http://www.mbaexcel.com/excel/how-to-use-index-match-match/
|
# ? Jan 13, 2014 01:37 |
|
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:
*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?
|
# ? Jan 22, 2014 23:25 |
|
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 |
|
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: 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 |
|
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. 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 |
|
RICHUNCLEPENNYBAGS posted:Have you considered VSTO? 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 |
|
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 |
|
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 |
|
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 |
|
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 |
|
|
# ? May 24, 2024 16:35 |
|
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 |