What are /r/excel's favorite Products & Services?

From 3.5 billion Reddit comments

Well, I'm not sure how many people will be impressed, but I learned them by working. You have to make a conscious effort to be aware of them and use them.

http://www.asap-utilities.com/excel-tips-shortcuts.php

I think you'll find that

For each person in America call SendEmail( person, SpamMessage) next

Is more efficient. And let's face it, when you're doing something 325,200,891+ times, it's worth doing well.

Type in a few (20?) contacts now, see how long it takes to get an estimate of entries/hour. Divide that into 20,000 and multiply by how much you think you can get per hour.

Like if it takes 20 seconds per entry, it will take 111 hours. Min wage is $7.25, figure $10 is good. $1,000.

Or just put bids out on Mechanical Turk, for some low price, find some other dude to do it for you, charge $750 and keep the profit.

So this appears to be possible using VBA. The following code launches Internet Explorer and then scours the html source code for all airports and assigns each one and its price (if any) to its own cell down columns A and B.

Sub ScrapeKayak()

Dim objShell As Object, Wnd As Object Set objShell = CreateObject("Shell.Application")

ieopen = True For Each Wnd In objShell.Windows If Right(Wnd.Name, 17) = "Internet Explorer" Then Set ie = Wnd ieopen = False Exit For End If Next Wnd If ieopen Then Set ie = CreateObject("InternetExplorer.Application")

ie.Visible = True p = "https://www.kayak.com/explore/CHI" ie.navigate (p) Do DoEvents Loop Until ie.ReadyState = 4

x = 1 For Each divelement In ie.Document.getElementsbytagname("div") With divelement If .ClassName = "priceMarkerWrapper withPoint " Then y = InStr(.innertext, ")") If y > 0 Then Cells(x, 1) = Left(.innertext, y) z = InStr(.innertext, "$") If z > 0 Then Cells(x, 2) = Mid(.innertext, z, 99) End If x = x + 1 End If End If End With Next divelement

Set ie = Nothing End Sub

File looks like this. Can you take it from here ... ?

~~I don't know a way to make an excel macro to fill a web form it doesn't mean that it's not possible but I never heard of that.~~

EDIT: (two days later)

Now I heard of a way to fill a web form so my first suggestion before PLAN A would be to watch the video linked in another comment by u/Youbestnotmisss and try it.

https://www.reddit.com/r/excel/comments/9tygzt/is_it_possible_to_do_the_opposite_of_web_scraping/e902ku6/

PLAN A

That website will save the information into a database.

With the appropriate rights you should be able to INSERT the information directly into the database. SQL Insert.

Ask if that's posible, if there is a way to access directly to the database and if you can get rights to INSERT data.

That's the easiest way that I can think of but I'm afraid that for security reasons the answer is going to be no. Probably you wouldn't be allow to insert anything VIA SQL in the database.

Ask as well if the system have a way to upload bulk data in csv format.

PLAN B.

You can try to use a program like. AUTOIT it's free works on Windows and you can use it to fill forms that follow always the same pattern. You need to learn the sintaxis and if fails it could be a disaster. If you use this I will recommend to use the program to fill the form and leave you (the human) to press the button once you confirm that the data is correct. If you become more a more confident after a few times maybe you can make the script to press the button itself.

From the 'Save as' dialog, choose 'Excel Workbook (*.xlsx)' as the format.

You're hitting the max row / column limit of an obsolete file format.

If you want a version of xlsx that loads large workbooks more quickly, try 'Excel Binary Workbook (*.xlsb)'.

More info: http://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx

I deal with csv files a lot and the way Excel handles them by default drives me crazy. It turns long numbers like FedEx tracking numbers into scientific format and drops zeros from the front of numbers that should be treated as text. I've tried a few ways around it and ended up using the PowerShell script on this page: http://superuser.com/questions/307496/how-can-i-set-excel-to-always-import-all-columns-of-csv-files-as-text/527894#527894

It opens a little slow and always comes up in a new instance but it's nice to be able to just double click the file in explorer and have it open in the right format.

here are the mind blowers: package:dplyr, package:tidyr, package:sqldf, package:ggplot2 package:rvest (with selector gadget) package:data.table

here is a quick primer on why dplyr and tidyr will make you WAY more efficient if you get data from clients or customers in weird formats:

https://www.rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf

some of this CAN be done in excel it's just that writing code is a little easier in R (imho) and you can get much better data visualizations

if you have any questions on how to do something specific in R that you have been doing in excel I would be happy to help, and of course there is always /r/rstats

It's unprofessional to present that much data in one view regardless of how you sex it up. Humans can only store 7 +/- 2 things in working memory, so that's the most independent data points you should provide. If you're presenting data points that form a trend, you can present more individual points but less than 7 sets of points. The Visual Display of Quantitative Information presents a bunch of other principles.

You should first figure out the smallest sets of data you can present individually and only group them if they're incredibly simple.

Have you looked at LibreOffice? It is a full suite of programs, just like Microsoft Office is. It is totally free and open source.

Your professor might allow you to use it.

https://www.libreoffice.org/

Hello, thanks alot it worked.

You've also just unlocked the doors the underground excel network. I feel like I'm in the matrix.

Also,is there a way to do this on the online shared google spreadheet versions of excel? https://www.google.com/sheets/about/

Private Sub Worksheet_Change(ByVal Target As Range) 'Does the validation range still have validation? If HasValidation(Range("DataValidationRange")) Then Exit Sub Else Application.Undo MsgBox "Error: You cannot paste data into these cells." & _ "Please use the drop-down to enter data instead.", vbCritical End If End Sub

Private Function HasValidation(r) As Boolean 'Returns True if every cell in Range r uses Data Validation On Error Resume Next x = r.Validation.Type If Err.Number = 0 Then HasValidation = True Else HasValidation = False End Function

http://superuser.com/questions/870926/restrict-paste-into-dropdown-cells-in-excel

Just make the named range to include all cells that have validation in them and copy that to your sheet.

If your calendar is laid out from E1 to NF1 then you need to display the number selected for that day and THEN use Conditional Formatting to highlight specific dates.

Conditional Formatting formula with all numbers highlighted, you will need 3 conditions:-

https://pixeldrain.com/u/ghRcNsGq

The order is critical with these simple formulas and you should put the Green first, then Yellow ...

So here's an examples of what I mean.

• https://expirebox.com/download/b6b8ab443c1e43976ba7721cffb3ef01.html

• I do it with 3 tables, but adding another in would be simple.

• Merely adding the tables into the append (with the Custom column tagging the table number) in the order Table3, T2, T1 is sufficient to put you in a position to then remove duplicates from your key column.

• the newest (highest Table number) are at the top so they remain.

• sort on key

Done.

If the PDFs are scanned, make sure they're as high quality as possible. Then, you can use a PDF to Excel converter. There are a number of both free and paid options. I've used smallpdf before and I was fairly pleased with the results. You will still have to go back and verify the integrity of the conversion, though.

Writing specs as development proceeds? Have you read Fred Brooks's The Mythical Man-Month?

My own $0.02.

1) Document data flow. You don't need flow charts, but some idea of where all data and parameters come from and how they're used, that is, what they produce rather than how the results are produced.

2) List all business rules. Such things as cities and/or counties in each state with surcharges over and above sales taxes. Document how any cash dataflows would be treated as accounting data flows.

3) Create a mathematical model of the calculations, starting with items from the data flow, and here is where you show how data and parameters become results.

4) Ask users what order they want to enter data, and who'd be responsible for parameters loaded into the model. This is mostly about achieving absolute, no quibbling, strict uniformity in data and parameter layouts. [Tangent: a big project I was on 3 years ago had one team convinced tables with state as the key (1st column) should be sorted by full state name, and another team convinced such tables should be sorted by 2-letter state abbreviations. Either would be OK (like choosing which side of the road cars drive on), but confusion wasted many man-months.] Make it very clear to all THERE ARE NO EXCEPTIONS.

Anyway, VBA should be a small part of the whole. If not, you have bigger problems than documentation.

Here's a sample file I created a couple of days back for someone in need of something similar. You can use the same formula in your spreadsheet. Let me know if you need any help with it.

These are both excellent color-pickers that allows you to situationally pick colors that pair well with each-other.

https://color.adobe.com/create/color-wheel

http://medialab.github.io/iwanthue/

Now I looked a bit more into this and I found a solution...

look at this array formula:

={MATCH(MIN(SQRT(ABS(B2-Towers!$B$1:$B$3)^2+ABS(Hits!C2-Towers!$C$1:$C$3)^2));SQRT(ABS(B2-Towers!$B$1:$B$3)^2+ABS(Hits!C2-Towers!$C$1:$C$3)^2);0)}

This gives you the row of the tower with the lowest linear distance from your hit from the list in Towers!C1:C3 which is where I put your list of towers from the example in the MrExcel thread.

If you then use

=Index(Towers!$A$1:$A$3;'Wherever the other formula is')

you get your tower name :-)

I'll just upload the workbook I worked this out in to google drive so you can look at it for yourself. Also, please check my distance calculation... I did this really quick and dirty using the numbers you provided as cartesian coordinates and just calculating the length of the resulting hypothenuse. Should be ok though if the distances don't get large enough for the curvature of the earth to become a problem.

https://docs.google.com/open?id=0B0z1rcQdKRf5cEZWNmVHYnlxbmM

Edit: I checked the difference in distance between my calculation and the "correct" calculation using the formula you quoted in the MrExcel thread. For a distance of 1° in both directions at the equator the difference is ~35m. I don't know if this is close enough for you...

I’m not sure how you would do this with only Excel, but for my work I do quite a bit of grabbing data from an excel sheet and inputting it to a website, or the other way around, grabbing data off a website and inputting it into Excel, but I use AutoHotKey for 100% of the process.

Here are some great guides that I used to set up my scripts:

Controlling chrome and web scraping data with Selenium: http://the-automator.com/cross-browser-web-scraping-with-autohotkey-and-selenium/

Grabbing data from or inputting data into an exel worksheet: https://autohotkey.com/board/topic/69033-basic-ahk-l-com-tutorial-for-excel/

http://download.cnet.com/Excel-Tool-VBA-Password-Recovery/3000-2077_4-75206791.html

Despite the fact it's coming from CNET, it's a legitimate file. This is not an executable, but an .xlsm file that your run concurrently with the file you're trying to view the code.

Unless your predecessor used a hex editor or some other commercial software product to lock the file, this should work.

I know this is an Excel request, however I've been in your situation as well and I'd suggest you take a look at Wave - https://www.waveapps.com/

I work in accounting but I've set up non-finance/accounting people with Wave and had good success.

Free, online, their revenue model is based on charging for payment processing and payroll if it is ever needed.

I've been using it for my own personal small business, and

Make a recipe good for 1 pc of candy then link the amount per ingredient to let's say, cell A1 (this cell contains the number, 1). So when you change the number on cell A1 the amount in your recipe would also change.

Here's a sample workbook:

https://files.fm/u/7xxnstge

I don't think you can do it using only formulas but you can do your own formula.

Add a module to the workbook with this code and you will have a formula called MERGEVALUES that does that. It will use 2 parameters: the range of cells and the delimiters. Example: MERGEVALUES(A1:A10, ",")

Hope this helps.

EDIT: I realize the code is not the most efficient but I think at least it gets you started.

Make a table

=randbetween(100,300)

=RANDBETWEEN(DATE(2017,1,1), TODAY())

="Person" & RANDBETWEEN(100,300)

etc etc

Here's an example I made for someone yesterday on here:

https://expirebox.com/download/e5b229dfd75dc9b5b2d449aca016ac3f.html

It's not a table, fwiw

Why are you doing this, what's wrong with displaying the data in a pivot table?

1. Don't reference the pivot table for your fixed values (month names and years) , fix them up front and then they're not going to be affected by the pivot table moving.
2. Remove the subtotal and/or do 1
3. You need to refer to a fixed 2019 cell and not a variable one.

Fundamentally

• you shouldn't try run a set of discrete cells which you don't expect to fuck up off of a Pivot table you are planing on filtering.
• Use multiple pivot tables for different purposes (copy paste them and they inherit slicers too)
• Use slicers to filter, filtering on the columns is old hat.

Here, I made you an example

• https://expirebox.com/download/a20781559532c4be2e4d6a1ad785c187.html

• Entity slicer only filters the first PT

• YEAR slicer acts on both PTs

• I made you an example of a fixed years and months matrix referencing the first PT in H2:J5 - It's unclear what you were trying to achieve, but it works :-)

That's a great question! Let me give you a bit of background on these posts...

I started this out making "lessons" for users who had a moderate understanding of VBA. I'm now redirecting this series to capture a more beginner crowd(thanks to some input of our mod team). I've yet to make a post for those who are just getting their toes wet. Writing Clean Code is a great place to start, but if you're brand new, I haven't yet covered the very basics of starting VBA.

I want to know what the community wants to learn; I'm beginning to realize that a ground up approach might be the best way to move forward. We'll see in the feedback.

I've been part of this community for over two years now and I have great passion for helping others learn, especially VBA. Please let me know what you'd like to learn and what you'd like to get out of being a part of this community.

Thanks for the comment!

And I'm sure there are still some of those spreadsheets doing business critical functions out there somewhere in an institutional basement!

I thought about doing a little write-up but life got in the way, basically looking at how people used to get help for excel though bulletin boards and what not.

A lot of the old data can still be easily accessed,

https://groups.google.com/forum/m/#!forum/microsoft.public.excel.misc

For example here, 260000 posts going back to 1996 (and some recent ones too:. I'm on mobile right now so I can't check, but it's fun if you narrow down the search to the earliest posts. A lot of the questions were very similar to the questions we see every day today ::) vlookup saving the world for at least 20 years :p

If anyone who's used excel for a long time have some anecdotes that'd be fun too!

='-'!C36 corresponds to cell C36 on sheet with name '-' (dash).
It shows 0 because it's formatted as a number and in Excel terms nothing == zero.

Also check http://superuser.com/questions/515932/display-blank-when-referencing-blank-cell-in-excel-2010
This will work to show blank as blank:

='-'!C36 & ""

I'd imagine this can help: http://www.makeuseof.com/tag/send-emails-excel-vba/

Regarding the loop, I'd just do something like this...

Dim NumRows As Integer Dim i As Integer Dim myRange As Range

Set myRange = Range("A:A")

NumRows = Application.WorksheetFunction.CountA(myRange)

For i = 1 To NumRows If Cells(i, 4).Value = "" Then [Code for sending email to Cells(i, 1)] Else End If Next i

I would consider that if you used the unique ID as a reference, to highlight duplicates with a COUNTIF formula and Conditional Formatting.

https://pixeldrain.com/u/SFZ16Y7a

That sounds pretty straight forward to do in Excel. They are a million and one different ways of doing it though depending on how simple/complex you want it to be.

You could just have a column A to enter a product code, Column B for number of units and then Column C has a VLOOKUP against your prices table. Then Column D is your total price. Simply sum Column D

Or you could go all-out and design something in VBA. It entirely depends on how much effort you want to put into it

I mocked something up for you that should get you started http://www.filedropper.com/register

Yeah, I don't find anything relevant by googling that too. Here below is my link to a color palette generator. Set it to 4 colors (tetrad), tweak it with the disk at the left so you get the 3 street light colors that you want and the 4th color is whatever. Then click at each color that you choose on the palette at the right, it give you the RGB[0-255] then you can enter that in excel by cliquing : color fill > other colors > custom.

http://paletton.com/#uid=75C1l0kBUpRzNOVHGAi-YjPEccg

And the font. Don't mix fonts. Use just one font. The only variation that you need is using italic, bold and having larger fonts at some places.

If you do this, don't forget to export your customizations, because when you/someone else upgrades your OS or hardware or whatever, you're going to lose all of these sweet settings changes you have been depending on.

"I want to learn a skill that should take me years to master, but I would like to do it in a week".

So how would you do that?

• Fake it till you make it - it's like learning a language, only very few people can master a new language in a week (and let me assume that you & I can't). However, you can get a grasp of some basics and bluff&build from there.
• Start by e.g. a course, udemy.com and coursera.org have quite a couple of (free) self-study courses
• Alternatively, try any of the recommended youtube channels or buy a book, whatever method fits you best.
• Finally: practice (on the job) - hours, days, weeks, months, years... I've been using Excel for 20+ years now and still learning new tricks.

Here's the file:

https://files.fm/u/ny2ugp6u

Put this in cell B4 of the dashboard:

=IFERROR(INDEX(Deviation!$C:$C,MATCH(CONCATENATE($A4,B$1),Deviation!$D:$D,0)),VLOOKUP(B$2,Employees!$A$4:$B$10,2,))

and this on column D of the deviation sheet:

=CONCATENATE(Tabel20[@Employe],Tabel20[@[Date:]])

The second formula is just a "helper column" to make the lookup formula easier and simpler. You can just hide the column if you want but don't forget to copy down the formula to the rest of the rows.

I made it for you without any of that :-)

https://expirebox.com/download/754bfbab1b2482a09da7bea658669763.html

• made a function based on the single line of code you had for the batch call.
• made a single parameter (so I could substitute it in )
• swapped the list of 100 tickers for my pTicker parameter.
• used THAT query to create a custom function
• created a single column parameter table and made a query over it (qrySRC)
• Add column -> Invoke custom function (my new function)
• Then expanded resulting column
• replaced all instances of your "src" query with my "qrySRC"

There's probably a more efficient way to do this - but it certainly works.

Not if you want to use the data. You will scan it and use OCR to convert the image to Text.

Then bring the text into Excel.

Here are links to a couple of OCR applications (I have used neither)

https://www.abbyy.com/en-ca/finereader/what-is-ocr/

http://www.paperfile.net/

Once converted to text, you will still have some cleanup to do, but it may be faster than manually typing everything.

Realistically, Access and SQL both sound a bit heavyweight, especially if you're not used to setting up databases.

You may want to check out Airtable. It has the look and feel of a spreadsheet (and it's free), but works great as a CRM. Here are two Airtable templates that might be useful to you:

• https://airtable.com/templates/sales/salesLeads
• https://airtable.com/templates/featured/personalCrm

Hi Officeexcelquestion!

This is cool! There is a way (probably a lot of ways) to do what you're asking. I would say go with shapes and manipulate their fill color based on the cell value.

Here is the example

There are four "seats" which are just shapes that can be dragged around (over a map for instance). If you change the letter under the "Team" column it will change the color. This example only accepts "A","B","C" and "D" as teams. It should be a good starting point for you.

Here is the code for reference:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range) Dim seatNumber As Long Dim teamLetter As String On Error GoTo err

teamLetter = Target.Value seatNumber = Target.Offset(0, -1).Value

Select Case Target.Value Case "A" ActiveSheet.Shapes(seatNumber).Fill.ForeColor.RGB = RGB(247, 91, 91) 'Red Case "B" ActiveSheet.Shapes(seatNumber).Fill.ForeColor.RGB = RGB(255, 204, 0) 'Yellow Case "C" ActiveSheet.Shapes(seatNumber).Fill.ForeColor.RGB = RGB(43, 170, 26) 'Green Case "D" ActiveSheet.Shapes(seatNumber).Fill.ForeColor.RGB = RGB(91, 91, 247) 'Purple End Select

err: End Sub

Paste the following code in the sheet in which you have the PivotTable. For understanding you can take a look at this sample file I created.

Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

Set rng = Me.Range("A1") If Not Application.Intersect(Target, rng) Is Nothing Then With Me.PivotTables("PivotTable2").PivotFields("Month") .ClearAllFilters If Len(rng.Value) > 0 Then .PivotFilters.Add Type:=xlCaptionEquals, Value1:=rng.Value End With

End If

End Sub

Since you didn't show the column numbers, this might or might not work for you exactly. Paste this formula in a cell and hit CTRL+SHIFT+ENTER, and not just ENTER because this is an array formula.

=IF(MIN(IF($B$2:$M$2>=P1,$B$1:$M$1))=0,"",MIN(IF($B$2:$M$2>=P1,$B$1:$M$1)))

Here's a sample file you could take a look at to understand and implement.

> How does one Maximise excel formulas? sorry if this is a dumb question.

It's a good question.

https://stackoverflow.com/questions/tagged/excel-formula

For the love of god, learn Power Query. Then move on to PowerPivot. Which will make you a 90% master at Power BI.

With a customer, you want to alway instill the idea that you are helping them solve problems not create more. Don't do anything that would require any installs on their end or any confusion. You have to be making their jobs easier not harder. The only thing almost guaranteed to work straight out of the box would be either a video or a Powerpoint Presentation. If you aren't there to give the presentation, go with a video.

OBS Studio makes a free tool for broadcasting podcasts and creating videos. The learning curve is very low. I was able to make this video on my first attempt.

Find someone at your office with a confident conversational voice and have them narrate as you demonstrate.

Yeah, just select the cells you want to format. Right click and select Format Cells. Go into "Custom" and put this in the input:

0;-0;

Source: http://superuser.com/questions/538211/how-can-i-make-a-cell-thats-a-number-format-not-show-a-zero

If you prep your data, it's possible. Your source data looks fine for presentation but is a mess for analysis. So maybe make a copy of that tab/sheet if you want to keep it how it is, and use that copied sheet for your analysis.

So first remove all the blank columns in that second sheet, then cut and paste until everything is in one column. So say all of your plants are in Column A. In B1, type =SUBSTITUTE(A1,CHAR(10),","), and copy down. Then highlight B, hit Text to columns, and this splits up the common name from the scientific name.

Now you can do a VLOOKUP, like VLOOKUP(B3,'[file2.xlsx]Sheet2'!B1:C1000,2) and it will find the common name and then report back the scientific name.

edit: And I left out the automatic way to get all that data in two columns as I figured you only had to copy and paste a few times. But if you had more columns, and since /u/shoE_ asked about a non-VBA solution, you can do this first and then do the rest of the steps in my comment.

Number of Quotes per country:

Create a helper column (new data to the right in column K) with this formula: =IF(COUNTIF($A$2:A2,A2)>1,0,1). This will establish a column with a bunch of 1s and 0s. 1s will be your population of unique Quote numbers. (I've assumed that "Reference" in column A is your Quote Number Field, but if it isn't, adjust the formula to the right column above. The absolute reference ($) in the formula are critical to the functionality of this formula.

Then, I would copy the country column to another worksheet (Worksheet2, Column A) and DATA>REMOVE DUPLICATES**. This will leave you with your population of unique countries. (*Assumes Excel 2007, 2010)

Then, use formula =COUNTIFS(Worksheet1!$D$2:$D$100,A2,Worksheet1!$K$2:$K$100,1) in column B of the new worksheet. This says for the column Country (Column D), count anything that matches the country in A2 but only count it when the helper column, K, also equals 1. Assuming a country per quote, when you copy this formula down, it will count any unique quote numbers by country.

I can help with the rest too, but figured I'd get you started.

Edit: Annnnnnnnnnnnddddd here's a simplified google doc demonstrating those techniques. The countifs formula isn't valid in google docs and I won't change the formula to work in there unless someone asks nicely, but it does work in excel

Great question! I think it is not possible without some trickery.

One approach is making right-aligned white data bars for the obverse of your data, and then changing the cell background colours conditionally.

So make a column with 1 minus your value, add a data bar rule with white data bars, white/no borders, direction Right-To-Left, Show data bar only. Then add a colour scale rule with your colours but you need to invert your thresholds too - so 0 is Red, 0.11 is Yellow, 0.35 is Green (I inferred you wanted a gradient - if not then you need three separate rules as given by u/ID001452).

The only problem is that the data bars do not cover the whole height of the cell, so you end up with some rather ugly-looking trailing lines. You can cover these up by adding the thickest possible border in black to each cell, or try to roll with it. I added a thick border in white and got quite a pleasing outcome where each bar appears to be in a little frame of the same colour.

Here are some pictures

Exactly how you send the emails depends on your configuration. Do you use gmail? Exchange? Your ISP's SMTP server? This may be a bit much if you consider your Excel skills fairly basic, but:

• http://www.makeuseof.com/tag/send-emails-excel-vba/

Alternatively you could just produce a list of emails and then once a week manually copy and paste the list into the "To:" box.

http://www.makeuseof.com/tag/use-windows-batch-file-commands-automate-repetitive-tasks/

It absolutely is programming. but then, so is VBA. But batch files are remarkably simple. VBA is a bit tougher. But everything you want to do has been done, and it's just a question of researching it. You don't need to get a programming degree.

I don't think the cloud version of excel will do it. I think you need it installed on your computer.

I don't know from PowerBI.

So, I gave it a shot through power query.

There is a table for

Equity split, Purchases, and Repayments.

It grabs all the data, works out each persons' portion based on the equity, then deducts any repayments made.

You will have to take care ensuring the purchase item and person are correct in the repayment table (there will be a negative in the summary table).

There is a pivot table that summarises all the above data.

It's really important that you don't rename columns in the table. It'll probably break the query.

Let me know how you go...

https://pixeldrain.com/u/YYkPtJ1X

I'm on 365, what version are you on?

You could even consider e for an exponential, rather than linear, decay. Because the difference between game 1 and game 5 is probably not the same between game 5 and 10.

I would dump the lists into excel, then put a rank between 1 and 25 next to it and use =EXP((25-C5)/10) where C5 is the rank.

Then you just need to create a unique list of all the games and use SUMIF to tally the score

Edit: I just whipped this up in 5 minutes in excel, it should give you most of the functionality that you need - https://www.dropbox.com/s/v8j680laqf7xtzw/GameRankExample.xlsx

here is a file that I believe is what you're looking for.

Select 1,2, or 3 from the drop-down box in A1 and the name in B1 will change as well as the graphic. You create a object and then associate it with a name (=PictureName). Check out the Name Manager to see how I have associated the graphics with the object. The graphics are located on the second tab.

This was just something I whipped up, but you could also have the graphics in column B and the numbers (1,2,3) in column A and use a VLOOKUP in the Name Manager function as opposed to looking specifically for 1,2, and 3.

Hopefully these are enough clues to get you what you wanted...

What about using Data Validation for the Directions using the values to validate the list and then from that use vlookup to get the corresponding values from that row?

Like this

I just yanked the entire page and did cleanup at the end to get the bits I cared about. Sloppy, but worked.

Sub Macro1() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://finance.yahoo.com/gainers?e=us", Destination:=Range("Sheet1!$A$1" _ )) .Name = "gainers?e=us" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With

Columns("A:A").Select Selection.Find(What:="Symbol").Activate

Rows("1:" & ActiveCell.Row - 1).Select Selection.Delete Shift:=xlUp

Columns("A:A").Select Selection.Find(What:="Headlines").Activate

Rows(ActiveCell.Row - 1 & ":" & Cells.Rows.Count).Select Selection.Delete Shift:=xlUp

ActiveSheet.Range("A1").Select Cells.EntireColumn.AutoFit ActiveWorkbook.Connections("Connection").Delete

End Sub

Try https://cronometer.com/. A great calculator of macros and micros that also allows you to enter custom recipes, track your biometrics and exercise, and more. I am not affiliated, just a satisfied customer who tracks what I eat.

http://superuser.com/questions/184276/how-to-open-excel-without-refreshing-data
In Excel 2007, you should do the following:

In Excel Options, navigate to Trust Center > Trust Center Settings > External Content. You'll see the Security Settings for Data Connections and Workbook Links. Disable both.

Additionally, navigate to Formulas tab, click Manual under Calculation options, and then click OK. Restart Excel and you're done.

In Office 2010, go to the same External Content menu, and select Disable all Data Connections as well as Disable automatic update of workbook links.

EDIT: It occurred to me that you may wish to total only the times outside discretionary times - to show the "In Discr.", the "Out Discr." and the "Total Discr." Hope this helps.

https://pixeldrain.com/u/1MCwD9YF

Alternatively here is a copy of the template from the site you've referred to, but with all the workbook, worksheet, and VBA passwords removed. Go nuts!

This was fun.

I did this using Array formulas (the {} brackets you see) and must be entered using Ctrl+Shift+Enter.

http://www.filedropper.com/copyofsamplexcel

*EDIT: I should elaborate. A new person can be added in column A with x for one, or each condition. Those names are fed to the Condition1 and Condition2 tabs to column A based on the x.

Notes are added to the person and the date changed

The Master then returns the latest notes Date for each sheet. The Notes field then looks to see which is larger, then indexes the correct sheet accordingly.

There's two ways that you can achieve this. The first would be to put the column numbers in another cell, say at the very top or bottom on your worksheet, then reference to that cell in your formula. The other way is to use the column() formula to return the current column number, then you can add or subtract from that to get to the column number you want.

Edit: I made a file as an example of the two difference methods that I supplied.

https://onedrive.live.com/redir?resid=BFA1EEE6F8D7B61C!1236&authkey=!AHd3t9chPf1bPpo&ithint=file%2cxlsx

=INDEX(A:D,MATCH(1,(A:A=G2)*(C:C=(MAX(IF(A:A=G2,C:C)))),0),column)

Change column to the column # that you want to return (i.e. Type would be 2).

Here is a file for reference.

There you go.

https://drive.google.com/file/d/0B03SxT05EktjbDNBdGhDY2h6eUU/edit?usp=sharing

Use 504 days and always use the S&P500. I put the other ones there just for fun but always use the SP

EDIT:All credit to professor Aswath Damoradan and my CS professor.

The basis xls can be obtained here: http://pages.stern.nyu.edu/~adamodar/

EDIT 2: thank you for your feedback

You can embed the two match formulas inside the INDEX formula to make your workbook cleaner but here is how to do it with helper columns.

E2 is =MATCH(TRUE,(I$2:I$7&J$2:J$7&K$2:K$7=A2&B2&IF(C2>"C","D-Z",C2)),0)

F2 is =MATCH(D2,L$1:O$1,1)

G2 is =INDEX($L$2:$O$7,E2,F2)

Copy down. Update $7 to $[whatever is your final row, or some row below that]

You also will need a row that contains 1, 500, 1000, etc.

I've heard, before, the suggestion that INDEX/MATCH is slower than VLOOKUP

To me, intuitively, INDEX/MATCH is more efficient, since it only has a 1-dimensional range as the first parameter, and another 1-dimensional range for the MATCH (in the form which replicates the VLOOKUP functionality)

A quick search (https://duckduckgo.com/?q=INDEX+vs+VLOOKUP+calculation+speed) turns up, as the top 5 results, 4 that say INDEX/MATCH is faster, and 1 that seems to suggest, initially, VLOOKUP is faster, but that is contradicted later on in the post

Now, admittedly, most of these are simply opinions - I don't know of any authoritative answer - and there is a link to a site which claims a 5% advantage for VLOOKUP (http://www.decisionmodels.com/optspeede.htm) yet, paradoxically, still recommends the use of INDEX/MATCH

That site also seems to be 15 years old (there's a discussion on Excel 97 vs Excel 2000)

Do you know of ant resources that compare the 2?

Started putting together a scenario for you and got a little carried away - sorry about that Chief. Wasn't certain how many grades you were looking for so gave you heaps! Basically just a single VLOOKUP formula and a table of figures and letters.

https://pixeldrain.com/u/5sGiCcQv

(I was having too much fun)

With your list of room numbers on Sheet2 Column A with capacity of rooms in Column B, I have used a VLOOKUP formula to gather the correct information. Also included is a Conditional Formatting formula to avoid double-booking rooms. Hope this helps.

https://pixeldrain.com/u/poMgowBN

Sheet2 Column A room numbers can be used in Data Validation!

Had a bit of a play with this, you need to add a column to determine sex and I have placed sum formulas horizontally and vertically so you can tell at a glance which students had days off and also which days had absences. Included a Filter on SEX column only so you can check differences between genders. Conditional formatting for ease of navigation. Hope this helps.

https://pixeldrain.com/u/6gjpayvu

I just tried to solve this using UNIQUE and FILTER in Excel Online (I don't have O365) -- was very disappointed to find you can't use these functions to create data validation lists! Could have been a very easy solution to dependent dropdowns. Oh well. Did it with VBA instead.

https://gofile.io/?c=aASD8B

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("DataTable[Country]")) Is Nothing Then Dim cell As Range Target.Offset(0, 1).ClearContents If Len(Target) = 0 Then Target.Offset(0, 1).Validation.Delete Else cityList = "" For Each cell In Sheets("Cities").Range("CountryCity[Country]") If cell.Value = Target.Value Then cityList = cityList & Replace(cell.Offset(0, 1).Value, ",", "") & "," Next cell With Target.Offset(0, 1).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=cityList .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End If End If End Sub

I've written the code to do this for you. Here is the code. Alternatively you can download the workbook here http://www.filedropper.com/ff_3

Sub Get_FF_Stats() Dim ie As Object Set ie = CreateObject("InternetExplorer.application") ie.Visible = True Sheets("Stats").Select myURL = Range("C2") ie.Navigate (myURL) Do If ie.ReadyState = 4 Then Exit Do Else End If Loop x = 5 y = 2 For Each player In ie.document.getElementbyId("ismDataElements").getElementsbyTagName("tr") For Each playerstat In player.getElementsbyTagName("td") Cells(x, y) = playerstat.innertext y = y + 1 Next playerstat y = 2 x = x + 1 Next player ie.Quit End Sub

4Tops is a tool I've used:

http://download.cnet.com/4TOPS-Compare-Spreadsheets/3000-2077_4-10410585.html

Another option is to put both of the spreadsheets in one file but on different tabs. On the 3rd tab, write a simple formula to determine if the cells from tab 1 and 2 are different.

No need for gold here I already have it.

I don't think there is a built-in way to send emails and manage unsubscriptions in Excel.

Having an unsubscribe link is complicated. I think you would be better served by dedicated software like customer.io or sendblaster. I haven't used either of these, but something like that would be better than trying something in Excel.

With any programming language, before job interviews I tend to try bashing out the first 10 or so problems from project euler - just quick and dirty solutions. This might work well for you as you could use both Excel and VB,

If you need to filter and separate by country, an option without VBA is to use a pivot table and then open details for any group required. That would put selected data in a separate sheet once at a time.

Another option is to install ASAP utilities. It is a great Excel add-in that already includes this functionality. Once installed, look it up in the ASAP Utilities Tab, under group Time saving tools / Range / Split the selected range into multiple worksheets. You don't even need to apply filters before.

http://www.asap-utilities.com

Note: I have no affiliation with that page, but I've been a user of this tool for years and I cannot recommend it enough.

I cannot see any pictures. https://snipboard.io/ is great for screenshots. Perhaps that would help me to understand what you want.

I'm not sure what your experience level is with excel so when you say that you want a formula to 1) We need for each matching element and gl code to be used once and 2) summed at the top... It sounds like you want one formula to do two things. Not impossible, but your question is not specific so it makes it hard to answer. If you say, I need a formula where I give the formula X and Y and need it to match X in column A and in the same row match Y in column B, then that's specific and easy to understand and answer.

You can use Color Brewer to select color blind safe schemes, and you can select color ramps based on the way you're displaying data. Kuler from Adobe is another useful tool for choosing color schemes.

Select your data table, and create a pivot table (Insert / Pivot table). In the dialog that pops up, make sure to tick "Add this data to the Data Model". Then drag "Drawing No" to Rows and "Sub Drawing No" to Values. Then select the entry in Values, click "Value Field Settings", and in the final dialog box, select "Distinct count".

Video here: https://screenpresso.com/=mThFb

Check out the workbook below. It will not work if you have lunch breaks included. I didn't want to use an array formula, so I just got the first match of an "x" in each row and added a half-hour for each occurrence of "x" in its row. It's important that you format the times properly, because 1:00 is usually 1AM in Excel. The Text function in the formula simply formats the time in the format it is written.

https://files.fm/u/2tpvb47e

You can do this either way. I'll explain how using an OLAP-based pivot table. First, for both options, you'll need each data source to be a table, so convert them to tables. Then, on the data tab, click Connections->Add(click the little right arrow on the right of "Add" button)->Add to the data model. Select your first table from the "Tables" tab. Repeat this step for your second table. Then go to "Data Tab"->Relationships->New. Select your first table's "Item", with the months included, as the foreign key. Select the 2nd table's Item as the primary key.

Now open a new sheet and go to Insert->"Pivot Table". Select "Use an external data source". Select "Choose external connection" and, on the Tables tab, select "Tables in workbook data model". Select where you want your pivot table to go.

Building the pivot table, use "Item" from the lookup table for the row field. Use "Actual" and "Budget" for the Values field and "Month" for the filter field.

Here is the example: https://files.fm/u/ypak4jdv

Here you go.

Depending on your post volume, you'll want to change the value in the cells

=IF(COUNTIF($G$1:$G189,CONCATENATE($I2,"-",J$1))>0,1,0)

189 in the current file. (Silly "code display" &gt = >, of course)

Make sure the formulas in F and G extend down to the bottom of your data. If you wanted, you could extend them down to row 10000, adjust the formulas once and be done with it.

Cell I1 is just a marker so the sheet knows what day to start looking on/for.

Add Instructors to the list (naming them whatever, I continued the unintentional military theme as an example). You mentioned you didn't care about post count, so I removed that entirely. If the Instructor has a least one post on a given day, they get a 1, if not, they get a zero. Column H is a simple percentage of how many days they've posted. Keep in mind that it's dependent on the number of columns off to the right.

>I'm actually auditing the instructors in these classes, about 35 instructors and 50 or so classes every week.

Bummer. This sheet will be able to tell you if the instructor posted at all, but on a per-class basis, that would mean you'd have to copy and paste each class, record the info you need, wipe it and paste in the next class.

We might be dealing with a "When you have a hammer, everything looks like a nail" problem. Have you considered asking the Forum Admin to automate a report of post count per forum per user per day or something? Assuming it's someone in-house and not some faceless forum provider, it shouldn't be too difficult, and then you could get an e-mail at 12:01am with yesterdays post breakdown.

I always look for the way to remove the Meat from the equation. :)

Unlike COUNTIF I believe it is best not to use SUMIF as SUMIFS use a different context and to avoid confusion this is my preference.

https://pixeldrain.com/u/1Cenb14B

This will definitely work for the 4 conditions you have shown although the Column 3 figure is redundant. I have used a MATRIX. INDEX / MATCH / MATCH

https://pixeldrain.com/u/S6w9gqgM

I have taken the liberty of altering the hours so both statements show overtime. For unique "CA" I have used a checkbox and when formulas completed you could either remote Column K or hide it. Hope it helps.

https://pixeldrain.com/u/bNUkdiXJ

Trust that you don't mind but I decided to have a bit of fun with it. VLOOKUP COUNTIF Conditional Formatting

https://pixeldrain.com/u/qFt3TZG7

EDIT the 25 should have been 26 in G5.

My interpretation of a possible scenario, hope this helps. 1904 Date System, Conditional Formatting and also SUBTOTAL formula.

https://pixeldrain.com/u/tQJKtqUj

I sorted and the re-arranged your data to make it more user-friendly for VLOOKUPs which will give your GPA in Letter and GPA reliably.

https://pixeldrain.com/u/sA9oz4K9

So that you are not overwhelmed by colour, I have added an extra condition to remove the Red after 60 days have passed from TODAY().

https://pixeldrain.com/u/giJQxZkP

First: As a date is recorded as a serial number, just sort by descending order (Z to A).

Just record macro using the recorder to work out code you need.

Second. The Hour function will return 9 for 9:00AM on any date. If you use something like:

=Hour(A2)=9

Where A2 is your date data.

It will return TRUE for each instance of 9:00 am up to 9:59 (any part of that hour). Then you can do a simple Autofilter and select all the False then just delete unwanted rows.

Turn off the filter and the remaining data will be what you want.

Third: Yes macro are limited to your knowledge and imagination (and sometimes physics).

Use the macro recorder, learn how to refer to ranges dynamically if the data you import has different amount of rows.

Here is a spreasheet with the True/False + Auto Filter option, plus a straight forward macro that does it all behind the scenes and deletes anything apart from the condition you specify in the If part.

http://www.filedropper.com/cleanhourdata

If you have any questions, let me know.

And just to address your sort, here is the file reloaded with a macro that will sort your dated from recient to most distant.

http://www.filedropper.com/cleanhourdata_1

Assuming:

• Column A contains the 161 field titles, constantly repeating
• Column B contains the field values with a units data every 161 fields
• Sheet with values is called Initial (same as your gdocs sheet)
• Sheet with final look is called Final exists
• You want to redo the table each time, not append new .hps files to the table each time (this can be done it's just not setup to do so)

Text of macro

XLSM File

Easiest way to test is to open the XLSM file and clear out the "Final" sheet (don't delete just clear all values). Then run the macro Convert. Otherwise you can copy/paste the macro to your own workbook and modify it to test there.

It'd be pretty easy to record a macro that imports the CSV/HPS file if you want to automate that step as well.

If that's the data you're starting with, then that's pretty much going to be the way you're stuck doing this. Have you tried pulling a better data source from the internet? I feel like the majority of the information you're looking for has already been broken down this way multiple times, and you just haven't found a good source yet.

​

I would suggest spending a little more time hunting for a good data source to pull from. It might be the time saver you didn't know you needed.

​

https://www.back4app.com/database/back4app/car-make-model-dataset

​

Try this and see if it's not better suited for your purpose.

R & Shiny or Shiny Dashboard can do some pretty cool webapps / live visualizations with a little programming. I have done a few of these at my work. It requires almost no HTML/web programming knowledge but you will need to learn a bit of R. The Shiny Server portion runs in linux, eg ubuntu server, and can be set up somewhat easily. The main con here being you would have to reimplement your excel work in R, which likely is not a minor task.

Hey!

Could try Sololearn (scroll down... it offers both Excel and Advanced Excel). Sololearn has mobile apps for Android, iOS, and Windows; or you can just do it right online. It's free too!

Also, Excel is a Microsoft product and if you Google something like "Excel learn Microsoft" or something, I'm sure there are some official Microsoft endorsed/offered tutorials.

I'm not OP but I am pretty sure he is referring to R, the statistical computing software. You might be familiar with similar programs like SAS, SPSS, or perhaps Minitab since I think they are used often in education.

I'm a bit surprised that he chose R to mention specifically for finance... I worked in M&A for a time, and never had to move beyond Excel and Access. Both programs were VITAL in almost every job I've ever had, from real estate to finance.

Download and install Notepad++

1. Copy your column data to Notepad++
2. Highlight the lines you want to join (or use Ctrl + A to select everything)
3. Choose Edit → Line Operations → Join Lines from the menu or press Ctrl + J.

Yes, I did it too :

• also took the 4 query approach,
• simply duplicated the first 4 column processing query and modified by hand.
• Then a simple Append of the 4 chunks.
• also ran into the missing header on the column you mentioned.
• I added in my own Parameter table which enables the file to reference itself - and read itself in

It's likely possible to have a simpler function for returning a table of 4 columns based on a numeric index.

My version: https://expirebox.com/download/fdd763abeeb5cc3a534eab897484030e.html

So there are +-600x the customer code in the top row?

What you are looking to do is an UNPIVOT - which is a power query operation in Data -> Get & Transform

Here's an example of how to do it.

https://expirebox.com/download/c84d9c6cd7e07689adbe3871ed68109a.html

It doesn't need to be done using groups

• I would add a helper column to the source and a table or two which would support a lookup and returns a sort of group "id".

I made you an example:

https://expirebox.com/download/69a595f806d53bed78a10c646c7495bb.html

• randomly generated values
• I made a lookup table which specifies ranges of values with a mapping to a "group slice".
• It supports different "sets" of ranges.
• one set is active at a time - but can be easily changed. The set values can also be names - I used numbers but that's not fixed.
• The currently active set is highlighted (with conditional formatting)
• a set can be arbitrarily broken down - you just have to set a lower limit and an upper limit - and give the slices different names (it would still work if you gave them all the same name - but wouldn't make much sense).

Here are two simple but useful M functions:

https://expirebox.com/download/12da28e3930bb1a7600529bbe2b37a40.html

• fnGetParam( name ) returns value from parameters table
• fnRange( range-name )
  • returns any named range as a table.
  • This prevents you from having to have a separate query to access a range of data in Excel

It's a web api and every time you send a GET to that web page it gives you the last trade price, quantity and bid/ask at that time (aka Level 1 market data).

This Power query driven sheet will load the latest price into the table once per minute...

https://expirebox.com/download/cabe6ed07ec1d58e5ef782a099bf646c.html

No prob, glad you tried it out first.

If I understand the problem right, here's the completed sheet with all the columns needed. You can condense the formulas into one if needed.

1. https://expirebox.com/download/d1b6f3f6ab10ffeffdb283d05918af7f.html