Hi fellow Hiveians,

Today I'm here with what is to me, some Excel fun! Check out what I learned and will be keeping for future reference.

So in the computer world, often times if you are working in an IT type role you are given requests for things and that may be in Excel. Those requests are naturally urgent so you need to crunch some serious fucking numbers and I don't like doing that, I want Excel to do my work and I just massage it along sensually to get it to cooperate with me.

Formula One

What the formula I want to use should be doing is taking all of the entries in column H and comparing them individually to the entries in column I. Column I has the numerical values I want to keep (they are unique identifiers for a subset of locations) so checking all of the values in column H to those in column I was what I was looking hours for. It took quite a bit of trial and error but with determination you can certainly accomplish it!

So this was one of the formulas I was playing around with. The IF MATCH formula seems to be useful in some regards but unfortunately not for what I needed it to. It was constantly returning an error so that didn't work. More work would be needed on these formulas for me to figure out what I need to do to get them to work. It could've been promising if I could understand it more, I think.

I then tried a new formula that was promising in my small, sample data but when I applied it to my larger data it failed muster unfortunately. I would like to figure out if I did something wrong or what.

=AND(COUNTIF(H:H, I2),COUNTIF(I:I, I2)) which works for the scenario I was in, in my sample data but again didn't live up to the expectation when applied to the larger dataset.

When I ran that formula above, Excel was having a real tough time calculating it but that's because I had 50k entries so there was a LOT of data in there! Precisely why I needed to have Excel do the work for me of filtering this junk out that I didn't want.

So once I applied that formula to my larger data set it also didn't want to work. Damn! So back to the drawing board but I think I may have found one that does work!

Thank god I am persistent! I finally got a formula that does indeed seem to work! I had to tweak it a little bit from the first iteration I was using but the formula is based on color rules which is what I was originally intending it to be. Thankfully I applied it to my large dataset and it worked like a charm! I filtered down the data I was working with from 50k entries down to 3,800. From there I was able to further filter out unnecessary stuff down to a nice 2,700 rows.

With this color formula it was important that I highlight column H as the row I want this to be applied to. I used the following formula =COUNTIF($I:$I, $H1) to get what I want. I then just used a color filter and only selected the rows in which the color was applied, knowing that was the data I wanted and I got the information I needed.

Raw to Cooked

So as you can see, my raw data was fucking crazy big numbers. That's when I select all. I don't want that at all, that's a whole lot of stuff I don't care the least bit about. 40541 unique rows, I don't think that's useful for me! That was even filtered, completely raw data, it was 50k rows.

Once I massaged the data and it cooperated with me, I was able to get things to a much more reasonable level. I was able to give some much better looking information, whittling it down even further once I removed more things I didn't care about, to 2,500 rows.

Share

Have you got any fun Excel things to share with me? Drop me a comment and let me know! It's not a program for everyone, I didn't use it at all really until I transitioned into my IT role now so unless you need to use it, this is boring shit! I would love to connect with more people who are Excel fans like myself, though!

Connect with me!
Did you enjoy this post or my other posts? Please consider following me for more! @cmplxty

Do you want to get paid, in crypto, for searching the internet? Try using and signing up for Presearch to earn some great crypto! I've currently got 1,519 PRE tokens, with a market value of $25.57. It doesn't sound like a lot but when you search using sites like Google you get paid $0! Join Presearch to break Google's stranglehold on the internet searches. If you'd like to sign up, use my referral link below and spread the word!
https://www.presearch.org/signup?rid=513043

Still using hive.blog? Upgrade! PeakD is a much better user-interface, gives loads more information at your fingertips and has some of the best developers behind it. Give it a try, go to www.peakd.com and see how much easier it is. You just need to use Hivesigner or Peaklock some of the most secure ways to log into other Hive frontends.