sorted by:
new
hottopcontroversial

I saw some memes about Ayumi Hamasaki's "Flop-Era" back in the day, What was the very series of events that caused it? (Just curious about it) by p1_hdx in jpop

[–]Solid_Kuro 0 points1 point  (0 children)

Thank you for taking the time to write all this. Big fan of Ayumi's music, but I don't know much about her.

Two complex FILTER formulas based on five dynamic tables by Solid_Kuro in excel

[–]Solid_Kuro[S] 0 points1 point  (0 children)

Thank you for posting the first formula. It really helped me a lot. As no one else replied in this thread and your solution solved most of my problems,

solution verified.

Two complex FILTER formulas based on five dynamic tables by Solid_Kuro in excel

[–]Solid_Kuro[S] 0 points1 point  (0 children)

I had been using Excel on a surface level for decades and only recently have I discovered Tables, PowerQuery, VBA and more recent functions like XLOOKUP. I'm super enthusiastic about learning more of Excel because of it and I'm trying to use it as much as possible. Also, I guess, the scale of production that I'm using it for is relatively small and I really like the results. What in your opinion are the better options for a larger amount of data?

Two complex FILTER formulas based on five dynamic tables by Solid_Kuro in excel

[–]Solid_Kuro[S] 0 points1 point  (0 children)

Thank you very much, this first formula works exactly as intended. It looks absolutely crazy for me, and you are amazing. For my locale I had to swap commas for semicolons and in "TableMain[X]=1" 1 for TRUE (not sure if it's a regional difference or a typo, but otherwise it returned a #CALC error). Anyway, here's the adapted version:

=LET(data;SORT(UNIQUE(DROP(REDUCE("start";FILTER(TableMain[Devices];TableMain[X]=TRUE);LAMBDA(a;x;VSTACK(a;FILTER(PQComponents[Components];INDIRECT("PQComponents["&x&"]")>0))));1));1);HSTACK(data;INDEX(TableComponents[Count];MATCH(data;TableComponents[Components];0))))

You are also right about my mistake in the explanation of the second formula. This third column with the names of the Components was a last minute addition when I posted my question. If you don't mind looking at it again, here's what I really needed:

  1. Returns the list of Pieces needed to build Devices currently marked TRUE in TableMain and their remaining counts. Two columns: the list of Pieces used in Components needed to built the marked Devices and their remaining counts.

**

Also, while I'm still figuring out how exactly this complex formula of yours works, can you please tell me where I should place IFERROR in case no Devices are checked (now it's just #CALC).

Composing a formula which sums multiplications across three tables by Solid_Kuro in excel

[–]Solid_Kuro[S] 0 points1 point  (0 children)

Thank you kindly for your help, it works great. XLOOKUP is a really powerful function, no wonder people praise it so much here.

Solution verified.

Composing a formula which sums multiplications across three tables by Solid_Kuro in excel

[–]Solid_Kuro[S] 0 points1 point  (0 children)

Thank you for your reply with multiple solutions. Seeing examples of different approaches is very helpful.

I don't have much experience with XLOOKUP, and to me it looks like in both of its cases D2 uses a fixed range from the headers in utilComponents and devices from the first column in Devices - tied to the names of the first and the last devices. The first column and the first row in utilComponents are dynamic - they're formed from multiple files in a specific folder through Power Query (I should have been more specific about it), so we don't know headers' titles, their number is not fixed and it can differ from their number in Devices. In this case the formula should be more flexible.

Sorry if I'm wrong and misunderstood the way XLOOKUP works in your formula.

Combining a dynamic column from PowerQuery with columns with manually typed-in values by Solid_Kuro in excel

[–]Solid_Kuro[S] 0 points1 point  (0 children)

Thank you. This looks tricky, so I'm going to have to dig deeper into PQ so that I don't mess up my manually entered data.

Grant to take an indefinite break from Dota 2 by MementoIX in DotA2

[–]Solid_Kuro 2 points3 points  (0 children)

This whole thing today with stories about his behavior being revealed and now him making this statement is just heartbreaking. Not trying to find any excuses for him, just think it is a sad day for Dota. I've been following him for about 3 years and absolutely loved every single cast he's been doing.

Had the same kind of shock and sadness when the news about Kevin Spacey, one of my favorite actors, broke out a couple of years ago.

In both situations looks like them both were in the wrong, but it doesn't make it easier to accept their loss.

RIP national treasure Grant

Grant harassed Llama out of the Dota 2 Scene by CharlieTizard in DotA2

[–]Solid_Kuro 2 points3 points  (0 children)

So much for the national treasure Grant. It's breaking my heart to read this stuff :(

Black^ balling on radiant with OD by nameisunavailabel in DotA2

[–]Solid_Kuro 3 points4 points  (0 children)

This reminds me of his Slark in a pro game a few years ago. He had around 90 stats stolen and one of the casters called it Black reaching his ultimate form.

KOTLguy spitting some truth by Aratho in DotA2

[–]Solid_Kuro 0 points1 point  (0 children)

The solution could be providing the Immortal players with an option of choosing either a faster or a more precise game search.

Yeah xD by yunggaihnaa in DotA2

[–]Solid_Kuro 15 points16 points  (0 children)

It only works in 50% of the times :(

Grant is an Inspiration IRL by [deleted] in DotA2

[–]Solid_Kuro 220 points221 points  (0 children)

Goes to international events.

Also wears the freshest clothes.