Array index Excel

How can I do an index match where the lookup_value is an array (i.e. range of cells, say A1:Z1), and the lookup_array is a an array of arrays (e.g. A10:Z50)? Or in other words, can I test one row of cells for existence in a table and return the row number? Screenshot included.

2020.10.29 16:18 notimeforwork How can I do an index match where the lookup_value is an array (i.e. range of cells, say A1:Z1), and the lookup_array is a an array of arrays (e.g. A10:Z50)? Or in other words, can I test one row of cells for existence in a table and return the row number? Screenshot included.

Sorry for the long explanation. There's a screenshot at the end.
I've got a Table where the header row is the x coordinates and each table row is they y coordinates, and there 46 coordinate pairs in each series. This Table is where I'm cataloging unique series (aka "curves") which relate, in this case, flood depth and percent flood damage. I'm collecting the source data from these curves via literature review, and what I'd like to be able to do is when I find a new curve, check to see if I already have it.
Stepping away from the Excel nomenclature, basically I'm treating each row as an Array, where the cells are the elements in the array, and so when I have a new curve from literature review, I want to test if that array already existing in my table, and return the row number if so.
I tried using TextJoin to concatenate each row to a string in a helper column, which works, but if I then use index match to try and lookup whether my new array already exists, I just get a #VALUE error, so the formula is failing to evaluate, maybe because the text array is too long (277 characters)? I also can't just test the sum of the cells in each row, because I've concerned about the shape of each curve too.
I've got an idea in my head that there should be a way to use boolean logic here, some kind of array formula that tests the equality of each pair of cells, but I can't figure out how to make that work inside a index match so that it tests all the rows. I can do it one row at time with the Exact function as an array formula, but get stuck after that.
Any help is greatly appreciated.
Here's a screenshot of a simplified example:
submitted by notimeforwork to excel [link] [comments]

2020.10.28 12:08 wekapipol Watch Dogs: Legion - Review Thread

Game Information

Game Title: Watch Dogs: Legion
Publisher: Ubisoft
Review Aggregator:
OpenCritic - 75 average - 62% recommended - 91 reviews

Critic Reviews

3DNews - Алексей Лихачев - Russian - 9 / 10
Watch Dogs: Legion doesn't have the main protagonist, instead we have a city full of oppressed and tired people with their own stories. Other than that this is the usual Watch Dogs game and fans of the first two should be pleased with what it can offer.
ACG - Jeremy Penter - Wait for Sale

Video Review - Quote not available

Ars Technica - Kyle Orland - Unscored
In the end, the London of Watch Dogs: Legion feels a mile wide but only a few feet deep. What promises to be endless variety in character choice and hack-driven gameplay options quickly boils down to the repetition of the same old gameplay and plot tropes.
Attack of the Fanboy - Diego Perez - 3.5 / 5 stars
Watch Dogs: Legion is incredibly ambitious, but the play as anyone system needs a little more work. The story suffers from the lack of a central protagonist, and it's hard to get attached to any of your characters when the character models and animations are stiff and robotic. Still, there's a lot of fun to be had in futuristic London.
BaziCenter - Bahram Bigharaz - Persian - 6.5 / 10
After so much anticipation, Watch Dogs: Legion is finally here, failing to impress. Almost every single problem that prevented the 2 previous version to reach their full potential is still there, and the ability to play as all NPCs added even more issues to the game. Yes, the world is beautiful and you have all the freedom that you want, but as a game, Watch Dogs Legion is shallow and suffers from poor level and character design. A strong contender for the most disappointing game of the year.
Bazimag - Vahid Zohrabi Nejad - Persian - 5.6 / 10
Watch Dogs Legion is yet another open-world game like other Ubisoft's games, full of great ideas, but in action, they don't have enough depth and don't perform well in general. A soulless world with poor level designs and exhausting missions make a graveyard for the series's real potential.
COGconnected - Michael Chow - 75 / 100
Overall, Watch Dogs: Legion is a fun game with a nifty new mechanic that can be utilized in different ways in the future.
Cerealkillerz - Manuel Barthes - German - 8 / 10
Until now the story of Watch Dogs was an up and down, which doesn't change that much in Watch Dogs: Legion. The energy that went into the unique recruiting mechanic leaves a lot missing in the actual game world and the story, which makes the trip to london a bit cloudy, classic british.
Cheat Code Central - Jon Gronli - 5 / 5
Even though Watch Dogs Legion already gives you an impressive amount to do as well as a lot of options on how to do it, it’s still going to be growing. I can’t wait to see what’s coming next and how It is going to affect what’s already in place. I’m also looking forward to the multiplayer component, which I’m more than willing to write about when it comes out. So, come on. Join the resistance.
Console Creatures - Luke Williams - Recommended
Watch Dogs: Legion's Play as Anyone is an exciting mechanic and post-Brexit Britain is easily the best setting yet. However, Watch Dog: Legion's brilliance is hidden behind a fair amount of smog.
Critical Hit - Darryn Bonthuys - 7.5 / 10
Watch Dogs: Legion is a fascinating game, massively ambitious and crawling with technology that isn't just on the bleeding edge of what's possible, it's pure magic to see unfold. All of that may sound impressive but slick software and a bustling metropolis of people power can't hide the dull gameplay and shallow approach to the sandbox shenanigans of Watch Dogs: Legion. It's still a fascinating game to experience in short bursts, and it's going to be fascinating to see how Ubisoft evolves London to make it vox pop as a next-gen headliner.
Daily Star - 4 / 5 stars
One that is very English, packed full of wild and interesting characters, each with their own story to tell.
It’s a huge step forward in that regard and one that should be celebrated as it shows a way forward for video game development.
Digital Trends - Tom Caswell - 2.5 / 5 stars
While Ubisoft presents its best open world to date, the main gameplay hook falls flat.
Digitally Downloaded - Trent P - 4 / 5 stars
What players will find when picking up Watch Dogs: Legion is a game that is prepared for a long post-launch game-as-a-service experience. The additional DLC announced so far leans into the strengths of the game and established ideas that the series does well. The beekeepers, paintball guns and magician tricks all bring a sense of playful humour to the series, but it is worth noting that anyone who is (rightfully) tired of Ubisoft's content approach to games is going to find this one a very content-driven game.
DualShockers - Ben Bayliss - 7.5 / 10
Watch Dogs: Legion offers an incredibly vast recruitment system that wonderfully complements its hacking mechanics while boasting the darkest story in the series.
EGM - Michael Goroff - 8 / 10
Watch Dogs: Legion pushes through Ubisoft's generally noncommittal attitude towards storytelling and exploiting current events to create something that feels like a genuine shift, or at least the prototype of that shift. It might be a sloppy game in many regards, but Legion offers a novel way to experience an open world, with its interconnected NPCs and the introduction of permadeath to the genre. - Panagiotis Petropoulos - Greek - 8 / 10
Watch Dogs: Legion is much better in terms of depth and hacking and also comes with a huge living world. It's by far the best game of the series. - Alessandro Bruni - Italian - 7.6 / 10
Ultimately, while perfectly able to offer players a good number of hours of fun, Watch Dogs Legion fails to fully realize the potential of its basic concept, yielding to the flattery of an open world model that, at the end of the console generation, loudly requires more innovation.
GAMES.CH - Benjamin Braun - German - 89 / 100
Watch Dogs Legion mostly benefits from its rich game world in futuristic London. It's also fun to build a whole army of DedSec agents, using their special abilities within fight and stealth sequences or utilizing them on solving puzzles. It's not all roses concerning story or performance on current-gen consoles. Nonetheless it's the best part of Ubisoft's open-world hacker series so far. - Michał Grygorcewicz - Polish - 7.5 / 10
I had really low expectations and Watch Dogs: Legion turned out to be a pleasant surprise. It’s a decent action game with some cool ideas and mechanics that yield several dozens of hours of fun, prvided you like wandering around virtual cities doing the same thing over and over again.
Gadgets 360 - Akhil Arora - 8 / 10
Watch Dogs: Legion lacks a soul. It's also a passive game, since there's no active push-and-pull. Albion took over London, and now you push them out one borough at a time.
Game Informer - Marcus Stewart - 9 / 10
Legion offers a refreshing and fun change-up to the Watch Dogs formula that succeeds in letting players forge their own path like never before
Game Revolution - Paul Tamburro - 4 / 5 stars
Watch Dogs: Legion‘s beautiful London and its array of recruitable denizens make it one of the most enjoyable games of the year.
GameMAG - Александр Логинов - Russian - 7 / 10
On the one hand Watch Dogs: Legion is a revolutionary game with ambitious open world and thousands upon thousands of characters, probably created by some kind of neural network. The gameplay is fine, and if you love original Watch Dogs, you will feel right at home with this new title. But on the other hand Legion clearly lacks a strong narrative lead.
GameOnAUS - Royce Wilson - Recommended
There are some fantastic ideas in the game which mostly work, but also require an element of metaphorically ignoring the stagehands and the suspension of disbelief may simply be too much for many players.
GamePro - Hannes Rossow, Markus Schwerdtel - German - 79 / 100
Watch Dogs: Legion relies on a unique concept that offers many possibilities, but for which many compromises are also made.
GameSkinny - Mark Delaney - 8 / 10 stars
Watch Dogs: Legion throws out a decade of Ubisoft's cluttered-map open worlds in favor of exciting systems that deliver unique emergent moments consistently.
GameSpot - Alessandro Fillari - 8 / 10
Watch Dogs: Legion struggles with tone at times, but its empowering message about unity and justice still shines in a game that is as absurd as it is impactful.
GameZone - Cade Onder - 6 / 10
While it has its moments, Watch Dogs Legion doesn't have enough to feel like a fun place to escape to. The gameplay is too repetitive and too restrictive to allow for anything tremendously exciting over a long period of time. It's a game that shows all of its tricks within the first few hours and leaves you with nothing but jank for the remainder of your playthrough.
Gameblog - Rami Bououd - French - 7 / 10
Watch Dogs Legion is a fun title with interesting and clever gameplay.
Gamerheadquarters - Jason Stettner - 7.8 / 10
Watch Dogs: Legion is great, it features an intricately detailed open world London to explore where you can recruit basically anyone though the story could have been more intriguing and the performance while driving could have been better.
Gamersky - 不倒翁蜀黍 - Chinese - 8.5 / 10
Watch Dogs: Legion is the most ambitious and innovative one in the franchise. You can play as anyone and finish your job in any way. The open-world of future London is so beautiful and so well-crafted that I always can find something interesting to do.
GamesRadar+ - Alex Avard - 3.5 / 5 stars
Legion royally shakes up Watch Dogs' open-world template with a Play as Anyone mechanic that just about outweighs any headaches left by its rough edges.
GamingBolt - Shubhankar Parijat - 9 / 10
Watch Dogs: Legion is definitely the best game in the series so far- and dare I say, one of the most engaging and inventive open world games I have played in years.
GideonsGaming - Joseph Pugh - Unscored
Overall I'm having enough fun that I want to stop writing and go back to playing it, which is always a good sign. The recruit anyone system is working incredibly well, and it's super addictive. The simulation is impressive, even if I haven't determined how much of that simulation affects the gameplay yet. And the few design flaws haven't been enough to hinder my enjoyment after 16 hours. Here's hoping it remains that way as I continue working on my full review.
Glitched Africa - Marco Cocomello - 75 / 100
Watch Dogs Legion is not a bad game I just believe it was too ambitious for its time. The recruiting system could have been something great but instead its shallow and delivered cliche characters with no real purpose. Unfortunately, this does not help the gameplay and story much. There’s a lot of fun to be had here but if you start expecting more from it, you are going to be let down.
God is a Geek - Mick Fraser - 8.5 / 10
Watch Dogs: Legion suffers from a little jank in the tank, but the recruitment system is fantastic and there's just so much to see and do. The open world is full of detail, and the whole experience is full of heart.
GotGame - Dragos Dobre - 8 / 10
The post-Brexit dystopian London is exactly the right amount of craziness and fun I was expecting from a Watch Dogs game. Even though the original recipe hasn't changed a lot in the past few years, you can see the progress they made with Watch Dogs: Legion, polishing the game with every iteration.
IGN - Dan Stapleton - 8 / 10
Watch Dogs: Legion's bold use of roguelike mechanics in an open-world action game pay off in interesting ways, making this visit to near-future London feel more varied than the previous two games.
Impulsegamer - John Werner - 4.8 / 5
Without a doubt, “Watch Dogs: Legion” ticks all the boxes required to be a true Watch Dogs game, embracing elements from both previous games while brining its own flavour to the table.
Inverse - Tomas Franzese - 7 / 10
Watch Dogs: Legion pushes current-gen hardware to the limit, and suffers for it.
Life is Xbox - Dae Jim - 89 / 100
Watch Dogs Legion ‘play as everyone’ mechanic works brilliantly, this is a genre-defying feature and something that sets the game apart from its competition.
Marooners' Rock - Andrew Peggs - 8.4 / 10
Overall, I feel as if Ubisoft has dug back into what made Watch Dogs enjoyable to play. With some improvements to the overall gameplay and tweaks as time goes by, I can see others enjoying the game.
Metro GameCentral - 6 / 10
A disappointingly tame vision of a near future dystopia, that represents a perfectly competent use of the Ubisoft formula but falters in its attempts to add anything new to it.
MondoXbox - Andrea Giuliani - Italian - 8 / 10
Watch Dogs Legion keeps the series' base mechanics while enhancing the whole formula thanks to the higher gameplay and tactical variety provided by the huge choice of agents available. This has the downside of making every character pretty forgettable though, keeping us from establishing an emotional bond with any of them.
New Game Network - Alex Varankou - 65 / 100
Being able to Play As Anyone in Watch Dogs: Legion is impressive at first, but it becomes a detriment to the core experience that's in need of revitalization. The hacking and stealth infiltrations haven't changed a bit, and with repetitive mission design and numerous technical issues, this latest chapter finds DedSec in an identity crisis.
Nexus Hub - Sahil Lala - 8 / 10
Watch Dogs: Legion is more of the same Watch Dogs formula fans of the franchise have come to expect. There are additional gimmicks and features that round off the product and it’s a great game to spend time in. The mystery plot and the intrigue around finding out just who exactly Zero Day is and putting a stop to him is great and will easily keep you entertained for 50 hours or more as you explore London.
PC Gamer - Christopher Livingston - 80 / 100
Playing as anyone works great in Legion—once you've finally found the right group of anyones.
PC Invasion - Tim McDonald - 7.5 / 10
The connected, living world here is a genuine revelation, and it's well worth exploring if you're willing to mess around and make your own fun. It's just a shame that some of the vibrancy and depth of Watch Dogs 2 has been lost in the process.
PCGamesN - Dustin Bailey - 7 / 10
Richly realised systems and empowering abilities create a tremendously fun sandbox to dig into, but another toothless story ensures these flashes of brilliance never cohere, leaving Legion feeling less than the sum of its parts.
Pixel Arts - Arman Akbari - Persian - 7.5 / 10
Watch Dogs: Legion is a game that has been able to maintain diversity and difference among thousands of playable characters. However, along with the dynamic and detailed world, the game suffers from weakness in the design of the stages and unfortunately becomes repetitive and boring over time.
PlayStation Universe - Neil Bolt - 6 / 10
While Watch Dogs: Legion does the basics well and has a refreshing change of scenery, it moves backwards from Watch Dogs 2 in terms of characters and storytelling. It's still quite enjoyable to get up to tech-based naughtiness in London despite that, but the underlying open-world template Ubisoft keeps using ends up feeling overexposed here.
Polygon - Owen Good - Unscored
Watch Dogs: Legion’s cast of randos makes a surprisingly winning team
PowerUp! - Paul Verhoeven - 6.3 / 10
And that’s the real issue here: the previous game was a story and a damned good one. Watch Dogs Legion is a playground and a damned good one. All it took was a shift in priorities to make the open-world feel less like a world, and more like… well, a game.
Press Start - James Mitchell - 8 / 10
Watch Dogs Legion builds upon the solid foundation established by Watch Dogs 2 while adding its own ambitious twist with mixed results. Having literally every character playable is a gargantuan task, and from a gameplay perspective it works to cement Legion as the best Watch Dogs game thus far. Narratively speaking, however, it collapses under its own aspiration to offer an intriguing concept with spotty execution. Regardless, Legion is a triumph for making good on most of its lofty promise and a triumph for the series.
Rock, Paper, Shotgun - Nate Crowley - Unscored
While I may not identify with any of my guerrillas and their grab-bag backstories, nor feel any sense of real investment in the fate of DedSec as a whole, I’m still attached to this strange band of possessed berserkers. We’ve had a good time together, in this nonsense dystopian playground.
Rocket Chainsaw - David Latham - 4 / 5 stars
Watch Dogs: Legion brings new ideas to the franchise while keeping within the world of Blume Corp’s ctOS.
Screen Rant - Leo Faierman - 3 / 5 stars
The takeaway is this: Watch Dogs: Legion is an ambitious simulation which reliably fails whenever players push against its boundaries. Like the cargo drones which grant them the ability to freely fly, it hits an invisible ceiling that prevents players from soaring above London’s skyscrapers.
Shacknews - Donovan Erskine - 8 / 10
Watch Dogs: Legion is a hacking good time and a great addition to Ubisoft’s technology-based saga.
Sirus Gaming - Lexuzze Tablante - 7 / 10
Watch Dogs Legion tries so hard to innovate the franchise, but in doing so, it feels like a product that was either rushed or there was no love for it. Ubisoft Toronto did their best to give us a whole new Watch Dogs experience, but when the second installment of the franchise is the benchmark, it’s hard for me not to nitpick on these issues I find in the game. I love the franchise, but this isn’t the kind of innovation I’ve expected Watch Dogs to have.
Skill Up - Ralph Panebianco - Unscored
Watch Dogs: Legion is an ambitious title. Perhaps a little too ambitious. As much as certain parts of the game shine, you can't help but feel that the game is too clever by half.
Slant Magazine - Steven Scaife - 2.5 / 5 stars
It's difficult to escape a sense that the game's ambition far outstrips the number of unique people it can plausibly render.
Star News - Rod Oracheski - 4 / 5 stars
Watch Dogs: Legion sticks you in the shoes of characters you’d never have chosen otherwise, and it works more often than it doesn’t.
Stevivor - Luke Lawrie - 6 / 10
There’s some fun to be had in Watch Dogs Legion, but it becomes so repetitive that by the end of the game everything feels like a chore — one I was desperately wanting to be over hours before its credits rolled.
The Digital Fix - Andrew Shaw - 8 / 10
The best Watch Dogs game yet. While it's dragged down by long load times and some repetition, Legion is a hugely enjoyable game that offers players a level of freedom that is rarely seen in this genre.
The Game Fanatics - Trevor Paul - 8.5 / 10
Overall, Watch Dogs Legion is a ton of fun. There is so much to do and experience in this game and so many different ways to do it. The hacking puzzles are familiar but still fun and sometimes challenging. The real star of this game is the variety of characters you can recruit and the backstories that come with them.
The Games Machine - Simone Rampazzi - Italian - 8 / 10
Watch Dogs: Legion starts with some really intriguing background ideas, ideas that try to dig deep and to leave us with many more questions about the near future. The overwhelming control of a state willing to know everything about its citizens, however, does not prevent a few uncertainties about the gameplay, a sore note that prevents the game from shining as hoped. However, it remains an enjoyable offer, ready to satisfy the taste of lovers of the genre.
TheSixthAxis - Miguel Moran - 8 / 10
Watch Dogs Legion is a different type of sequel to Watch Dogs 2, contrasting in its approach to creating a hackable open world playground, but with no less impressive results. Playing as any citizen in London leads to some less-than-engaging story moments, but the web of relationships and activities that crop up as a result of the systemic design is mind-blowing. I rarely did the same thing twice in Watch Dogs Legion, and if I did, I wasn't doing it the same way twice. Watch Dogs Legion truly feels like a living, breathing world, and it's a world that I plan to revisit often, even though I've seen the credits on the main story roll.
ThisGenGaming - Robby Bisschop - 90 / 100
Watch Dogs: Legion is a massive game with perhaps the biggest recruitable main cast of characters we’ve ever seen. With its varied gameplay and its tried-and-true Ubisoft open-world experience, it offers dozens of hours of entertainment and isn’t to be missed.
TrueGaming - محمد جابر الصهيبي - Arabic - 8.5 / 10
Watch dogs legion gives you freedom and it's accentuated in the new recruiting system which makes this title worth playing even before the release of next gen version.
USgamer - Mike Williams - 3.5 / 5 stars
The new "Play As Anyone" system is as impressive as it sounds on paper, creating a host of intriguing characters if you choose to dive into their backgrounds. Crafting your own version of DedSec is a ton of fun, especially early on. The problem is the gameplay of Watch Dogs Legion is mostly the same as its predecessors and the missions are quite repetitive overall. It's not a step back for the series, but the hacking and stealth core of the series does need an overhaul.
VG247 - Lauren Aitken - 3 / 5 stars
Watch Dogs fans and more die-hard anarchists among you might enjoy it more, but between the short storylines, underwhelming tech and mission types and the general “everything is on fire” vibe, it just doesn’t rate highly for me.
[VICE] - Austin Walker - UNSCORED
'Watch Dogs: Legion' Promises Revolution, But Mostly Delivers Distraction You can play as anyone you want, but the game remains the same.
Video Game Sophistry - Andy Borkowski - 6 / 10
The ‘Play as Anyone’ feature is the game's biggest fault. There’s no way to really work as a team. Instead each individual is one part of a fully fleshed out protagonist that has now been cut into 20 different pieces and called upon to work without the other. A severed hand doesn’t make a hero.
VideoGamer - Josh Wise - 5 / 10
Where the action comes alive is in the leaving behind of bodies altogether. Most missions involve breaking and entering, and the thrill lies in the absence of any breaking.
Wccftech - Rosh Kelly - 7.9 / 10
Watch Dogs Legion is a great step forward for the series, with enough experimental new gameplay features to complement the familiar mechanics. London is incredible, and exploring it is an almost visceral experience. It's just a shame that the story doesn't hold the same familiarity that the map does.
We Got This Covered - Todd Rigney - 3 / 5 stars
Although the recruitment system provides a few hours of entertainment, Watch Dogs: Legion feels like a series of systems masquerading as an open-world adventure game. Compared to the first two entries, Legion is a massive step backward, both in terms of story and execution. This is paint-by-numbers Ubisoft on autopilot.
WellPlayed - Zach Jackson - 8 / 10
With a surprisingly good narrative that excels thanks to the unique ability to turn anyone into a DedSec hacker, Watch Dogs: Legion is a damn good time
Windows Central - Carli Velocci - 4.5 / 5 stars
Watch Dogs: Legion is a departure from the typical Ubisoft brand, and it's better for it. The play as anybody system just works, there's a lot to do, and it's unabashedly political in a way that feels important in 2020.
submitted by wekapipol to Games [link] [comments]

2020.10.24 05:15 NietzscheJr "Murder is Bad", and Other True Things: An Introduction to Meta-Ethics!


There are many purposes to this post:
  1. To introduce and explain the most popular views in meta-ethics.
  2. To give some common arguments for and against these positions.
  3. To connect some common positions this subreddit holds to the academic terminology
Most of this post is not argumentative! It does contain arguments, but the purpose is not to convince you of any one position. The primary purpose of the non-argumentative sections is to help ground those unfamiliar with Meta-Ethics with the tools necessary to describe their beliefs accurately and in a way that other people will understand. The most might also give you access to new arguments to support your position, or perhaps an argument to support another.
You can get nearly all of the information I've used here on the SEP or the IEP. Some philosophers also allow you to access their papers for free if you go through their personal websites. Some literature reviews are also free. I've linked to free resources where I can. However, some sources are going to be behind a pay wall. If you need some help to get over that barrier join the discord here and ask about it.

What Is Meta-Ethics?

In analytical ethical philosophy, there are three branches: applied ethics, normative ethics, and meta-ethics. In applied ethics, we ask if abortion is wrong? If normative ethics we ask what our duties might be, or whether we even have duties? In meta-ethics we ask what properties do moral propositions even have; can moral propositions even be true or false?
Let's take a quick example: take the proposition "murder is wrong". Is this true? Is it mind independent? Is it the expression of a belief or an attitude? If it is a fact, what sort of fact is it: natural or non-natural? Are those even the only options?
We are going to discuss the answer to these questions, and one's like them, throughout the post.

Realism and Anti-Realism

There has been some debate over how best to taxonomise meta-ethics but I see the primary split as between Moral Realism and Moral Anti-Realism.
Moral Realists minimally claim that moral propositions can be true or false, and some are actually true. By a moral proposition, they mean a proposition of the kind "theft is wrong" or "murder is bad". Moral Realists often commit to more than this, though: some argue these truths and falsities are objective -and by objective I mean not dependent on the attitudes or other beliefs held by an agent- or that moral facts are mind independent (Geoff 2015).
Moral propositions can be simple, like the two examples given above, or more complex like the example: "Sandra should not have lied to her boss" is still a moral proposition!
Moral Anti-Realists reject moral realism. However, what exactly they are rejecting depends on their understanding of realism: they could reject minimal realism or something more substantive (Richard 2016). Let's take another example: the anti-realist denies that "murder is bad" is true.
Other accounts divide the positions differently: Michael Huemer sees the divide between Moral Intuitionists and everyone else (see Huemer's Ethical Intuitionism) while others see the major split occurring between cognitivists and non-cognitivists (see Alexander Miller's Contemporary Meta-Ethics: An Introduction).

Cognitivism and Non-Cognitivism

All moral realists - and Error Theorists - are Moral Cognitivists (van Roojen 2018). Moral Cognitivists say that (i) moral statements express beliefs and (ii) they can be true or false. Moral statements differ slightly from moral propositions - moral statements are moral propositions verbalised.
Conversely, Moral Anti-Realists can be split into broad camps. Non-Cognitivists argue that "moral statements have no substantial truth conditions. Furthermore, according to non-cognitivists, when people utter moral sentences, they are not typically expressing states of mind which are beliefs, or which are cognitive in the way that beliefs are. Rather they are expressing non-cognitive attitudes more similar to desires, approval or disapproval."(Richard 2016). Error Theorists claim that moral propositions are truth-apt – they are able to be true or false - but are never true (Richard 2016).
These are the two top level distinctions: Cognitivism and Realism. Nearly every taxonomy is going to start by talking about realism or about cognitivism. Ours has proved no different. However, I'm going to put cognitivism to the side from now on. It's an important position and this is mostly for the sake of space.

Generalist Arguments for Realism and Some Replies

I understand "generalist arguments" as arguments for these top-level positions: these arguments (typically) work for defending a broad realism or anti-realism. Later we are going to look at arguments for lower-level positions like Moral Naturalism and Error Theory.
I am going to look at two arguments for Realism. I will not argue that these are successful. I take the Arguments for Anti-Realism, at the top level, to be refutations of Arguments for Realism.
  1. Moral Realism as the Default Position
  2. Argument from Epistemic Facts (Companions in Guilt Arguments)
The first argument often had between Realists and Anti-Realists is over who holds the default position. More precisely: who has the burden of proof?
The most common position has been that Anti-Realism has that burden. Jonathan Dancy, David McNaughton and David Brink all posit that people "begin as (tacit) cognitivists and realists about ethics... [and therefore] Moral Realism is our starting point." (Brink 1989) This view is motivated by several considerations: one is intuition and one is the explanatory power. Why does it seem that moral propositions held sincerely by agents seems to motivate them? Well, because they are beliefs and judgements! Why do we talk about morals as though they are real and refer to them as beliefs in everyday conversation? Well, because they are! I don't want this argument to over reach: the point is merely that the default position is a Moral Realism and that it is a position that one needs to be motivated away from. This isn't a position held just by Realists: John Mackie accepts that his view is unintuitive (Mackie 1977). He believes he has sufficient arguments to move people away from realism.
There are replies to this argument. The SEP has twin articles on this. One on intuitions and the other on explanatory power.
The second argument, or set of arguments, made by realists are companion in guilt arguments. These arguments say that if we reject a moral realism, we have to reject realism about lots of other things we typically accept (and accept with good reason). Therefore, we ought not to reject realism. Typical companions are epistemology, mathematics, the mind and sometimes philosophy itself; to reject facts about these is a tremendous bullet to bite that is both massively counter intuitive and has to run the gauntlet of rejecting many good arguments.
Terence Cuneo gives a version of such an argument in The Normative Web (2007). He gives his 'core' argument as:
  1. If moral facts do not exist, then epistemic facts do not exist.
  2. Epistemic facts exist.
  3. So moral facts exist.
  4. If moral facts exist, then moral realism is true.
  5. So moral realism is true.
Cuneo sees his first premise as the most crucial (but defends premise 2 as well). The thrust of Cuneo's argument is that the epistemic facts have all the features that anti-realists think are problematic for moral facts. Cuneo defines epistemic facts as "facts to the effect that something has some such property as being justified or irrational or insightful or a case of knowledge." (Lenman 2008) He thinks there is a parity, for example, between the intrinsic motivation of moral reasons and epistemic reasons. Similarly, he thinks epistemic facts face the same apparent intractability of disagreement that morality supposedly faces. James Lenman's review is an excellent primer for Cuneo (2008).
For more of these arguments, see Russ Shafer-Landau's Moral Realism: A Defence (2003). This review is also a good primer (Lillehammer 2003). We will continue to use Cuneo's argument as a surrogate for all Companions-in-Guilt arguments.
The Anti-Realist has two options: argue against premise 1 or argue against premise 2. Rejecting premise 1 seems the most likely: claim that epistemic facts and moral facts are sufficiently different. Perhaps, for example, that moral facts supervene in a way that epistemic facts don't. However, some people take on the freakish task of denying 2. Bart Streumer's Unbelievable Errors: An Error Theory about All Normative Judgements takes on the self-admittedly insane position that there are no normative truths (Streumer 2017). Again, we have a review from Notre Dame by none other than Koons (Koons 2017). Streumer seems to think that this position is functionally impossible to hold, but none the less plausibly true. He has a forthcoming paper Why We Really Cannot Believe the Error Theory which builds upon this claim (Streumer 2013 & Streumer forthcoming).
What conclusions can we draw from this? It seems like there is debate over whether moral realism is in fact intuitive, despite the common answer being that it is the default position. I also introduced us to a species of argument. These arguments seem good, at least to me, and the most likely premise to attack is that these companions are no companions at all! Otherwise, we see forced into the unenviable position of rejecting all normativity.

Moral Anti-Realism: Error Theory

Moral Error Theory is a view that should be fairly easy for this community to understand: it is the same as the view that atheists take towards the existence of god, or the view most people take towards astrology. The moral error theorist doesn't believe in morals: no obligations, no values, virtue, or permissibility. Sometimes this is understood by saying that all moral claims are false, or that we systematically make false claims when talking about morality. Error Theory is both cognitivist and anti-realist.
This position is most famously held by J. L. Mackie, who 'invented' error theory with Ethics: Inventing Right and Wrong (1977).
Let's have a look at the claim: "What Hitler did was morally wrong." The Error Theorist denies this. However, they also deny "What Hitler did was morally right." They deny any kind of moral claim about the goodness, wrongness, badness, rightness or permissibility of a person or action. We still allow the Error Theorist to hate the Nazis and to hate Hitler - they can still oppose Hitler. But they cannot claim to do so because of moral judgments (Joyce 2015).
Mackie provides two famous arguments for Error Theory:
  1. Argument from Moral Queerness
  2. Argument from Moral Disagreement
We have seen claims like the Argument from Moral Queerness already, so we will begin with that. This, explicitly, is an argument that Cuneo was rebelling against. The Argument from Moral Queerness makes two claims: moral facts, if they existed, consist of weird properties. These weird properties, the second claim says, would have to be understood through a weird mechanism. Put differently: "(A) that morality is centrally committed to some thesis X, and (B) that X is bizarre, ontologically profligate, or just too far-fetched to be taken seriously..." (Joyce 2016).
I think we have good reasons to think that the Argument from Moral Queerness is not very effective. Most arguments we are going to discuss argue themselves to be in the position of defending perfectly normal or intuitive views. We might see Queerness in Plato's views, or even in some non-naturalisms. We do not see it uniformly.
The second argument is the Argument from Moral Disagreement. This is an argument that appears frequently on the subreddit despite being not very good. This argument goes that there is widespread disagreement on what our morals are and ought to be. This disagreement, unlike most disagreement, is intractable. Take two cultures with two different values. The realist will claim that they have different access and therefore come to form different beliefs. Some of these beliefs are false. Mackie argues it just makes more sense to say their moral beliefs result from their cultural and anthropological heritage. They do not have different access; they just have different (never true) beliefs.
The Argument from Moral Disagreement has been criticised heavily. There are three strands of criticism: (1) that the disagreement part of moral disagreement is heavily exaggerated. If we polled people what would they say their morals comprise? Presumably that theft is often bad, as is murder. They might emphasize the family; on happiness and on fairness. If we take these to be moral claims, then it seems there is widespread agreement! (2) Disagreement doesn't seem to have weight on the truth of the matter. If you lacked the tools to calculate the shape of the Earth, but I had them, we would not conclude that the shape of the Earth is unknowable or nonsense. Finally, (3) cultures do not seem to have equal epistemic access. As cultures progress their values align; why would we think that Mackie is right in his assessment that his view is more parsimonious when we seem to experience moral progress with increased epistemic access?
Moral Error Theory is not a particularly popular position. The arguments for it have come under heavy criticism. That said it remains an important position in the history of meta ethical philosophy. Newer types - updated versions of Mackie's original formation - might be able to deal with these criticisms better.

Moral Anti-Realism: Non-Cognitivism & Emotivism

Earlier I introduced both Cognitivism and Non-Cognitivism. In this section, I will explain where arguments seen elsewhere fit into Non-Cognitivism, and then talk about a popular brand of Non-Cognitivism: Emotivism.
As a reminder: Non-Cognitivism is the view that that moral statements "moral statements have no substantial truth conditions. Furthermore, according to non-cognitivists, when people utter moral sentences they are not typically expressing states of mind which are beliefs or which are cognitive in the way that beliefs are. Rather they are expressing non-cognitive attitudes more similar to desires, approval or disapproval." (Richard 2016).
Mark van Roojen (2018) gives two more precise theses:
  1. Semantic Non-Factualism: moral statements do not express propositions or do not have substantial truth conditions
  2. Psychological Non-Cognitivism: the states of mind expressed by moral statements are not beliefs or other cognitive mental states.
van Roojen says that most Non-Cognitivists accept both theses. We have already seen two arguments that would motivate Non-Cognitivism because the two arguments for Error Theory can be applied here. Later we will see Moore's Open Question Argument. I will argue the Moral Naturalist as a response but if you find that response lacking then you have further motivation towards Non-Naturalism or Non-Cognitivism (rather than there being non-natural properties the Non-Cognitivist could conclude there are no moral properties at all.)
A. J. Ayer gives a general argument for Non-Cognitivism:
  1. The Verification Principle: a synthetic proposition (Any proposition whose truth depends on the relationship between the content of the proposition and the world is labelled Synthetic) is truth apt only if it is empirically verifiable. All meaningful propositions are analytic (true by definition) or are empirically verifiable.
  2. We cannot translate ethical statements into statements of empirical fact, no natural reduction of ethical concepts is possible. So, they are not empirically verifiable.
  3. Ethical statements are synthetic, not analytic.
  4. Non-Cognitivism: Therefore, ethical statements are not literally meaningful, and can be neither true nor false. (Ayer 1996) (Markovits 2009)
As we will see later some people deny 1, 2, and 3. I won't say much more in this because we will encounter these premises (and defences thereof) later!
There are many types of Non-Cognitivism but we are going to focus on one popular type: Emotivism.
Emotivists say that moral judgements are emotional expressions of one's approval or disapproval of some action or person. For example, when I say "Murder is bad" what I mean is that I am angry at murder or murder makes me go "eww". There are very famous emotivist texts: A. J. Ayer's Language, Truth and Logic (1936) and Charles Stevenson's Ethics and Language (1945). The SEP functions offer two biographical primers. Here's Ayer's link and here's Stevenson's link.
The Emotivist then adds that they think that ethics is best explained as emotions. Let's examine the supposed intractability of ethical disagreement. Do we not think this is the same for emotional expressions? The Emotivist says yes, and then would run through other qualities.
In this section, I have described Non-Cognitivism and one sort of Non-cognitivism. We have sparce on the detail of the criticisms. We will see a criticism of premise 2 with our next position: Moral Naturalism!

Moral Realism: Moral Naturalism

Moral Naturalism is a Moral Realism. Broadly, a moral naturalist thinks that morality can be explained within a naturalist framework. In this section I'm going to introduce a broad moral naturalism before talking briefly about Neo-Aristotelean Naturalism. In the following sections, I will talk about the two most common arguments. against Moral Naturalism in two mini-sections.
Moral Naturalists are often taken to be making three claims:
  1. Metaphysical Naturalism: Moral Facts are natural facts where natural facts are those kinds of facts that scientists study.
  2. Epistemic Naturalism: We come to know moral facts the same way we come to know other natural facts.
  3. Analytic Naturalism: Our moral claims are synonymous with certain claims in the natural sciences. (Lutz & Lenman 2018)
3 is unnecessary and can be in contrast with 2. The central claim is Metaphysical Naturalism.
In a more layman friendly way: Moral Naturalists think that (1) moral facts exist and (2) moral properties are reducible to natural properties.
There are a few reasons to like Moral Naturalism. The first is that it fits nicely into two frameworks with broad support. If one was compelled by some arguments from realism, and was compelled by naturalism, then Moral Naturalism seems like a natural fit. It also seems to make sense of some of the criticisms we've seen of realism already: if moral properties are natural properties we have no reason to think they are queer, for example. The second is that Moral Naturalism seems to enjoy a lot of support by contrast. We've seen the arguments for Non-Cognitivism and some for Anti-Realism. For many, these arguments fail. As we will see later, Non-Naturalism also has problems. This is not to say that Moral Naturalism has no counter-arguments, but it does seem to enjoy the status of being the least immediately problematic position (Lutz & Lenman 2018).
There are three popular accounts of Moral Naturalism: Neo-Aristotelian, Cornell Realism and Moral Functionalism. For an example of a Moral Functionalism, see Frank Jackson's From Metaphysics to Ethics: A Defence of Conceptual Analysis (Jackson 1998) and Stephen Finlay's Confusing Tongues: A Theory of Normative Language (Finlay 2014). For an example of Cornell Realism, see Richard Boyd's How to Be a Moral Realist (Boyd 1988), David Brink's Externalist Moral Realism (1986), Railton's Moral Realism (1986), and Nicholas Sturgeon's Moral Explanations (1985). The account I will focus on, Neo-Aristotelianism, has many contemporary proponents: Foot, Hursthouse, Nussbuam, MacIntyre and Thomson are all examples. I will focus on Hursthouse's:
Essential to Aristotle is that all things have a telos; or nature. Let's use the most common example in ethical philosophy. What is it that makes a knife a good knife? Well, its ability to cut cleanly and its sharpness. A bad knife is a knife that is bad at cutting. Aristotle thinks we can expand this account to humans: what makes a good human? Aristotle thinks a good human is one that performs their function (Richard 2018) and that function is dictated by our telos, or nature (Richard 2018 & Lutz & Lenman 2018)
Hursthouse thinks there are (at least) 4 parts of the human telos:
  1. Survival
  2. Reproduction
  3. Characteristic and Systematic Enjoyment & Freedom from Pain
  4. The Good Functioning of the Social Group (Hursthouse 1999)
Hursthouse thinks that evaluating humans qua their natural kind is different from evaluating leopards, or elephants of bees. Hursthouse writes:
But in virtue of our rationality—our free will if you like—we are different. Apart from obvious physical constraints and possible psychological constraints, there is no knowing what we can do from what we do do, because we can assess what we do do and at least try to change it. Suppose that, as far as human ethology goes, human beings do have a ‘characteristic’ way of going in for the sustained protection and nurturing of their young—the biological mothers of the offspring do it. Thereby human beings resemble a large number of other species in which (to coin a phrase) stepfatherly nature bears much harder on the females than it does on the males. With those other species, this is (unless we are mad enough to interfere) necessarily so, but with us it is not, and it has been one of the most illuminating aspects of feminism that it has made us see this. It is in the nature of things—in the nature or ‘essence’ of cheetahs and thereby of female cheetahs—that, speaking anthropocentrically, female cheetahs are bound to have a rotten life in comparison with male cheetahs. Part of what feminists are after, and right about, when they deny ‘essentialism’, is that, for us, it is not in our nature or essence that female human beings are bound to do whatever they have, so far, done. We can do otherwise. Our concepts of ‘a good human being’ and ‘living well, as a human being’ are far from being completely constrained by what members and biologically specialized members of our species actually, or, at the moment, typically, do; we have room for the idea that we might be able to be and to live better. (Hursthouse 1999 p.222)
Our rationality, which is taken as characteristically human, can alter the basic naturalist structure into a more complex naturalist structure.
Our next two sections are going to be Hume's Guillotine and Moore's Open Question. These famous arguments each claim to defeat Moral Naturalism. I am giving them a special amount of attention because Moral Naturalism is a widely held view, and these arguments are often taken as evidence for other views.

Against Moral Naturalism: The Is-Ought Gap

David Hume argued that ethicists often make claims about what is the case and wrongly infer from those what ought to be the case (Hume 1739). There is a jump in logic, and in value, going from a state about what the world is like, or what is the case, and inferring from that what we ought to do. There is, then, a category error in jumping from a descriptive state to an evaluative fact.
The argument goes that the moral naturalist has jumped from what the natural facts are to what the moral facts are. I don't think this criticism is particularly good and I'm going to give two very quick responses:
  1. Deny the Category Error
  2. Deny the Gap
Alistar MacIntryre, in After Virtue, argues for the telos account we've seen above in Hursthouse and Aristotle (MacIntyre 1981). He sees the Is-Ought Gap as posing no real problem:

  1. If there exists a human telos, then a good human can exist
  2. There exists a human telos.
  3. A good human can exist.
The goodness of any person is measured against that telos. It seems no more fallacious to say what a good human is than it is fallacious to say that a good knife should cut or a good TV needs to be able to turn on. We might even think we don't need to introduce "oughts" at all here.
Philippa Foot denies the gap via an analogy with rudeness. Foot thinks that "rude" is evaluative. But she thinks it can be derived from a description: that x causes offence by indicating a lack of respect. If that definition is true, can one deny that it is rude? If she is correct and the answer is no then one has derived an ought from an is! (Foot 1958 & IEP)
Both counters have been countered and developed to deal with those counters. Right now, I only want to introduce them. The second objection is Moore's Open Question.

Against Moral Naturalism: Moore's Open Question

Moore's Open Question goes as follows:
If X is analytically equivalent to the good, then the question "Is it true that X is good?" is closed and therefore silly or meaningless.
  1. The question "Is it true that X is good?" is an open not silly or meaningless since it is an open question.
  2. X is not analytically equivalent to the good.
Let's illustrate with Socrates and his morality. Let's say that Socrates is a man and all men are mortal. Given that, it seems a meaningless question to ask "I know Socrates is a man and all men are mortal, but is Socrates immortal?" Moore proposes that since all questions about moral goodness are open, then moral goodness cannot be reduced to an analytical equivalent (which is what moral naturalists claim to be doing) (Moore 1903).
While Moore uses this to motivate a Non-Naturalism, the Open Question also supports a Non-Cognitivism.
For a brief while, it seemed as though Moore had ruined moral philosophy. However, I want to give two reasons why we should reject Moore's conclusions:
  1. The Open Question Overreaches
  2. The Open Question Isn't Open
Michael Smith argued that the Open Question's true would mean that modern philosophy is scuppered (Smith 1994). Smith thinks that if our metric is to ask if the question is obviously closed then it seems like the only things we could ever prove are propositions of the same triviality as "all bachelors are unmarried, John is a bachelor, and therefore John is unmarried." Smith thinks that if we allow for analytic truths to be non-obvious, then the appearance of openness is insiginifcant.
The second argument is put forward by Finlay: Finlay argues that the Open Question isn't open at all. Instead, it only appears open (Finlay 2014). If goodness can be analysed, the question is going to appear open for all answers that aren't correct (so an infinity of answers, minus one) and it might even appear (as we have seen with Smith) to be an open question even if it is actually closed. Finlay continues that the only way to know if X is good is to analyze it, and not to ask about how the question feels.
I see both of these are successful. I find Finlay particularly successful. However, you might not. Moore himself argued for our next view: Moral Non-Naturalism.

Moral Realism: Moral Non-Naturalism

In this section, my goal is to give a terribly broad definition of Non-Naturalism before analysising Moore's specific account of Non-Naturalism. I will not highlight any criticisms because I think the motivation for Moore is the success of the Open Question. How much you like Moore's account will depend on how strong you take previous arguments in this post to be.
If Moral Naturalism can be understood as the claim that moral facts are natural facts understandable and discoverable through the natural sciences, then Moral Non-Naturalism is the claim that moral philosophy is autonomous from the natural sciences (Ridge 2019). Giving a more precise definition is problematic since non-naturalism covers a wide array of views. Some are primarily epistemology, some metaphysical.
I am going to examine G.E Moore's account of Moral Non-Naturalism. Moore's account has two claims:
  1. Moral Realism
  2. Moral Facts are sui generis (sui generis just means "Of its own kind" so moral facts are in a class of their own) (Moore 1903).
As I said earlier, Moore's position seems motivated by two things: the success of moral realism and the failure of moral naturalism (Hurka 2015). If you agree on both counts, then Moore is likely someone who you think has a good position.

Common Meta-Ethical Views in the Subreddit

That's the main body of work done. The last thing to do is to run through three positions that we often encounter in the subreddit. I am going to run through
  1. Divine Command Theory
  2. Moral Non-Objectivism/Subjectivism
  3. Moral Relativism
I am going to explain these positions within the context of the positions we have already discovered.

Common View #1: Divine Command Theory

Divine Command Theory is the view that morality depends on God, and that our moral obligations are to follow God's Commands. Divine Command Theory has enjoyed a long history of support. In fact, some philosophers think that without God we have to give up on moral oughts and instead move onto moral virtue (Anscombe 1958).
There are different versions of Divine Command Theory, but I take most to say something along Edward Wierenga's lines:
  1. God determines what is moral
  2. 2. Moral obligations are derived from God's Commands
I understand Divine Command Theory to be a kind of Moral Non-Naturalism. It is therefore a Moral Realism that holds that moral facts are not reducible to natural facts. One might call Divine Command Theory a Moral Supernaturalism.
There are some advantages to Divine Command Theory, but those advantages are not unique to it over any other realisms - although some theorists claim that Divine Command Theory is the only way to make sense of why we ought to do the good which is something they claim that naturalism suffers in doing. I want to put the motivations to one side for this post. Instead, I want to talk briefly about the Euthyphro Dilemma.
The Euthyphro Dilemma asks “Does God command this action because it is morally right, or is it morally right because God commands it?” If it is the first, it must worry us that there is a good independent of God and God merely recognises that God. Having a good external to God poses problems for most monotheisms. The second horn leaves open the possibility that cruelty could be morally right if God commanded it.
I think the Divine Command Theorist has some good responses: they can bite the bullet and admit that God could command cruelty (but never would) or they can insist on a telos. The IEP entry on this is excellent. Link here.

Common View #2: Moral Relativism/Moral Subjectivism

These make sense to talk about together in how we understand the terms and then come apart with how we motivate them. In this section, I'll define both and distinguish them from a non-cognitivism. I want to end by saying Moral Subjectivism is a confusing, and perhaps necessarily confused, discussion.
Moral Relativism claims that moral claims are indexical: that any moral truth claim requires that you relativize it to a group or individual. The index can change from individual to group (think mafia) to culture, to perhaps something larger. This relativism is minimally realist: moral propositions are truth apt. It might even be the case that they are mind-independent. Joyce offers a comparison to tallness. Michael Jordan' is not made tall by thinking that Michael Jordan is tall, but his tallness is still relative to the culture he exists in. (Joyce 2016)
Moral Subjectivism, contrasted to relativism, is mind dependent. These two views are distinct but not mutually exclusive. Joyce talks about the difficulty of extracting meaning from "mind-(in) dependent" used as a coarse grain term. (Joyce 2016) Moral Subjectivism seems to be a moral realism because it does posit moral facts, and it seems to be a cognitivism. Most Subjectivists, even on this subreddit, don't seem to think moral propositions report an attitude: they seem to think they report a belief.
But this isn't so clear cut: some people take subjectivism to be a non-cognitivism. I think this is a view we see on the sub. It needn't be that way though.
There are two points to take from Joyce:
  1. So many debates in philosophy revolve around objectivity versus subjectivity that one may be forgiven for assuming that someone somewhere understands this distinction. Joyce is right in saying that if you ask 10 people what they give different accounts, and in fact the definition I've given above suffers from this problem.
  2. That said, subjectivism needn't be a "silly" position: subjectivity needn't be so as unsophisticated that it can't make sense of moral progress or disagreement. It needn't be analogous to taste.
So, while relativism seems fairly easy to understand, moral subjectivism seems comparatively difficult. My advice here is to give a full account of moral subjectivism when you talk about it!


That is all! There are a few positions missing. For people who are familiar with the topic, you’ll notice that Constructivism is strangely absent. If this post is successful, I might talk about Constructivism. I would also like to talk about Moral Intuitionism. There are also arguments I would have loved to include: I want to talk about Evolutionary Debunking Arguments, but I’m saving that for a post where I defend a Moral Naturalism.
This isn’t every position that you’re going to see in meta-ethics, nor is it a complete account of the positions we have seen. What it should be, however, is a rapid-fire introduction to meta-ethics. I hope that this will help interested parties come to understand their own views a little better, and come to understand the views of others a little better. Hopefully, it also makes the case that none of these positions are ever trivially true or false–argumentation, often good argumentation, surrounds these positions.
submitted by NietzscheJr to DebateReligion [link] [comments]

2020.10.21 14:34 samspopguy Importing into a MSSQL database

I originally had this importing into a mondodb database and two collections but having an issue with the reporting needs I had so I switched to MSSQL so I could use SSRS, but now im having an issue on the import. I didn't copy the rest of the code because the error keeps happening on the cursor.executmany. and the error is
TypeError: ('Params must be in a list, tuple, or Row', 'HY000')
I'm assuming the error is the way the data is parsing to an array for a mongodb/document based import rather then a relational database type import
importncdrdata = '''INSERT INTO ncdr.dbo.ncdrdatatest (Hospital,MetricKey,LineText,Year,Quarter,YrQtrRange,qtrnum,qtrden,qtrpercent) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)''' importvolgroup = '''INSERT INTO ncdr.dbo.ncdrvoltest (Hospital,MetricKey,LineText,Year,Quarter,YrQtrRange,rqtrnum,rqtrden,rqtrpercent,volnum,volpercent) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,)''' def importncdrfile(): # Open the workbook and select the first worksheet wb = xlrd.open_workbook(self.filename) sh = wb.sheet_by_index(1) # Function to import main set of data if yearqtrid.get() == "2019Q3": def excelToDict(hospital, metricKey, lineText, year, quarter, yrQtrRange, num, den, percent): for rownum in range(12, sh.nrows): ncdr = OrderedDict() row_values = sh.row_values(rownum) ncdr['Hospital'] = hospitalchosen.get() ncdr['YrQTRid'] = yearqtrid.get() ncdr['MetricKey'] = row_values[metricKey] ncdr['LineText'] = row_values[lineText] ncdr['Year'] = year ncdr['Quarter'] = quarter ncdr['YrQtrRange'] = yrQtrRange ncdr['qtrnum'] = row_values[num] ncdr['qtrden'] = row_values[den] ncdr['qtrpercent'] = row_values[percent] ncdr['SubGroup'] = 'holder' ncdr_list.append(ncdr) # List to hold dictionaries ncdr_list = [] # Imports data into MongoDB takes the aboce code and then runs the function against each set of data and then repeats for all 4 Quarters excelToDict(n,0, 1, 2018, 4, 20184, 2, 4, 6) excelToDict(n,0, 1, 2019, 1, 20191, 8, 10, 12) excelToDict(n,0, 1, 2019, 2, 20192, 14, 16, 18) excelToDict(n,0, 1, 2019, 3, 20193, 20, 22, 24) cursor.executemany(importncdrdata, ncdr_list) #x = importncdrdata.insert_many(ncdr_list) # function to import volume group and rolling quarters def excelToDictvol(hospital, metricKey, lineText, year, quarter, yrQtrRange, rqtrnum, rqtrden, rqtrpercent, volnum, volpercent): for rownum in range(12, sh.nrows): ncdrlist = OrderedDict() row_values = sh.row_values(rownum) ncdrlist['Hospital'] = hospitalchosen.get() ncdrlist['YrQTRid'] = yearqtrid.get() ncdrlist['MetricKey'] = row_values[metricKey] ncdrlist['LineText'] = row_values[lineText] ncdrlist['Year'] = year ncdrlist['Quarter'] = quarter ncdrlist['YrQtrRange'] = yrQtrRange ncdrlist['rqtrnum'] = row_values[rqtrnum] ncdrlist['rqtrden'] = row_values[rqtrden] ncdrlist['rqtrpercent'] = row_values[rqtrpercent] ncdrlist['volnum'] = row_values[volnum] ncdrlist['volpercent'] = row_values[volpercent] ncdrlist['SubGroup'] = 'holder' ncdr_vollist.append(ncdrlist) ncdr_vollist = [] excelToDictvol(n,0, 1, 2019, 3, 20193, 26, 28, 30, 32, 34) #v = importvolgroup.insert_many(ncdr_vollist) cursor.executemany(importvolgroup, ncdr_vollist) 
submitted by samspopguy to learnpython [link] [comments]

2020.10.21 02:38 ChristmasAllYear I don't understand the mechanics of this formula (dynamic unique value extraction)- see link - would appreciate a ELI5
Creating a financial bottom up and needed one small feature (having a column list out unique values on another column..but keeping it dynamic for any changes).
This formula provided worked perfectly for the functionality I needed but I'm confused as to how it ended up working)
=[IFERROR(INDEX($C$7:$C$500, MATCH(0,COUNTIF($H$6:H6, $C$7:$C$500), 0)),"")]
One item I will need to look further into is array, but overall confused on how this formula works and achieves what I wanted !
submitted by ChristmasAllYear to excel [link] [comments]

2020.10.18 04:08 notfoursaken I've been playing with Power Query for a week and I can't figure out how I survived prior to this.

It's like I've gone from using candles and oil lamps directly to smart bulbs connected to HomeKit/Alexa/Google/etc. In my opinion, this is Excel's biggest power feature that all users should learn.
In my line of work, we implement our company's private equity accounting software for private equity fund managers who want to do accounting in house on a proper Accounting Information System. We show them how to populate import templates with transactions and we then load those into the system and the client reconciles back to their financials. There's a lot of room for error on their side while populating. In the past few days, I've used PQ to get a list of all available transactions (and their GL entries) from the system by querying the db directly. We manually entered the chart of accounts and created the transactions, so there was room for error. I sent that resulting query to the controller for her to approve. I've merged that with a table of the import template prepared by the client, and then I essentially made a Trial Balance showing them what the balances will be in each account if we load the import template as is. This has allowed us to catch a bunch of mistakes up front and correct them prior to loading.
When it comes to reconciling files, we have some existing clients that we're on the hook to reconcile. Our previous method of reconciliation has been a big mess of SUMPRODUCTS and INDEX/MATCH formulas that take forever and a day to load because of how huge the data arrays are (30 columns of transactions by 450 investors in the fund). Just yesterday I showed one of my team members how we can do this with PQ and she started seeing the value immediately. She's our resident VBA expert and she started dabbling in R recently with a friend, but as we walked through how to set up some basic queries to 1) query our report, 2) query client's report and 3) merge them and subtract our value from their value to find differences, she started going nuts at how awesome and quick this was.
As I build more and more of these efficiencies, I'm going to make the case to my director of why we should be able to have read-only access to the production databases. As it stands currently, if I want to query the db directly, I first have to have IT pull a copy down to our test servers. Waste of time. There's value in our ASP team granting us read-only access to prod, in my opinion.
Apologies for the rambling walls of text. I'm super excited about PQ and I'm sure I'll have more questions in the future as I discover new use cases for our team to solve.
submitted by notfoursaken to excel [link] [comments]

2020.10.15 19:32 jbowie "LET" Function replacing some UDFs

I've been working with the new LET function in Excel, and I think it has strong potential to replace some VBA UDFs. For example, a problem that I have to work with often is Linear Interpolation of data. While this can be done using only native excel functions, it either requires a few helper columns, or a single fairly complicated formula. The irritating part of the complicated formula is that each input reference is used in multiple parts of the formula, so even copy/pasting from another sheet takes a while to modify for a new spot. I typically used a UDF to simplify this, but that comes with performance issues, and it doesn't play nicely with the new Dynamic Array engine.
Using the LET function allows for putting all of the inputs at the start, then using the same formula for the main calculation. This means that you can paste in the formula, then only modify each input reference once. As well, you do not need to use each of the names that you define, so you can put a description in the formula. It also lets you do step by step calculations within the cell.
For example:
=LET(desc,"Linear interpolation, flat outside of range", x, C1, xrange, A1:A5, yrange, B1:B5, lowindex, XMATCH(x,xrange,-1,1), highindex, XMATCH(x,xrange,1,1), xlow, INDEX(xrange,lowindex,1), xhigh, INDEX(xrange,highindex,1), ylow, INDEX(yrange,lowindex,1), yhigh, INDEX(yrange,highindex,1), IFS(x=xlow,ylow,ISERROR(xhigh),ylow,TRUE(),ylow+(yhigh-ylow)/(xhigh-xlow)*(x-xlow))) 
performs linear interpolation quickly and simply within a single cell. It's also easier to troubleshoot and maintain than a system with multiple helper columns, especially when entered over multiple lines. Finally, it works as expected when using it in dynamic array formulas (i.e. it returns a whole array of values if the input x is an array).
I think it will have a huge impact on spreadsheet design, removing a lot of helper columns and removing the need for some UDFs.
submitted by jbowie to excel [link] [comments]

2020.10.14 13:23 imed92rd I'm getting 0 = -5.55111512312578E-17 for some reason..

New here! I'm currently developing my own matrix calculator that does elementary row operations automatically and does encoding of its Latex form like so [\matrix(1&0&0&0/1&1/[email protected]&1&0&0/1&0/[email protected]&0&1&0&3/[email protected]&0&0&1&2)] in MS Word. It basically does everything for me since row operations are tedious to do manually.
Link to the file:
Here's how I do it.
I just need to plug in the elements in 10X11 matrix right where cells are bordered in thick lines. Besides it is where I'm supposed to copy the input matrix's Latex syntax but currently I'm struggling with its formatting. Anyway, below the matrix is where I'm plugging in values for row operations except row interchange which can be done manually. Each of the operations have their corresponding Latex syntax on the right.
Right here, a new matrix spits out below "ROW EQUIVALENT MATRIX" which comes with Latex syntax too. Way below is where I'm just copying the matrix for reference.
On this sheet I named "Process" is where behind the scenes happen. As you can see, letters skip because those columns contain ampersand "&" that is in 10 rows height. I'm using CONCAT() to produce textstring in each row of the Input Matrix for Latex syntax. For example, the 2nd row of the matrix has elements 1,0,0 which means I'll need to get "1&0&0" from it. Every elements in my input maps perfectly fine since I'm using OFFSET() that is hidden in row1. All empty cells in my original input is empty when mapping to "Process" sheet happens.
After this is where I'm having serious headaches.
This is the initial phase of calculations. Remember how we're supposed to reduce a matrix in Row Echelon Form? Well, I need leading one first for the first row. At the left side of the first "R" is where the multiplier that I want for that row (which is mapped from the former sheet perfectly fine). This is the formula in each cell:
=IFS( ISBLANK(A2),"", AND( ISNUMBER(A2),OneorZero=0,RowN=ROW()-14 ), MultiplierA2,AND( ISNUMBER(A2),OneorZero=0,RowN<>ROW()-14), A2, AND( ISNUMBER(A2),OneorZero=1,RowM=ROW()-14), MultiplierINDEX(A$2:A$11,RowN) + A2, AND( ISNUMBER(A2),OneorZero=1,RowM<>ROW()-14 ), A2 )*fin* 

The first logic is based on the cell above with the same position as this cell in this matrix. It checks if it has anything or nothing. It overrides all other logic tests. The other form processes the cell with an input or element.
Second and third logic tests deals with simple 'multiplying a row' operation - so no adding involved. The parameter to be expected is OneorZero = 0, "OneorZero" refers to the left side cell of the second "R". RowN is the row that I want to multiply with. So, I need a parameter involving rows which requires the row of the current cell to be equal to RowN. The third logic only tests if the row of a cell is not equal to RowN.
The last two deals with 'adding a multiple of a row to another row' operation which is determined by OneorZero=1. That's why I need RowM, the row that I will add a multiple to, be equal to the current row of a cell. Now there's an INDEX() function to get the cell on the input Matrix that belongs to RowM, whatever value that is, and on the same column number as the current cell.
The cell range in red is where I'm getting the element to be multiplied
This is the "fun" part:
It was supposed to do one row operation at a time. I'm super lazy so I need to built it up more than necessary. The next steps 'till the tenth matrix follows the same formula below:

=IFS( A15="", "", AND( ISNUMBER(A15), INDIRECT("Multiplier"&$W28)=0 ), A15, AND( ISNUMBER(A15), INDIRECT("Multiplier"&$W28)<>0, INDIRECT("OneorZero"&$W28)=0, INDIRECT("RowN"&$W28)=ROW()-(14+13*($W28-1)) ), INDIRECT("Multiplier"&$W28)*A15, AND( ISNUMBER(A15), INDIRECT("Multiplier"&$W28)<>0, INDIRECT("OneorZero"&$W28)=0, INDIRECT("RowN"&$W28)<>ROW()-(14+13*($W28-1)) ), A15, AND( ISNUMBER(A15), INDIRECT("Multiplier"&$W28)<>0, INDIRECT("OneorZero"&$W28)=1, INDIRECT("RowM"&$W28)=ROW()-(14+13*($W28-1)) ), INDIRECT("Multiplier"&$W28)INDEX(INDIRECT(ADDRESS(2+(13($W28-1)),COLUMN(),2)&":"&ADDRESS(11+(13*($W28-1)),COLUMN(),2)), INDIRECT("RowN"&$W28)) + A15, AND( ISNUMBER(A15), INDIRECT("Multiplier"&$W28)<>0, INDIRECT("OneorZero"&$W28)=1, INDIRECT("RowM"&$W28)<>ROW()-(14+13*($W28-1)) ), A15 ) *fin* 
They do the same thing as before but needs extra step. Note that the italicized ones are the process to do once the conditions are met.
"A15="", "", "just checks if a cell on the previous matrix (after the first row operation) is empty or not.The other four processes the cell that is not empty.
"AND( ISNUMBER(A15), INDIRECT("Multiplier"&$W28)=0 ), A15," is an extra step. Basically my parameter to terminate an operation is the multiplier, each named Multiplier2, Multiplier3, Multiplier4,..., and Multiplier10 to correspond to the 2nd, 3rd, 4th,..., and 10th operation. It's necessary for mapping the resulting matrix to the first sheet. How? All's needed is 0's throughout the operations that do not do anything.
Then, the rest are operations to be done which is tested by "INDIRECT("Multiplier"&$W28)<>0,"."INDIRECT("Multiplier"&$W28)", "INDIRECT("OneorZero"&$W28)", "INDIRECT("RowN"&$W28)", and "INDIRECT("RowM"&$W28)" references a cell named with OneorZero2 or Multiplier2, for example. The number only corresponds to the nth operation (2 to 10).

C26=\"Multiplier2\", I26=\"RowN2\", M26=\"OneorZero2\", S26=\"RowM2\"
"INDIRECT("OneorZero"&$W28)=1," is the same in function as "OneorZero=1" and "INDIRECT("RowM"&$W28)=ROW()-(14+13*($W28-1))" to "RowM = ROW() - 14" previously. "ROW()-(14+13\($W28-1)"* is analogous to this, x-(14+13[n-1]) where n is the current number of row operation.

I have to explain this one:
INDIRECT("Multiplier"&$W28)*INDEX(INDIRECT(ADDRESS(2+(13*($W28-1)),COLUMN(),2)&":"&ADDRESS(11+(13*($W28-1)),COLUMN(),2)), INDIRECT("RowN"&$W28)) + A15, 

This is the same as this in function:
Multiplier*INDEX(A$2:A$11,RowN) + A2 

INDEX( INDIRECT( ADDRESS( 2+(13*($W28-1)),COLUMN(),2 ) & ":" & ADDRESS( 11+(13*($W28-1 )),COLUMN(),2 ) ) 

is analogous to this:
Okay.... I guess I've explained what's needed. Back to the main problem.

1 0 0 0 1
0 1 0 -0 -0
0 0 1 0 3
0 0 0 1 2

Above is the REF matrix I got from the following matrix:
1 1 1 1 6
2 3 0 -1 0
-3 4 1 2 4
1 2 -1 1 0
For some unknown reason, I'm getting a value that is supposed to be zero, -5.55111512312578E-17. This is where I am currently:

1 0 0 -1/3 1/3
0 1 0 -1/9 -2/9
0 0 1 1 4/9 5 8/9
0 0 0 3 6

I really hate dealing with fractions especially in matrices. Then, right here is where it started:
AND(ISNUMBER(G29),INDIRECT("OneorZero"&$W42)=1,INDIRECT("Multiplier"&$W42)<>0,INDIRECT("RowM"&$W42)=ROW()-(14+13*($W42-1))), INDIRECT("Multiplier"&$W42)*INDEX(INDIRECT(ADDRESS(2+(13*($W42-1)),COLUMN(),2)&":"&ADDRESS(11+(13*($W42-1)),COLUMN(),2)), INDIRECT("RowN"&$W42)) + G29, 

I've confirmed that the process should proceed here for this cell.
G42="", "", 




is TRUE; and

AND(ISNUMBER(G42),INDIRECT("OneorZero"&$W55)=1,INDIRECT("Multiplier"&$W55)<>0, INDIRECT("RowM"&$W55)<>ROW()-(14+13*($W55-1))), 

So, I pressed F9 right here:
I got 0.
But, at the same time, I got this by pressing F9 while this was highlighted.
I got -5.55111512312578E-17.
I double-checked everything before I'll get 0.
Indeed, TRUE

0.1111111111111111 approximates 1/9

The whole array of elements in the same column as the current cell in the previous matrix
And, curiously, I got a number vaguely similar to -5.55111512312578E-17.
I don't know if this is related to my issue but I'll put this out just in case. However, the logic remains to be consistent.

Results to 0.
I don't even know what's going on anymore. The rest falls apart when I'm producing its Latex syntax.

0/1 should be 0. 3/1 should be 3.
This is the formula:
=IFERROR( TEXT(A132, IF(ABS(A132)-ABS(TRUNC(A132))=0,"##0","##0/##0") ), "") 
I'm thinking that the formula perceives 3 and 0 as numbers with decimal digits that are non-zero.
I'm using Excel 2016 right now.
submitted by imed92rd to excel [link] [comments]

2020.10.09 04:10 oledawgnew Trying to use Named Array as List for Data Validation

Can someone explain why a named array can't be used for Data Validation, but if the array is spilled in cells the array's cell range can be used? My guess is that the Excel software engineers just haven't thought of using arrays as a data validation option. Seems like it would be an obvious choice to me and not too tough to code.
EDIT: I do appreciate the recommended solutions, but this post was originally posted as a discussion topic, not a problem looking for a solution.
The question was asked in different forms "why use, or did you intend to use, an array of month abbreviations?" The array is used in a rather detailed personal finance spreadsheet to search for tables and sheets using array functions as such: {IFERROR(MATCH(INDEX(Months,COLUMN(D:O)-3),MM!$A$5:$A$16,0),"")}. This particular array searches a table with monthly headings in sheet entitled "MM" (stands for Money Market) for monthly amounts and puts the values in a table on an different sheet.
I was hoping to use the Months array for data validation in drop drown boxes to select and compare returns for specific months. And since I was using the array in other ways I simply trying to reduce duplication of data structures.
The array was not created using a named range. In Excel you can create named Arrays (similar to how you create named ranges) by placing comma separated index values inside curly brackets. Maybe half-dozen in one basket and 6 in another, but for me this seemed to be a more creative solution than a named range.
Thanks for the different recommendations and discussion!
submitted by oledawgnew to excel [link] [comments]

2020.10.07 01:29 Blackjack357 How can I use VBA efficiently to compare feeder spreadsheets and make updates to a large master file? Is there a code cleaner to reduce unneeded code?

I’m relatively new to VBA I’ve been working to automate some of the things I do with Excel.
For my latest project, I’m working to create feeder spreadsheets that lead into a large master file and create a change log and an archive. I started on a small scale to test my theories and code, and it works when I’ve got a single feeder providing information to the master. The code I’ve written so far works exactly like I want it to, so I’m happy about that, but I haven’t added a second feeder yet because I reviewed my code today and realized I probably have a LOT of unnecessary things in there and that will only compound with the way I’m writing due to inexperience.
My master document currently has 87 columns and about 1000 rows. I’d like to break it down into sections / feeders to make navigation less cumbersome. Then compare the master document to the feeder and update the master with any information that is provided on the feeder, then create a change log for any information that changed and an archive to store any items that need to be removed from the master so i can trace back at a later date if necessary.
I expect to have at least five feeder spreadsheets, a master, an archive, and the change log, and later other reports and metrics based on the master file.
Currently I look at the feeder’s first row, find the corresponding row in the master, loop through each feeder column and individually compare each cell to the master, make a change if there is one, capture the change in the change log, move to the next column until I reach the end of the first row, then move to the next row and repeat until I get to the end of the table. Once I reach the end of the table, I look to see if there is a new item in one column, if so, I copy the entire row to the archive and delete the row from the master.
Is there a more efficient way to do this? I apologize I don’t have any code included, it’s currently on a work computer, if I need to include it to help with this please let me know and I’ll find a way to do so since I can’t email or access reddit on the work computer.
In action:
My current code:
Option Explicit Dim wbUpdates As Workbook Dim wsArchiveLog As Worksheet Dim wsChangeLog As Worksheet Dim wsMasterLog As Worksheet Dim wsPartUpdateLog As Worksheet Dim tblArchiveLog As ListObject Dim tblChangeLog As ListObject Dim tblMasterLog As ListObject Dim tblPartUpdateLog As ListObject Dim varArchiveLogRowLast As Variant Dim varChangeLogRowCount As Variant Dim varChangeLogRowLast As Variant Dim varChangeLogImpact As Variant Dim varMasterLogRow As Variant Dim varMasterLogRowCount As Variant Dim varMasterLogRowLast As Variant Dim varMasterLogCol As Variant Dim varMasterLogNewValue As Variant Dim varMasterLogOldValue As Variant Dim varPartUpdateLogRowCount As Variant Dim varPartUpdateLogRowLast As Variant Dim varPartUpdateLogColCount As Variant Dim varPartUpdateLogColLast As Variant Dim varPartUpdateLogInfoToChange As Variant Dim varPartUpdateLogPartNumber As Variant Dim varPartUpdateLogNewPartArray As Variant Dim varPartUpdateLogNewPartArrayCount As Variant Sub SetObjects() Set wbUpdates = Workbooks("Updates.xlsm") Set wsArchiveLog = wbUpdates.Sheets("Archive") Set wsChangeLog = wbUpdates.Sheets("Change Log") Set wsMasterLog = wbUpdates.Sheets("Master Log") Set wsPartUpdateLog = wbUpdates.Sheets("Part Feeder") Set tblArchiveLog = wsArchiveLog.ListObjects(1) Set tblChangeLog = wsChangeLog.ListObjects(1) Set tblMasterLog = wsMasterLog.ListObjects(1) Set tblPartUpdateLog = wsPartUpdateLog.ListObjects(1) End Sub Sub UpdateMasterLog() varMasterLogNewValue = tblPartUpdateLog.DataBodyRange(varPartUpdateLogRowCount, tblPartUpdateLog.ListColumns(varPartUpdateLogInfoToChange).Index) varMasterLogCol = tblMasterLog.Range.Find(varPartUpdateLogInfoToChange, , , xlWhole).Column varMasterLogOldValue = tblMasterLog.DataBodyRange(varMasterLogRow, varMasterLogCol) If varMasterLogNewValue <> varMasterLogOldValue Then tblMasterLog.DataBodyRange(varMasterLogRow, varMasterLogCol).Value = varMasterLogNewValue End If End Sub Sub UpdateChangeLog() If varMasterLogOldValue <> varMasterLogNewValue Then varChangeLogRowLast = tblChangeLog.Range.Rows.Find("*", , , , xlByRows, xlPrevious).Row With tblChangeLog .Range(varChangeLogRowLast + 1, tblChangeLog.ListColumns("Who").Index).Value = Application.UserName .Range(varChangeLogRowLast + 1, tblChangeLog.ListColumns("Action").Index).Value = "Changed PN: " & varPartUpdateLogPartNumber & ": " & varPartUpdateLogInfoToChange .Range(varChangeLogRowLast + 1, tblChangeLog.ListColumns("Original Value").Index).Value = varMasterLogOldValue .Range(varChangeLogRowLast + 1, tblChangeLog.ListColumns("New Value").Index).Value = varMasterLogNewValue .Range(varChangeLogRowLast + 1, tblChangeLog.ListColumns("Date").Index).Value = Date End With End If End Sub Sub UpdateArchiveLog() For varMasterLogRowCount = 1 To varMasterLogRowLast Step 1 varArchiveLogRowLast = tblArchiveLog.Range.Rows.Find("*", , , , xlByRows, xlPrevious).Row If Not IsEmpty(tblMasterLog.DataBodyRange(varMasterLogRowCount, tblMasterLog.ListColumns("New Part number").Index)) Then tblMasterLog.ListRows(varMasterLogRowCount).Range.Copy (tblArchiveLog.Range(varArchiveLogRowLast + 1, 1)) tblMasterLog.ListRows(varMasterLogRowCount).Range.Delete End If Next varMasterLogRowCount End Sub Sub CheckParts() varMasterLogRowLast = tblMasterLog.Range.Rows.Find("*", , , , xlByRows, xlPrevious).Row If tblMasterLog.ListColumns("Part Number").DataBodyRange.Find(varPartUpdateLogPartNumber) Is Nothing Then tblMasterLog.DataBodyRange(varMasterLogRowLast, tblMasterLog.ListColumns("Part Number").Index).Value = varPartUpdateLogPartNumber End If End Sub Sub ImpactChange() For varChangeLogRowCount = 1 To varChangeLogRowLast Step 1 If InStr(1, tblChangeLog.DataBodyRange(varChangeLogRowCount, tblChangeLog.ListColumns("Action").Index), "Price") > 0 Or InStr(1, tblChangeLog.DataBodyRange(varChangeLogRowCount, tblChangeLog.ListColumns("Action").Index), "LT") > 0 Then tblChangeLog.DataBodyRange(varChangeLogRowCount, tblChangeLog.ListColumns("Change Impact").Index).Value = _ tblChangeLog.DataBodyRange(varChangeLogRowCount, tblChangeLog.ListColumns("New Value").Index).Value - _ tblChangeLog.DataBodyRange(varChangeLogRowCount, tblChangeLog.ListColumns("Original Value").Index).Value End If Next varChangeLogRowCount End Sub Sub PartUpdates() Call SetObjects varPartUpdateLogRowLast = tblPartUpdateLog.Range.Rows.Find("*", , , , xlByRows, xlPrevious).Row varPartUpdateLogColLast = tblPartUpdateLog.Range.Columns.Count For varPartUpdateLogRowCount = 1 To varPartUpdateLogRowLast - 1 Step 1 varPartUpdateLogPartNumber = tblPartUpdateLog.DataBodyRange(varPartUpdateLogRowCount, tblPartUpdateLog.ListColumns("Part Number").Index).Value Call CheckParts varMasterLogRow = tblMasterLog.DataBodyRange.Find(varPartUpdateLogPartNumber).Row - 1 'I just realized this needs to change to only search the first column For varPartUpdateLogColCount = 2 To varPartUpdateLogColLast Step 1 varPartUpdateLogInfoToChange = tblPartUpdateLog.DataBodyRange(0, varPartUpdateLogColCount).Value Call UpdateMasterLog Call UpdateChangeLog Next varPartUpdateLogColCount Next varPartUpdateLogRowCount Call UpdateArchiveLog Call ImpactChange End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Call PartUpdates End Sub 
submitted by Blackjack357 to vba [link] [comments]

2020.10.02 15:21 casinelli26 Test Excel Knowledge in Interview

Hello excel!
I am going to be interviewing candidates for a Sr. Financial Analyst to work along side me. I am wondering, what's the best way to test someone's Excel knowledge as this job requires intermediate to advance Excel skills. Examples of the work I do requires Power Query, pivot tables, working with linked files, running VBA Macros, rolling over old array formulas, then your typical sum-if(s), vlookups, index-match, etc. This is my first time interviewing someone so it will be a learning process for me.
Thank you.
submitted by casinelli26 to excel [link] [comments]

2020.09.28 08:20 fysiotherapieplint Foot Posture as well as Positioning

Our feet are incredible machines, managing without grievance most of the time to send the weight of the body as we drive ourselves around at varying speeds, all the while maintaining equilibrium as well as a protected connection with the surface. While foot pose can vary fairly significantly without particular painful troubles, numerous changes in foot pose can result in uncomfortable signs and symptoms and substantial degrees of disability. The degrees of force transferred with the feet require solid and also adaptable joints, muscular tissue stamina as well as neuromuscular coordination for balance and also control.
The adjustments which take place in the feet can be postural as well as associate with the positions of the physiological frameworks in weight bearing, or be dynamic because the postural irregularity happens fleetingly however regularly in strolling or running. The rear of the foot, the back foot, is commonly influenced in that the heelbone can be tilted in an outward direction to a degree, emphasizing the inside joints of the ankle and throwing the weight of the foot inwards. The main, or longitudinal, arc of the foot can be reduced normally from beforehand in life or can decrease with time as the tendons stretch and the placement of the bones preserving the arc adjustments.
The forefoot arch can likewise reduce, forcing even more pressure from the first metatarsal head onto the second and third metatarsal heads, creating callouses and discomfort to establish under the round of the foot, which in addition to thinning cells around hurting can provide substantial problems. Physiotherapy assessment of the foot starts with checking out the foot in static standing to obtain a suggestion of the primary posture propensities of the foot, after that look at the individual walking to assess the vibrant patterns of motion and also joint stability.
The physio will manually examine the individual joints for their anatomical and also accessory movements as well as anxiety the joints to test that they can cope with that without pain.
Physiotherapy management can involve mobilisation methods to bring back joint movements, strengthening workouts for the general muscular tissues and also arch stabilisers, modifications in gait pattern and also option of footwear as well as prescription of insoles. Ready made insoles can be warm molded to the wanted shape of the patient's foot as well as little adjustments put on realign the joint facilities correctly for the desired mechanical alignment of the joints. Soles are simple for the physio to use and also can be adjusted till the patient feels they are getting a good result.
A common complaint that arises is an aching or damaged reduced back. And, what you will certainly uncover when you have actually suffered from an aching lower back is that there are a frustrating array of choices readily available to fix the damage and reduce the pain. Everyone you fulfill will certainly have a various viewpoint on what you ought to do concerning your back pain. So, I am going to attempt and also clarify what some of the alternative therapies imply to aid you better recognize your options.
The first time you wound your back, you are probably to end up at the physiotherapist. Their technique will usually revolve around muscle mass adjustment to correct the issue. They will certainly utilize a variety of massage therapy and stretching methods around the afflicted area as well as right into your legs too. You will typically be offered a variety of exercises that you after that complete on a regular basis to try and maintain recurring muscle mass motion.
An osteopath will work on a combination of your muscle mass as well as bones to repair the trouble and also ease your symptoms. You will find an osteopath will certainly be very curious about your individual background to understand where the problem began, also returning to childhood. They will certainly additionally look at your stance and also posture, especially the positioning of your hips about your spine as well as legs. Many therapies generally include a mix of massage as well as spine adjustment. In a similar way to a Physiotherapist, you will certainly be provided some workouts to extend as well as reinforce the location in between appointments.
A Chiropractic practitioner wants your back. After an assessment of your issue, a Chiropractic physician will certainly control your back to try to deal with the trouble over time. There are two strategies for manipulation that I have seen, although there might be much more. Any kind of Chiropractic doctors that are available do not hesitate to chip in! The first strategy I have seen is physical manipulation where they use their body to change your back. The 2nd is using a small gadget that pushes right into the area they wish to deal with. It has a flat, round end that uses a percentage of stress to a targeted area so it does not hurt.
Remedial massage therapy or myotherapy is likewise frequently used to treat back pain. Basically, the massage specialist will attempt to lower tension around the affected location as well as launch toxins that have actually built up in the muscle mass gradually. This typically deals with the signs instead of the reason itself. Having claimed that, massage therapy is a reliable method to restore motion and lower discomfort. Many individuals additionally utilize it as part of an upkeep approach to prevent additional injury in the future. It is possibly worth mentioning at this moment, that as soon as you injure your back, it is very rare not to experience issues once more.
Pilates is an excellent preventative and also long term repair work technique for individuals with lower back pain. It is concentrated on building the muscular tissues that support your back, frequently referred to as core stamina. In doing so, you are managing the danger of additional injury as your body starts to acquire toughness in the right areas. There are 2 kinds of Pilates to choose from called scientific or timeless.
Yoga exercise
In a similar way to Pilates, yoga exercise is one more preventative method. Many people in fact begin yoga exercise to help alleviate or manage reduced back pain. Yoga exercise offers an organized technique to stretching of the muscles and also aids develop stamina over time.
submitted by fysiotherapieplint to u/fysiotherapieplint [link] [comments]

2020.09.27 05:44 startaquizbowlteam Quizbowl Studying Resources and Guides: A Comprehensive Set of Links

Since this is a fairly common question on this subreddit, here's a compendium of resources and guides. Note that, unless otherwise indicated, all of these resources are free for players and coaches.

Specific Resources for Studying Quizbowl

The Quizbowl Packet Archive. Tens of thousands of free questions from past quizbowl tournaments, arranged by general level (Middle School, High School, and Collegiate) and then by tournament. The difficulty for each tournament varies considerably, so look for a number on the name of each set that indicates its general difficulty (1 = easiest, 5 = hardest).
The QuizDB. A search engine for quizbowl questions where players can filter results by question category, difficulty, tournament, and a few other settings. Most questions on QuizDB are also available via the Packet Archive too, but QuizDB is far easier to use when searching for specific topics and categories.
SCOP Study Sheets. A collection of useful facts and buzzwords about some of the most common quizbowl topics by the folks behind the excellent SCOP novice question sets. Great place to start to learn the basics for a variety of topics for newer players.
NAQT's "You Gotta Know" guides. A free monthly themed set of usually around 10 specific people/places/things with a paragraph-long description of quizbowl-relevant clues and information for each. There are *many* of these out there and the level of detail and relative difficulty of each can vary greatly. In general, the older ones are a bit less detailed and the more recent ones are a bit more detailed.
Protobowl. An automatic question reader that allows for multiple people to buzz-in and compete against each other online as the computer "reads" the question to you word-by-word. Has a relatively limited database of questions, though you can pick specific categories and difficulty levels. Unfortunately, there are anonymous trolls in many of the public rooms who will do their best to ruin the experience for others. Use caution and avoid the public rooms unless you are prepared for trolling. Setting up your own room for yourself and your teammates is simple (just add a /anyname to the address, e.g. and you can easily play without distractions.
Quizbug. An automatic question reader similar to Protobowl but with more functionality and access to updated questions from QuizDB. Less social than Protobowl, but also no trolls.
The Qwiz Quizbowl Camps have a fairly large collection of moderately-detailed study guides for an array of quizbowl topics. These are like a more focused, HS-level version of NAQT's "You Gotta Know" guides.
NAQT's quizbowl podcasts. If you want to listen to NAQT-style quizbowl questions at tournament speed, NAQT has recordings from its national championship tournaments dating back to 2005 freely available (obtaining copies of the questions themselves though requires purchasing them from NAQT for a $ fee). There's also a NAQT YouTube channel with some filmed matches.
The Culture Index. An eccentric but detailed selection of various names and facts that could be useful for quizbowl. Probably more useful for college quizbowl than the HS level.

Guides to Studying and Preparing for Quizbowl

From the Northern California Quizbowl Alliance comes Niki Peters' excellent "Guide I Wish I Had" that covers a whole range of quizbowl topics from picking a category to learn to practicing and in-game strategy. Also includes a useful lexicon defining many common quizbowl terms.
NAQT has a fairly extensive guide to improving as a quizbowl player, with some references to proprietary ($) NAQT study aids like its frequency lists and Power-Up guides. Some useful ideas about reference materials as well as links to other NAQT guides for building up teams.
Greater Pennsylvania Quiz Bowl's Ryan Bilger has a detailed post on how to study and improve for quizbowl players.
The Missouri Quizbowl Alliance's Charlie Dees has a good set of tips on studying and preparing for quizbowl.
PACE's Colin McNamara (also of Idaho Quizbowl) has a nice guide for coaches looking to improve their teams.
Greater Pennsylvania Quiz Bowl has a coaching guide as well as a "coaching efficiently" guide for busy teachers.
A really good post on how to become a great science player that gets into the mental aspect of trying to tailor your studying techniques so that you'll be rewarded when playing quizbowl.
The QBWiki has some pages related to studying methods for quizbowl, but they're not always fully fleshed-out and, like much of the QBWiki, often have baffling in-jokes that have been lost to time.
The HSQB Forums have a "Theory" section that sometimes has discussions of improving at quizbowl (search for things like "studying" "improving" and see what you can find).
Finally, you should always feel free to ask other quizbowlers how they improved--most of the time, quizbowlers love to talk about learning things for quizbowl and may have specific sources, practice/study techniques, or ideas for you. Don't hesitate to reach out to people on your team or other teams to ask!
submitted by startaquizbowlteam to Quizbowl [link] [comments]

2020.09.22 15:24 Questioning-DM Excel tip to aid user modification: INDEX-MATCH-MATCH

Excel tip to aid user modification: INDEX-MATCH-MATCH
Hi All,
Given the recent datascraping issues, I wanted to keep a couple of different codes for my ManagedFunds in case these needed to be changed over at any time. When inserting a new column, I noticed that a couple of formula broke. I wanted to offer this tip for u/CompiledSanity and any other users wanting to keep data clean in this and any other Excel/GoogleDrive sheets.
VLOOKUP formulas: when entering the VLOOKUP[index] variable (i.e. the column number the formula should pull), entering a hardcoded number means that if you ever modify the spreadsheet to delete, move, or insert extra columns, the hardcoded number will need to be updated.
  • Example: In the ManagedFunds tab, I inserted a new column B headed ‘Other ID’ so that I could keep a log of the Bloomberg codes I previously sourced for each fund, whilst updating column A with MorningStar IDs to get around the recent datascraping issue. However, the “Live Price” VLOOKUP formula in the Purchase History section is pulling Column “4” for the [index], which is now ‘Currency’ rather than ‘Live Price’ as a result of the new column.
There are a couple of ways to get around this error:
  1. replace the hardcoded number index variable with a COUNTA($A$1:E$1) formula. As long as every column header in row 1 has text in the cell, the formula will count it as "+1", which allows the VLOOKUP to remain correct, no matter how the columns are customised.
  2. Another, more elegant solution is to use INDEX-MATCH-MATCH formulas
  • the INDEX[reference] variable is the array you are searching in. This is equivalent to the VLOOKUP[range].
  • The INDEX[row] will be a MATCH formula, looking for the ticker in the relevant column. This is equivalent to the VLOOKUP[search_key].
  • The INDEX[column] is where this is very helpful. You can either hard-enter a column number, as in VLOOKUP; you can enter a COUNTA() formula, as mentioned in point 1 above; or you can use another MATCH formula, and search specifically for the column header. This means that, regardless of where in the array the column is positioned, the formula will pull the correct data. I have included an example snapshot below.
Image 1 - The various arrays and references being used in the INDEX-MATCH-MATCH formula
Image 2 - The original VLOOKUP formula, being used in cell G23
Image 3 - The new INDEX-MATCH-MATCH formula entered for cell G24
Image 4 - The final result
In Image 1 you can see that the user has inserted a new column B to customise their sheet and keep a code spare in case of data loading issues. As a result, the hardcoded formula in cell G23 (Image 2), which is pulling column “4”, now returns ‘GBP’ rather than the 'Live Value' and breaks the sheet.
However, in cell G24, the INDEX-MATCH-MATCH formula (Image 3) is able to pull the information fine. It begins by identifying the INDEX[reference] array as locked Rows 1:12 (goes down to row 12 so that more rows can be inserted in the table and still be captured in the array). For INDEX[row] it then uses a MATCH formula to find which row in locked cells A1:A12 contains the ticker in cell $A24. It then uses another MATCH formula to find which column in row 1 contains the header contained in cell G$22, i.e. the 'Live Price'. This ultimately returns the correct number. The INDEX-MATCH-MATCH formula locks the reference cells in a way that the formula could be copied left-right-up-down and still pull the relevant ticker information for the respective column it is in.
The only things to watch out for are 1) that the arrays in the MATCH formulas match the number of rows/columns in the INDEX array, respectively, and 2) that the header in G22 has identical text to the required header in row 1.
I hope this can help people. I discovered INDEX formulas in the past 2 years and the versatility helps in all sorts of situations.
submitted by Questioning-DM to CSPersonalFinance [link] [comments]

2020.09.21 14:35 excelevator UDF - REPTX ( Text_range/array , repeat_range/array [ , horizontal ] ) - Repeat given values to an output array

REPTX ( textValue , repeat_x_times [, return_horizonal_array] )
Another function evolved from the new dynamic array paradigm.
Excel has the REPT function that allows the user to repeat given text x times, and little else.
REPTX allows the user to return x number of values to an array.
The textValue can be from a range of cells, a dynamic formula, or another function passing an array.
The repeat_x_times is a paired values to repeat that text x times, the argument being from a range or array argument.
By default a vertical array is return by the function. If you wish to return a horizontal array, the third optional boolean argument horizontal should be TRUEor 1
The array will be spilled to the cells with Excel 365.


REPTX is an array function and returns an array
Show Repeat x times String
1 2 Apple
0 1 Banana
1 4 Pear
0 3 Cherry
1 5 Potato
=REPTX(C2:C6,B2:B6) =REPTX(""""&C2:C6&"""",IF(A2:A6,B2:B6))
Apple "Apple"
Apple "Apple"
Banana "Pear"
Pear "Pear"
Pear "Pear"
Pear "Pear"
Pear "Potato"
Cherry "Potato"
Cherry "Potato"
Cherry "Potato"
Potato "Potato"
Apple Apple Banana Pear Pear Pear Pear Cherry Cherry Cherry Potato Potato Potato Potato Potato
Paste the following code into a worksheet module for it to be available for use.
Function REPTX(strRng As Variant, repRng As Variant, Optional horizontal As Boolean) 'REPTX ( text , repeat_x_times [,return_horizonal_array] ) 'https://www.reddit.comexcelevator ' ' - for all your Spreadsheet questions! Dim rALen As Double 'the length of the arguments If TypeName(repRng) = "Variant()" Then rALen = UBound(repRng) - 1 Else rALen = repRng.Count - 1 End If Dim rArray() ReDim rArray(1, rALen) 'the process array 'get the required numner of rows for the final array Dim ai As Integer: ai = 0 Dim fALen As Double: fALen = 0 Dim fAALen As Integer: fAALen = 0 '& insert the word repeat value to the process array For Each v In repRng fALen = fALen + v rArray(0, ai) = v ai = ai + 1 fAALen = fAALen + v Next Dim fAArray() As Variant 'the final result array ReDim fAArray(fAALen - 1) 'put the words in the process array i = 0 For Each v In strRng rArray(1, i) = v i = i + 1 If i = ai Then Exit For Next i = 0 ai = 0 For i = 0 To rALen For ii = 0 To rArray(0, i) - 1 fAArray(ai) = rArray(1, i) ai = ai + 1 Next Next REPTX = IIf(horizontal, fAArray, WorksheetFunction.Transpose(fAArray)) End Function 
Let me know if you find any bugs!

See a whole bundle of other custom functions at Excelevator

submitted by excelevator to excelevator [link] [comments]

2020.09.19 17:52 creatinsanivity Let's talk a Finnish icon: The Ismo Alanko primer

I was challenged into writing a primer on one of Finland's primary songwriters of all time by u/Zhanteimi at the LetsTalkMusic discord. So here's an album-by-album runthrough of his career!
No artist's career begins with the first album, so naturally nor did Alanko's. Long story short, he was born in a highly artistic family (mother a poet, all siblings musicians), picked cello as his instrument of choice, found rock (especially the Hurriganes debut) and picked a guitar, wrote his first song 'Suck and Fuck All Night Long' (no recordings of this exist, but apparently one of his bands named itself after the song), and formed a number of bands with varying levels of success. His most succesful pre-Hassisen Kone band was a prog band called Sight, which got on the second place in the prog section of Finnish Rock Championship competition (in 1977 or 1978). After he finished high school in the spring of 1979, he moved to Stockholm for the summer. This turned out to be the decision that became the catalyst for huge parts of his career, including...
Hassisen Kone - Täältä tullaan Venäjä
While in Stockholm, Alanko caught wind of a new wave of rock beginning in Finland. A breath of fresh air in the previously stale rock scene. He returned to Joensuu and put together a band from his bandmates from Sight (Reijo Heiskanen and Harri Kinnunen), and Harri's then 17-year-old brother Jussi. They eventually named the band Hassisen Kone, after a sewing machine store in town (the deeply religious shopkeeper was not amused).
The band recorded an album-length demo in 1979 and were signed on a label relatively quickly. They were also qualified to enter the 1980 Finnish Rock Championship competition (even though the judges nearly disqualified them, for they thought they might be professional musicians performing under fake names), which they won, gaining reputation preceding the recording of their debut album.
In August of 1980, the band released Täältä tullaan Venäjä. Propelled by arguably the biggest hit single in Alanko's career, Rappiolla [which was hilariously covered by Metallica recently (which was spontaneously responded to by Ismo Alanko himself)], the album became a smash hit. The album provides variety from straight-up new wave punk to talkingheadsian grooves, schlager punk, simplified swing jazz, ska-infused rock, to whatever you'd categorise 'Viimeinen rock ennen aivokuolemaa' as. It's an ambitious yet consistent whole but, in my opinion, the weakest of the three Hassisen Kone albums. The youthful anarchic feel it has can be refreshing every now and then, but this burst of energy from an obviously young (only two of the bandmembers even in their 20s, band only half a year old) band is redirected better on the follow-up.
Hassisen Kone - Rumat sävelet
Following the release of their debut, the band found themselves in a position that many acts today would both fear and envy: they played hundreds of gigs in the second half of 1980 and the first half of 1981. The gruesome touring around the country took a toll on the young band, but that's only barely comparable to the toll that the audience took on them. For example, as time went by, the band grew tired of the audience drunkenly demanding 'Rappiolla', so they stopped playing the song altogether. This time of maturing and growing more and more cynical reflected on their sophomore effort.
Rumat sävelet should not necessarily be described as bleak per se, but it is certainly darker, tighter, and more mature than the band's debut. The band tackles sounds ranging from quasi-prog expression to post-punk, punk, psychobilly, and they take the talkingheadsian qualities into a sharper direction. The lyrics touch upon issues like love, exploitation, and sex (it's curious to think that probably the most explicit Finnish song about sex before this album was about "curly armpit hair", while Alanko dares to sing about penetration itself). I have to admit that I have a bit of a bias when it comes to this one though, as it's undoubtedly my favourite album of all time.
Hassisen Kone - Harsoinen teräs (and High Tension Wire)
In 1981, the band participated on a riverboat tour with a couple of other punk acts. During this tour, the bassist broke (drugs), Alanko met "Safka" Pekkonen, and the band was generally put under huge stress as the diet consisting mainly of alcohol began burning them down and their every move was documented by either film makers Mika and Aki Kaurismäki or the columnist documenting the tour for a zine. Despite all this, some of the better live recordings of the band come from this tour, and both the live album and the Kaurismäki documentary are worth digging up for the music.
After the tour, the band expanded into a septet with the addition of a keyboardist (Pekkonen), a saxophonist (Antti Seppo), and a percussionist (Hannu Porkka). The final form of the band was shaped during the rehearsals by the departure of guitarist Heiskanen, who was replaced by the guitar wizard Jukka Orma.
Released in March of 1982, Harsoinen teräs is the band's most artistically ambitious work. It's an album combining the band's prog leanings seamlessly with the band's new wave leanings, a polished whole that takes cues from I don't even know where. Reggae at least on a couple of tracks, prog and new wave on most, but the general sound is unlike anything I've ever encountered. The album was re-recorded in English as High Tension Wire later on in the year, after a tour had slightly tightened the band's sound. The decision to do so apparently came after the decision to disband the band, which makes it a very baffling addition to Hassisen Kone's discography. You'd think that they'd release an album in English as an attempt to break into international markets, right?
Sielun Veljet - Sielun Veljet
After Hassisen Kone was disbanded (in August 1982), Alanko had a schlager rock project that eventually turned into Sielun Veljet by December. The band was comprised of Alanko, Orma, a drummer veteran Alf Forsman, Alanko's Stockholm contact Jouko Hohko on bass, Vinski Viholainen doing lighting, and a future cult legend Jouni Mömmö doing "weird noises".
Sielun Veljet were signed in early 1983, but they refused to record a studio album because Viholainen's lighting work wouldn't show in a studio recording. Instead, as a compromise, they agreed on recording a live album where "the lighting would affect the ambiance". They set out to do this on a tour they began on March, planning on recording the first show and the last one. However, fate interfered and Orma accidentally cut tendons from his fingers during the tour while cutting bread, which made the recordings from the last show basically unusable due to his difficulties in adjusting to the situation (a very punk move to finish the tour even with torn tendons, by the way).
The live album is punk/post-punk goodness. It's noisy, no-wavey rock that really shows how the band took all the drugs in the process of writing these harsh, repetitive songs. It also shows that Alanko wished to abandon messing around with intricate compositions in favour of a more stripped and primal expression.
Sielun Veljet - Lapset
In summer of 1983, the band brightened up a bit to record this odd EP in a style following directly from the debut. It's angular and distorted, yet the melodies are more melodic and jamming less bleak. It's also the home of the only a capella punk song I've ever heard.
Sielun Veljet - Hei soturit
In 1984, Sielun Veljet took their first coherent step toward a pop/rock idiom with their first studio album. Hei soturit is the awkward outlier between the band's grimy punk era and commercial rock era. It feels like a punk band working with a producer who doesn't understand punk, but even the clumsy production doesn't entirely hide the fact that some of these songs are absolutely iconic. From punk to garage rock, general oddness, and flirting even with metal, this selection of songs does provide good variety for anyone digging deeper in Alanko's body of work.
Sielun Veljet - L'amourha
The Sielun Veljet breakthrough album! The beginning of their rock era of albums, an only mildly angular affair with anthemic choruses and a muscular production. It was recorded after the band had toured all over Europe, honing their sound and Alanko finding a lot to say about international affairs and the human condition.
There are a plenty of anecdotes from the time of release of this album. The song 'On mulla unelma' was written by Alanko in Spain, when he was recovering from a disease (can't remember which one. Dysentery?) and bitter about nationalism, and it caused quite a scandal when the band unexpectedly debuted it on live television. They performed an impromptu Red Riding Hood play on their album release party instead of playing music. One of the members went missing in Russia for days after the band found a corpse. All of their instruments were stolen in Spain. There'd a lot to unpack from 1984-5 alone.
Sielun Veljet - Kuka teki huorin
The follow-up to L'amourha takes the band to a funkier place. It's a minor downgrade from the previous effort, a slightly directionless and overpolished effort that has diverse variety from RHCP-like funk rock to tango-infused rock, tribal chants, and what's essentially watered down imitation of their earlier work. It's an easy album to criticise, yet I don't find ever to be outright bad. A lot of it is extremely forgettable though
L'amourder - Ritual and Shit-Hot
Sielun Veljet recorded a bunch of their songs in English as L'amourder. Most of them follow the originals very closely, but there are a few surprises. The biggest change is on the translation of 'Tuulelta vastauksen saan', which has been turned into a cover of Bob Dylan's 'Blowin' in the Wind'.
Sielun Veljet - Suomi - Finland
Suomi - Finland begins the last era of the band, as this album brings more acoustic instruments to the mix and begins to flirt with psychedelia in a way that will culminate on the follow-up. It feels like a breaking point for the band, as it sprawls on multiple directions at once, the musicians seemingly having lost focus. It feels like a band slowly drifring apart, yet it remains consistently captivating as the different influences come together in this chaotic work.
"Various Artists" - Onnenpyörä
Sielun Veljet performed under a number of false identities on their Onnenpyörä-tour, four of which make an appearance on this recording. All of these are cover bands of sorts, and each one of them had a different repertoire of songs they played on these wildly differing sets. The most noteworthy of these personae are the pavillion dance band Kullervo Kivi ja Gehenna-yhtye and the rock band Leputation of the Slaves, the two having the most songs on the record.
Sielun Veljet - Softwood Music Under Slow Pillars
Who would have thought that the noisy punk band in 1983 would eventually release what could be called a psychedelic flamenco album in 1989? Many factors come together here, as the band continues on their effort to sell their music internationally by making the biggest left turn in memory. Orma's fascination with flamenco combines here with influences Alanko picked up in India and what could be called a somewhat logical progression from the budding psychedelia of Suomi - Finland. It's a weird album, that's for sure. A bit inconsistent, but easily among the strangest albums I've ever heard.
Ismo Alanko - Kun Suomi putos puusta
In 1990, Alanko found himself in a situation where Sielun Veljet had almost run its course and he could finally start building a solo career. He recorded this solo debut as a quasi-concept album about rural flight, combining the various interest he wasn't able to pursue with the band into a unique singesongwriter album of sorts. It's a classic album and, in some ways, an ideal entry point into Alanko's work as it feels like it's his personal expression in its purest form.
The music on the album sounds mainly like pop rock of sorts, but it also takes cues from melancholic singesongwriter stuff, joyous showtunes, post-punk akin to Nick Cave's work, and some field recording experiments. It finds a good balance between artistic ambition and catchiness, and it's home to some of the most iconic tracks in Ismo Alanko songbook.
Sielun Veljet - Musta laatikko
Do you know Tom Waits' Orphans? This one is kind of like that. Three discs filled with random stuff recorded over years.
The first disc, "Muistinmenetys", is one third a new studio album (very weird new direction to take, something that feels like a cross between chill hippie jamming and 80s dance pop), one third music from some production, and one third short excerpts from live performances. The second disc, "Taudinkuva", is mainly live performances of late 80s Sielun Veljet songs, Tuomari Nurmio covers, and some other oddities. And finally, the third disc, "Isältä pojalle", is a full pavillion dance set, the band LARPing as a suave and jazzy house band playing waltz, tango, schlager, and anything that's really expected of them.
This album is definitely a skippable one, but there are a few gems that an Ismo Alanko fan might get a lot out of. The flamenco pieces are cool, the Tuomari Nurmio covers are nice (more about those later), and that pavillion dance set is unexpectedly fun, especially if you're not already familiar with the tradition.
Ismo Alanko - Jäätyneitä lauluja
Alanko goes electronic! This album was originally lauded as cutting edge and a sign of significant artistic growth, but it has definitely fallen in popularity over the years. It sounds extremely like a product of its time, so if you like non-industrial synthpop-y rock from 90s, this is exactly your thing. Overall, it's still very surprising how many Alanko live staples come from this album though, and how some of his live bands have improved on all of them.
Ismo Alanko - Taiteilijaelämää
If someone doesn't think that Kun Suomi putos puusta is Alanko's magnum opus, they usually pick this one. Taiteilijaelämää feels like a combination of the first two solo albums (acoustic, electric, and electronic joining hands in harmony), but brought into the mid-90s rock idiom. The result is an interesting album that lacks real highs but remains consistently accessible, and the one Ismo Alanko work I've heard to have resonated with Beck fans for some reason.
Ismo Alanko - I-r-t-i
I made the mistake of learning that this album was written in only two weeks (because Alanko wanted to test himself), and now that's all I can think of while listening to it. This does feel halfbaked. The accessible rock sound it has is underproduced and covers up lazy songwriting more than once. That said, Alanko has later on proven that some of these songs can be absolutely amazing live, and the demo-like quality many of these tracks have can be seen as a feature instead of a bug. Pushing its flaws aside, I feel that it is underappreciated as an album, and feel like its high points deserve more attention.
Ismo Alanko Säätiö - Pulu
Säätiö was an interesting group. I'm honestly still a bit unclear whether they should be considered Alanko's backing band, a band that just happened to capitalise on his name, or a fullblown collective of musicians. Alanko's statements concerning the group together with the changing lineups on Säätiö albums both point to all three options. What I do know is that the band has two distinct eras, the first one kickstarted by Pulu.
I genuinely believe that the first iterarion of Säätiö is the most important band Alanko worked with. The amount of pure talent in that band is staggering with Jussi Kinnunen (Hassisen Kone) on bass, Teho Majamäki (HC Andersen, Tapani Rinne, Ismo Alanko Teholla) on percussion, Kimmo Pohjonen (you will want to check his solo stuff) on accordion, and Marko Timonen (Värttinä, Tuomari Nurmio) on drums giving Alanko's songwriting a fascinating folk rock spin, reeking of schlager and eastern mysticism. Pulu is an album that seeps nostalgia, is radical enough to upset traditional folk nerds, is accessible enough to have produced multiple Alanko live staples, and is significant enough a twist on Alanko's tropes to sound fresh even in his eclectic body of work. Yet, I feel like it's so self-referential that I feel like recommending it as anyone's first Ismo Alanko album could be a mistake.
Ismo Alanko Säätiö - Luonnossa
Säätiö playing acoustic renditions from the entire Ismo Alanko songbook, from Täältä tullaan Venäjä to Pulu. An exciting set, and definitely one of the best live albums I've ever heard. The band reworks this wide variety of songs into captivating folk rock, transforming the music into forms that defy expectations. There are some duds though, but not all fan favourites can sound great with just one band.
Ismo Alanko Säätiö - Sisäinen solarium
This is possibly the weirdest Ismo Alanko album to this date. It continues with nearly the same lineup as on Pulu, but takes the music in a radically new direction, exploring what modernised folk could be rather than wallowing on nostalgia. This means updating the largely acoustic instrumentation with both electric and electronic instruments, and creating an unpredictable tapestry of music with influences that are surprisingly difficult to pinpoint. Some say this kind of experimentation cheapens traditional folk (which is something I can agree with regarding some songs on this album), but I'm not sure if such a clearcut statement can be made of the full album. It's certainly aiming for a sound of its own.
Ismo Alanko Säätiö - Hallanvaara
This is where Säätiö's status as a band becomes complicated. There's absolutely no reason to call this anything but an Ismo Alanko solo album, so marketing it as an Ismo Alanko Säätiö album is baffling to say the least. I mean, the only constants on this album are Alanko himself, the producemulti-instrumentalist Riku Mattila, and various symphonic elements (I don't want to downplay the work the symphonic orchestra and the string section do on this album, but they have been used quite haphazardly). There are three members from the previous Säätiö albums involved in this project: Marko Timonen on nine tracks, Samuli Laiho on seven tracks, and Kimmo Pohjonen on one track. In addition to this, there's the bassist of the next iteration of Säätiö, Jarno Karjalainen, on six tracks. Thus, there are Säätiö band members playing on the majority of these tracks, but never as a full band.
That all being said, I believe this to be the best Säätiö album. The melancholic pieces are beautifully fragile, the pop tracks are catchy, the massive songs are massive, and the atmospheric pieces are chillingly well-arranged. And even the weaker songs here are excellent live, making this album probably the richest one to mine for a live set of any kind.
Ismo Alanko Säätiö - Elävää musiikkia
Honestly, this feels like a bit of a throwaway live album. On one hand, these rock renditions of a great setlist of songs are unique but, on the other hand, none of these performances improve on the studio recordings. 'Kansallispäivä' and 'Julkinen eläin' come really close though, both being sharper and meaner than the 80s versions.
Ismo Alanko Säätiö - Minä ja pojat
The first album with the second iteration of the band. Fuzzy rock in similar vein to Smashing Pumpkins and their kin, but played through the lense of Alanko's style of songwriting. It's never as hard-hitting or catchy as an album by a great rock band would be -- all of the songs soften up during the chorus -- but the youthful and slightly naivistic touch is welcome after a string of artistically ambitious albums. That said, I'm only attached to a single song on the whole album, which is definitely not a good sign.
Ismo Alanko Säätiö - Ruuhkainen taivas
The second (and last) studio album of the second iteration of Säätiö is a different beast than the first one, taking the rock approach to a slightly more complex direction. It's more mature and chromatic than the first album, yet I personally find it to sound slightly less inspired. However, at the same time, it does have more tracks that I would consider keepers and the general sound is harder to define. Thus, it's definitely a divisive album, conflicting.
I'm not sure how to describe the sound of this album. It's unmistakeably early 2000s rock, sounding like an average Finnish rock band from the era, yet the songwriting and the production also remind me of the band Wire out of all things. It's a digestible alternative/indie rock sound, whenever it doesn't abruptly go in a new direction.
Sielun Veljet - Otteita Tuomari Nurmion laulukirjasta
Remember those random Tuomari Nurmio covers on Musta Laatikko? Turns out, Sielun Veljet recorded a full album of those in (I assume) late 80s. They didn't end up using those recordings for anything, so they were packed away and stored somewhere. Years went by and a good portion of those recordings were destroyed due to poor storing conditions, but someone was eventually inspired to put the surviving songs to good use.
You'll be in for a treat, if you like Sielun Veljet and have never heard anything by Tuomari Nurmio. Most of these covers are originally from Nurmio's early 80s albums, his strange new wave turned into the angular rock Sielun Veljet perfected. Some of these songs only barely work, some sound like Sielun Veljet originals, but most are just serviceable covers. It's still a good album though.
Ismo Alanko Teholla - Blanco spirituals
After putting Säätiö on hold (perhaps indefinitely), Alanko joined forces with Teho Majamäki, the first iteration Säätiö percussionist. Together they stripped down a number of Ismo Alanko songbook staples to a form they could perform as a duo, essentially bringing the strengths of Alanko's live performances alone together with the strengths of him performing with a small ensemble. This endeavour proved succesful, so the two recorded two albums of original music as well.
The music of Blanco spirituals is surprisingly full. The two musicians fill space well, with Alanko singing and playing chord instruments (mainly guitar and piano), while Majamäki stretches himself as thin as possible, working a drumset, vibes, an array of percussions, an oscillating delay pedal, and singing backing vocals. It's usually at least two of those at the same time, often three. Him working in a live environment is a sight to behold.
This is honestly one of my favourite Ismo Alanko albums. The stripped down arrangements bring the most out of Alanko's songwriting. The selection of songs highlight very different sides of his style, from theatrical piano ballads to singalong acoustic guitar romps, silly pop songs, and trance-inducing rock. It's by no means a perfect album, but these simple songs all work in one way or another.
Sielun Veljet - Kansan parissa (1-4)
Archival live recordings of sets recorded around 1989-1991. The first one is a typical Sielun Veljet set, the second one filled with Tuomari Nurmio covers, the third one is material from Softwood Music Under Slow Pillars, and the fourth one is a mix of subtle experimentation, new tracks, and deep cuts. Quite a comprehensive collection of live music. However, only few tracks are really worth keeping, including the electrifying high-tempo performance of 'Lammassusi' and the prototypical version of Alanko's 'Don Quiote'.
Ismo Alanko Teholla - Onnellisuus
The simplicity of the previous album is gone, replaced by a polished and highly produced pop sound. The DIY duo sound gives way to a more layered style, where synths, samples, and doubled vocals are added to the band's sound. Acoustic instruments are largely replaced by electric guitars and synths, turning the folksy garage band sound to a sleak and radio-friendly beast. If the fact that I just phrased the same exact thing in three ways didn't clue you in yet, I'm not particularly fond of this change of direction. However, I've seen this ridiculously often called the best Ismo Alanko album since the 90s, so it does appeal to the masses.
If you like 2010s pop and are looking for a decent gateway to Alanko's music, this could be the album to start with. It's accessible.
Hassisen Kone - 20 vuotta myöhemmin
Hassisen Kone had a reunion in 2000. They played a show that was both filmed and recorded. It's an interesting document of musicians playing music they wrote 20 years earlier. However, it ultimately sounds a bit tired compared to both the tight playing on their studio recordings and the energy levels on their 80s live recordings.
Ismo Alanko - Maailmanlopun sushibaari
Remember when I said that most pick between Kun Suomi putos puusta and Taiteilijaelämää as Alanko's magnum opus? Well, this is that one for me. I'm not saying that to imply that it would be his best album, but it's the album where he finally brings his disparate influences together in a coherent but eclectic way. If Kun Suomi putos puusta is where Alanko's artistic voice is at its purest, this is where it is at its maturest and most representative of the multi-faceted artist he has become during his career.
More or less incidentally, this is also Alanko's midlife crisis album. It's not entirely thematic -- who even knows what 'Kuusilmä' is about? -- but it does touch upon themes like growing old, dying, passing the torch, losing one's touch, and liking the colour grey. It's not quite on the nose, but you don't exactly have to dissect the lyrics to find those undercurrents.
So what does the album sound like? It's lighter than you'd imagine based on the central themes. There's rock, funk, subtle latin feel, a capella, pop, traditional folk, and even an ambitious rock opera about what sounds like a zombie apocalypse. It's fairly eclectic, making it a nice first solo album to release in nearly two decades.
Ismo Alanko - 33 1/3: Kolmannesvuosisata taiteilijaelämää
This is the Ismo Alanko live album I recommend people to start with. Are these performances as exciting as their studio versions? No. But I'd argue that they don't have to be. The main strength this recording has is its uniformity. The songs are played in a generic rock band style, but it doesn't change the fact that the setlist is good and diverse. There's no compilation that would dive this deep in such a digestible manner. Essentially, this is the middle-of-the-road pick that gives an excellent cursory look into a prolific artist's entire body of work (up until 2013).
Ismo Alanko - Ismo Kullervo Alanko
Considering how introspective and self-reflective the previous album is, it's surprising that Alanko decided to name this one after himself. It works though. The songs are produced sparser and airier than on any other Ismo Alanko album, making the music feel intimate and almost confessional. It feels like you're sitting in the same room with him, as he opens up to you. Amazingly produced album.
Ismo Alanko - Pannaanko pakasteet pieneen pussiin?
To be frank, I don't think this EP is an essential release. It's noteworthy for the modern hobo blues feel it has, and for having one of the very few covers Alanko has recorded so far, but none of these songs have an iconic feel to them. The best I can say about it is that none of the songs are bad, but neither are they memorable.
Ismo Alanko - Yksin Vanhalla
I wish more band-focused artists performed live alone every now and then. An arrangement stripped down to just vocals and an instrument (in Alanko's case, usually acoustic guitar, piano, or cello) turns every song into something entirely different. However, the lyrics grow in significance as instruments are dropped, so your mileage may vary with this one. I still enjoy it though.
Pohjonen Alanko - Northern Lowland
Alanko collaborates with Kimmo Pohjonen and Tuomas Norvio to bring us an electronic neon-shamanic album. Primal chants and vocalisations blending together with beats ranging from harsh to chill and breakbeat-y. It's a fascinating EP, even if highly gimmicky and lacking a sense of direction. Besides, this stuff will always be better live than on a studio recording.
Ismo Alanko - Minä halusin olla niin kuin Beethoven
And finally, the latest Ismo Alanko album, where he takes yet another left turn. This one was mainly recorded by Alanko alone in a studio, but eventually a drummer and a keyboardist were brought in to round up the sound. And what a sound it is! Youthful indie rock with a production that's stuck somewhere between the 00s and the 80s. If it were not for 58-year-old Alanko's vocals and eccentric riffing, I could very well believe this to be a debut album by ambitious 20-somethings.
Since Alanko's full albums are not readily available on many countries (especially the US), I'll provide a summary that's somewhere between a longish TL;DR, a series of recommendations, and a quick-glance overview of his career.
Album:: Täältä tullaan Venäjä (1980) [new wave punk] Representative track: Rock ehkäisyvälineitä vastaan (a bouncy high-tempo punk track)
Album: Rumat sävelet (1981) [new wave/post-punk] Representative track: Jurot nuorisojulkkikset (a gloomy post-punk-infused rock track)
Album: Harsoinen teräs (1982) [new wave/progressive rock] Representative track: Kupla kimaltaa (a well-flowing new wave track with a progressive song structure)
Album: Sielun Veljet (1983) [punk/post-punk] Representative track: Pieni pää (a noisy punk track with groovy tribal drumming and metallic guitar playing)
Album: Lapset (1983) [punk/post-punk] Representative track: Elintaso (an angular punk track)
Album: Hei soturit (1984) [post-punk/alternative rock] Representative track: Tää on tää (a straightforward punk track with a catchy hook)
Album: L'amourha (1985) [post-punk/hard rock] Representative track: Peltirumpu (a hard-hitting rock song with dissonant guitars)
Album: Kuka teki huorin (1986) [post-punk/funk rock] Representative track: Kristallilapsia (a funk rock track with screechy guitars and an unfunky bassline)
Album: Suomi - Finland (1988) [post-punk/psychedelic rock] Representative track: Totuus vai tequila (a ferocious folk punk track)
Album: Softwood Music Under Slow Pillars (1989) [psychedelic rock/flamenco] Representative track: Life is a Cobra (a psychedelic track combining flamenco rhythms and Indian string sections)
Album: Kun Suomi putos puusta (1990) [singesongwriter] Representative track: Kun Suomi putos puusta (a gentle organ-led track with subtle folk influence and field recordings)
Album: Jäätyneitä lauluja (1993) [electronic rock] Representative track: Pornografiaa (a slightly industrial-tinged electronic rock track)
Album: Taiteilijaelämää (1995) [art rock] Representative track: Nuorena syntynyt (a 90s sounding rock track with a freeform looseness to it)
Album: I-r-t-i (1996) [alternative rock] Representative track: Kriisistä kriisiin (a rock track with a steady dance pulse on the actual rock sections)
Album: Pulu (1998) [folk rock/art rock] Representative track: Rakkaus on ruma sana (a track with pseudo-shamanistic verses and catchy choruses)
Album: Sisäinen solarium (2000) (art pop/folk rock) Representative track: Kirskainen hyvätyinen (a largely electronic and pulsing track that feels one part a strange rock experiment and one part a traditional Finnish folk song)
Album: Hallanvaara (2002) (art pop/symphonic rock) Representative track: Paratiisin puu (a smooth pop track with significant classical influence)
Album: Minä ja pojat (2004) [alternative rock] Representative track: Joensuu (a straightforward and fuzzy rock song)
Album: Ruuhkainen taivas (2006) [alternative rock) Representative track: Paskiainen (a rock track alternating between manic psychobilly and catchy radio rock)
Album: Blanco spirituals (2008) [minimalistic art pop] Representative track: Päästänkö irti (an acoustic rock track with an interesting chord sequence)
Album: Onnellisuus (2010) [art pop] Representative track: Onnellisuus (a danceable and atmospheric pop track)
Album: Maailmanlopun sushibaari (2013) [alternative rock] Representative track: Vanha nuori (an accessible pop track with a funky brass section and theatrical choruses)
Album: Ismo Kullervo Alanko (2015) [art pop/singesongwriter] Representative track: Lintuperspektiivi (a melancholic and sparsely produced track with airy ambience)
Album: Northern Lowland (2018) [glitch hop-y tribal electronic music] Representative track: Northern Lowland (a track with primal chanting and glitchy beats)
Album: Minä halusin olla niin kuin Beethoven (2019) [80s flavour indie rock] Representative track: Transsioletettu tanssi (a funky rock track with a somewhat generic 2000s rock chorus)
What is your opinion on Ismo Alanko? I personally enjoy how prolific and eclectic he has been, and I find it a shame that most of his work has never left Finland. I can especially imagine punk fans easily getting into his 80s work.
submitted by creatinsanivity to LetsTalkMusic [link] [comments]

2020.09.16 06:06 thetorontobot Toronto Daily - Sep 16th 2020

Welcome to the Toronto Daily Thread.

This thread serves two purposes:
1) To collect and make visible new posts in smaller Toronto based subreddits.
Feel free to visit, comment and be generally helpful in posts indexed below. Please also remember to stay on your best behaviour when travelling outside of /toronto.
2) To act as a general off-topic conversation hub for the day.
To that end, use this thread to talk about whatever is on your mind, regardless of whether or not it's related to Toronto.
No matter where you're posting, please remember to be excellent to each other.


Post Title Author Comments
Looking for a place to buy garden seeds. Mercenary-Jane 2
Looking for photographer for online dating profile call_911911 0
Toronto Utilities Problem LARPerator 2
What are some reliable ISP's in Toronto/North York? jwong11 0
About the G1 Driver’s License Test, how long does it usually take someone to get prepared and get the G1 Driver's License? 123261 16
Searching for a therapist specializing in eating disorders atlaster 3
How is it living near east Waterfront in Toronto? (near Lower Jarvis Street, Richardson Street) tiberius1102 3
Anyone Get a delivery from Structube lately? modest811 6
How Do You Dispute Multiple City Parking Tickets At Once? hydrantidiots 4
Looking for Teppanyaki recommendations! DingleberryJones94 2
Living in The Junction wildrow 10
Best pumpkin pie nodoubtguy 7
$24,500 for laminate glass to reduce traffic noise...fair price? should I do it? AnnieNY_C 33
How do you prepare your garden for the winter? rch-ie 5
Popeyes chicken Sandwhich, is it better than mcdonalds jr chicken? ArrayMichael7 132
Does anyone know where downtown I could buy a fidget cube? PhiliDips 3
heated patios with good food? Coucyman 1
What's the traffic like on TTC during rush work hours? noragretx 5
Filming on Walmer Road Today upcoming_emperor 3
Has anyone been to the walkthrough Van Gogh exhibit? PaintedFrancolin 3
What are some surprisingly cheap UberEats options? MerseySideAlt9 17
"Among Us" game group CanuckAussieKev 11
G1 knowledge test looneycrispy 14
Anyone manage to find Cimexa locally? skerting 0
Condo building not enforcing COVID-19 bylaws picytarY 18


Post Title Author Comments
[HIRING] Walmart Stockyards, Toronto, September 16, 2020 - Job Fair, 02:30PM - 05:00PM notpreposterous 0
[For hire] Junior Front End Web Developer coreymannweb 0


Post Title Author Comments
Private, socially-distanced concerts in Toronto socialdistancing716 5


Post Title Author Comments
Men's Ultraboost 20 Size 10.5 - Brand New In Box with tags - $140 methlabz 0
I am a bot, and this post was generated automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
submitted by thetorontobot to toronto [link] [comments]

2020.09.14 17:24 DoraDD How to use INDEX and MATCH in an array fornula to create a list?

Hi all!
I'd really appreciate your help with making this work. First off, here's a mock-up of my sheet so I can make this easier to understand.
In Sheet 1, this is Table 1 called tbl_Table_1 for this purpose. In reality, it has over 15 columns. These are just the ones I'm concerned with at the moment. The "Participants" column is column 13 in the original table (hence the column 13 reference in the formula), and the exhibition name is column 2 in case you need to know.
Exhibition name Participants
Exhibition a Participant 1, participant 2, participant 3
Exhibition b Participant 1, participant 4, participant 5
I have a large dataset listing exhibitions, participants and other data. I have created a dropdown list of all possible participants (e.g. Participant 1, participant 2, etc.) and now want to extract all the exhibitions they've taken part in. The issue here is that, when I apply the following array formula in Sheet 2, I end up getting the same exhibition name repeated over and over again. I want a list of all the exhibitions any individual participant has taken part in.
Here's the formula I'm using (adapted for this mock-up) in Sheet 2:
=INDEX(tbl_Table_1[#All];MATCH(INDEX(tbl_Table_1;MATCH("*"&Participant 1&"*";tbl_Table_1[Participants];0)+1;13)tbl_Table_1[Participants];0)+1;2)
Mind you, I get the data I want using this formula, but Excel stops at the first exhibition name and just repeats it in all cells in the array formula.
The bolded part of the formula might seem strange to you and like it has errors because of the "+1" but it seems to get me the data I need. So unless you think this is causing the issue, you can completely disregard it.
So thank you for taking the time to help me out here!
If need be, I can create a more realistic mock-up in Google Sheets, but I'm unable to share the exact spreadsheet since it has data I can't go public with.
Thank you again!
submitted by DoraDD to excel [link] [comments]

2020.09.14 14:53 excelevator UDF - MULTIFIND ( find_range , words_range [, case_match , match_all , return_all ] ) - return a string/s from multiple search words

UDF - MULTIFIND ( find_range , words_range [, case_match , match_all , return_all ] )
MULTIFIND allows for the return of a string or multiple strings from a search list, matched from a list of words the user enters as criteria.
The advent of Dynamic Ranges in Excel 365 gives the opportunity to write UDFs that can return multiple array results in an easy manner where they Spill automatically; in this UDF with the return_all option set in the last argument.
The other option for pre Dynamic Ranges is to select a range of cells and enter the formula as an array formula with ctrl+shift+enter.. but this was written with 365 and dynamic ranges in mind.
find_range - the contiguous range of cells with the values to search in for a match
words_range - the contiguous range of cells with the search words in , one per cell.
case_match can be set to TRUE to return case matched values. MULTIFIND is not case sensitive by default.
match_all can be set to TRUE to only return a matched value when all the search words have been entered into the word_range. By default MULTIFIND starts to return matched values on the first search word as entered, returning more filtered values with each additional word,
return_all can be set to TRUE to return all matched values in an array. By default MULTIFIND will return the first matched result.


Source list Example 4 word search list range1 Example 4 word Search list range2
An apple a day SNOW garden
A banana go away peas my
Snow Peas For Winter carrots
Carrots in my garden in
Lots of snow peas in winter
Carrots planted in my garden
Who planted carrots in my garden
Runner beans be running
Formula Example 1 results Why
=MULTIFIND(A2:A9,B2:B5) Snow Peas For Winter default
=MULTIFIND(A2:A9,B2:B5,1) #VALUE! case senstive
=MULTIFIND(A2:A9,B2:B5,,1) #VALUE! match all words
=MULTIFIND(A2:A9,B2:B5,,,1) Snow Peas For Winter array
array Lots of snow peas in winter array
Formula Example 2 results Why
=MULTIFIND(A2:A9,C2:C5) Carrots in my garden default
=MULTIFIND(A2:A9,C2:C5,1) Who planted carrots in my garden case matched
=MULTIFIND(A2:A9,C2:C5,0,1,1) Carrots in my garden match all words, return all words
array Carrots planted in my garden
array Who planted carrots in my garden
Paste the following code into a worksheet module for it to be available for use.
Function MULTIFIND(t As Range, f As Range, Optional cSen As Boolean, Optional fAll As Boolean, Optional rAll As Boolean) As Variant 'MULTIFIND ( find_range , words_range [, case_match , match_all , return_all ] ) 'https://www.reddit.comexcelevator ' ' - for all your Spreadsheet questions! Dim tArray() As Variant tArray = t Dim fArray() As Variant fArray = f Dim aString As String Dim fsize As Double fsize = UBound(fArray) Dim wCount As Integer Dim tValue As String Dim ta As Variant, fa As Variant If Trim(Join(WorksheetFunction.Transpose(fArray))) = "" Then Error (13) For Each ta In tArray wCount = 0 tValue = IIf(cSen, ta, UCase(ta)) For Each fa In fArray If Not cSen Then fa = UCase(fa) If InStr(tValue, fa) And IIf(fAll, fa <> "", True) Then wCount = wCount + 1 Next If wCount = fsize Then aString = aString & ta & "§": If Not rAll Then Exit For Next MULTIFIND = WorksheetFunction.Transpose(Split(Left(aString, Len(aString) - 1), "§")) End Function 
Let me know if you find any bugs!

See a whole bundle of other custom functions at Excelevator

submitted by excelevator to excelevator [link] [comments]

2020.09.11 10:41 bhaveshj21 converting excel to struct

I have a use case where I need to read an excel and process it , which will be uploaded by the user.
I am using this package to read the excel , the problem here is that to reach value of each cell I need to read the row as array and cell with the index , this will hardly bind the column sequence and will fail if the sequence is changed, I was thinking is there a way to read excel directly to a struct.
I found this , but the issue here is it requires the file path to open it,
I receive the file in request which is *multipart.FileHeader format , here for this I need to first create a file and save it somewhere and then process it .
is there any other way where I can convert excel into struct .
submitted by bhaveshj21 to golang [link] [comments]

2020.09.11 02:07 MCP_ Come and Join the Simulation Hockey League!

Find us at:
Hello Buffalo Sabres fans! First we would like to thank the mods for allowing us to share our league with you!
So what exactly is the Simulation Hockey League? We are a free, established sim-league about to enter our 56th season! The SHL allows you to create a character and take control of every aspect of their career. From creation there are tasks you can participate in on a weekly basis to earn points that go towards improving your player to perform better. With the conclusion of our most recent season comes the off-season activities, including the Simulation Major Junior Hockey League's (SMJHL) Entry Draft, the initial stepping stone for your player. You will be drafted to one of 12 excellent SMJHL teams, including 2 brand new expansion teams! You'll spend your first season getting acclimated to the league and fighting for the Four Star Cup, and afterwards you'll be drafted by one of the 18 Simulation Hockey League (SHL) teams. Work hard, stay active and you could earn the prestige of being the 1st overall draft pick!
BE-A-PRO EXPERIENCE - A rich, personal hockey experience that crosses between video games and fantasy hockey. Build your player exactly how you want with a variety of different styles. Be totally involved in your player from the beginning, and take them from rookie to the SHL Hall of Fame! Every game is simulated on Franchise Hockey Manager 6, and its wide array of attributes allows you to customize your player to the maximum. Success is based on your activity within our community, so the more involved you are, the greater your player can be! A link to our YouTube channel can be found here if you want to see what our daily streams are like!
FREE - As stated earlier, I want to emphasize that our community/site is completely free. It's free to join, free to participate, free to do what you want. Our league runs off the hard work of our community, we all serve each other in that regard. Every aspect of our site is run by our members. This isn’t a sales pitch, we’re just always looking for new members to fuel the already great community we have built!
DRAFT DAY - Experience what it is like to wait for your name to be selected, alongside your draftmates, vying for that top spot! This is a full draft experience where real people will draft you and spend their resources on your potential. Become the next big steal, or potentially the next big bust. Show the GMs you’ve got what it takes to be the face of their franchise!
REAL PEOPLE, REAL OWNERS - Every aspect of the league is run by real people from all around the world! Your draftmates, future teammates, general managers and the league’s head office are all real people you can interact with. Every decision from team lineups, draft day selections and last minute trade deadline deals are done by teams owned by members of the site, with plenty of opportunity for you to fill in those roles yourself! SHL superstar, team captain or scout, General Manager, Head Office; the site is full of opportunities for you to get involved however you so choose!
COMMUNITY - Once your player is drafted onto a team you will gain access to a private locker room for your organization, and their Discord server (should you choose to join there). Here is often where the closest relationships develop, and where you’ll make some great new friends to share your SHL journey with! You can spend time discussing the season, reflecting on your success, or just having a blast hanging out together! Of course you’ll find passionate hockey fans here, but with so many unique members, you’re bound to find people with lots of interests all over the site for you to get connected with.
Find us at:
submitted by MCP_ to sabres [link] [comments]

2020.09.09 21:18 DeathToHeretics Check out the Simulation Hockey League!
Find us at:
Hello Minnesota Wild fans! First we would like to thank the mods for allowing us to share our league with you!
So what exactly is the Simulation Hockey League? We are a free, established sim-league about to enter our 56th season! The SHL allows you to create a character and take control of every aspect of their career. From creation there are tasks you can participate in on a weekly basis to earn points that go towards improving your player to perform better. With the conclusion of our most recent season comes the off-season activities, including the Simulation Major Junior Hockey League's (SMJHL) Entry Draft, the initial stepping stone for your player. You will be drafted to one of 12 excellent SMJHL teams, including 2 brand new expansion teams! You'll spend your first season getting acclimated to the league and fighting for the Four Star Cup, and afterwards you'll be drafted by one of the 18 Simulation Hockey League (SHL) teams. Work hard, stay active and you could earn the prestige of being the 1st overall draft pick!
BE-A-PRO EXPERIENCE - A rich, personal hockey experience that crosses between video games and fantasy hockey. Build your player exactly how you want with a variety of different styles. Be totally involved in your player from the beginning, and take them from rookie to the SHL Hall of Fame! Every game is simulated on Franchise Hockey Manager 6, and its wide array of attributes allows you to customize your player to the maximum. Success is based on your activity within our community, so the more involved you are, the greater your player can be! A link to our YouTube channel can be found here if you want to see what our daily streams are like!
FREE - As stated earlier, I want to emphasize that our community/site is completely free. It's free to join, free to participate, free to do what you want. Our league runs off the hard work of our community, we all serve each other in that regard. Every aspect of our site is run by our members. This isn’t a sales pitch, we’re just always looking for new members to fuel the already great community we have built!
DRAFT DAY - Experience what it is like to wait for your name to be selected, alongside your draftmates, vying for that top spot! This is a full draft experience where real people will draft you and spend their resources on your potential. Become the next big steal, or potentially the next big bust. Show the GMs you’ve got what it takes to be the face of their franchise!
REAL PEOPLE, REAL OWNERS - Every aspect of the league is run by real people from all around the world! Your draftmates, future teammates, general managers and the league’s head office are all real people you can interact with. Every decision from team lineups, draft day selections and last minute trade deadline deals are done by teams owned by members of the site, with plenty of opportunity for you to fill in those roles yourself! SHL superstar, team captain or scout, General Manager, Head Office; the site is full of opportunities for you to get involved however you so choose!
COMMUNITY - Once your player is drafted onto a team you will gain access to a private locker room for your organization, and their Discord server (should you choose to join there). Here is often where the closest relationships develop, and where you’ll make some great new friends to share your SHL journey with! You can spend time discussing the season, reflecting on your success, or just having a blast hanging out together! Of course you’ll find passionate hockey fans here, but with so many unique members, you’re bound to find people with lots of interests all over the site for you to get connected with.
Find us at:
submitted by DeathToHeretics to wildhockey [link] [comments]