Spreadsheet Geeks Concatenate!

[Eskimo Brother]

I'm using 2016 at work right now.

I have 2017 at home, and it's a Mac if that makes a difference.

[jer_33]

Thanks guys, I'm sure I'll get to sorting that out. However, it's only the tip of the iceberg of what I need.
Ideally I'm trying to get points to specifically update inside my existing table to line up with the order players are in now. Though I guess I could probably do that with a lookup or something, can't I?

VLOOKUP is exactly what you'd need.

Before you start making experimental changes to your existing table, I would recommend making a copy of it - otherwise you run the risk of screwing up your original table.

[jer_33]

I have 2017 at home, and it's a Mac if that makes a difference.

https://excel.uservoice.com/forums/3...-the-mac-excel

Excel for Mac allows you to import data from a web query, by going to the Data menu > Get External Data, and then import from a web query. We are not planning to add a wizard for creating these queries like we have in Windows Excel. We are planning for a more modern way to get data from the web, with Get and Transform (PowerQuery), which is in the planning stages for Excel for Mac.

I agree that it's frustrating how Excel on the Mac is inferior to Excel on Windows in this area (and others), but Steve K's answer is correct -- Excel for Mac does allow importing of data from the web. If you search on YouTube for "excel 365 mac get external data" (remove the quotes) you'll get a few videos that show how to do it. They are a bit convoluted, but they work, at least for the data they wanted to scrape.

[Eskimo Brother]

https://excel.uservoice.com/forums/3...-the-mac-excel

I tried going through that troubleshoot and found the same page you linked. My options don't include "import from web query" though. I only have:
Run saved Query
From Database
Import Text File
From Filemaker
Import from Filemaker Server

If any of those options sound right, let me know.

Also, apologies for derailing the thread, but Excel is how I do a lot of hockey stuff. From keeping salary cap rosters organized, planning games played maximization, putting together salary projections and player rankings for cap leagues (and sharing them over google docs), as well as a lot of other things. If anyone has some questions or anything they would like me to share with those, happy to discuss. I'm just trying to optimize the updates for the predictions/rankings sheets where I would input them all one by one at this point.

[jer_33]

If you have them saved in Google Docs, then you can open them in google spreadsheets and make the changes you want. It sounds like Excel for Mac is making things tougher for you.

[Auston'sWilly]

Getting auto stats from Frozentools into excel worked pretty well, see embedded image, however good luck using lookups with this data.
Like with the random spelling changes I mentioned earlier in the rankings, the data in frozen tools puts random spaces in front of some of the names, but not others.
e.g. lookup PTS/G for Mantha Anthony will work fine but do the same lookup for Josi, Roman or Carlson, John and you'll get an error because of the extra space in front of the names.

Capture.JPG

[Auston'sWilly]

Here's an excel file download link. It's basically a published page based on Jer_33's formula from earlier that is linked to hockey reference.
I think it will auto update as the source updates, but I'm not positive.
https://docs.google.com/spreadsheets...ub?output=xlsx

jer_33 -thank you for that google sheets formula - that is awesome. I wish I could give you 50 reps right now. cheers

[ericdaoust]

Getting auto stats from Frozentools into excel worked pretty well, see embedded image, however good luck using lookups with this data.
Like with the random spelling changes I mentioned earlier in the rankings, the data in frozen tools puts random spaces in front of some of the names, but not others.
e.g. lookup PTS/G for Mantha Anthony will work fine but do the same lookup for Josi, Roman or Carlson, John and you'll get an error because of the extra space in front of the names.

Capture.JPG

That's actually the hot/cold icon from the website plus an extra space for those names.

[Auston'sWilly]

That's actually the hot/cold icon from the website plus an extra space for those names.

Ah!!! ok, that makes sense. thanks

[jer_33]

Is anyone interested in a 'quick lesson' on VLOOKUP?

The VLOOKUP function will allow you to quickly grab data from several data tabs, and collect it in one place.

Works great if you wanted to create a spreadsheet to:

- compare player stats in a single season
- create a list of players and show their stats from the last 3 seasons
- add missing data to an existing sheet (example, adding salary data to a sheet with only scoring data)