Excel People

[RobinRobin 9136]

[wykikitoon 16452]

Right G-Dogg & F-Mann.

I am trying to figure out the best way to do this one.  We have a race league that is held weekly throughout the summer months.  You get a set number of points per participation.  Not everyone competes every week due to other commitments etc.  I want the easiest way of working out total points with the least amount of manual imputing as possible.

[Gemmill 34133]

You need to provide more details. Framework of a spreadsheet or whatever. This is too broad a request as it is, you PENIS.

[wykikitoon 16452]

15 minutes ago, Gemmill said:

You need to provide more details. Framework of a spreadsheet or whatever. This is too broad a request as it is, you PENIS.

 

We have a weekly event and basically I need to total up the points  

 

At the moment I have;

 

 

 

So we give 10 points per ride.  So in this instance Rider A would have a total of 30 points.

Edited May 26, 2023 by wykikitoon

[Gemmill 34133]

1 minute ago, wykikitoon said:

 

We have a weekly event and basically I need to total up the points  

 

At the moment I have;

 

 

 

So we give 10 points per ride.  So in this instance Rider A would have a total of 30 points.

 

Just do a COUNTIF multiplied by 10.

 

So have a separate table with just rider names and then a COUNTIF for each of them.

[wykikitoon 16452]

7 minutes ago, Gemmill said:

 

Just do a COUNTIF multiplied by 10.

 

So have a separate table with just rider names and then a COUNTIF for each of them.

<gif> You're the man </gif> 

[Gemmill 34133]

5 minutes ago, wykikitoon said:

<gif> You're the man </gif> 

 

BTW to arrive at your list of rider names, don't do that manually. Use the following:

 

=UNIQUE()

 

And the range in the brackets should be the name column from your previous screenshot. That way you don't have to keep adding new people to your score table.

 

You can wrap your UNIQUE() in a SORT() as well if you want it sorted alphabetically.

[The Fish 8926]

Fuck it @Gemmill it's better with pictures so I'll stick it here.

 

I need to return purely those fixes that have failed status and no success status. 

 

 

Pivot table returns this;

 

so I want to return only the lwfra1oraavp01 and those like it. 

 

The column to the right of the pivot contains the following; =IF(AND(NOT(ISBLANK(K5)),(ISBLANK(L5))),J5,"Success"), which is fine, but I want to return an array with solely the jobs that are not success. I'd prefer to pull from the raw data rather than the pivot. 

 

[Gemmill 34133]

Yeah Dave, we're using power query here fo sho. It'll spit out a table with only the rows you want to see, no formulas required. 

 

Where does the initial data come from? Does that get spat out of some system as a csv? Do you have one continuous file for this or do you get a new one each day? 

[Dazzler 5215]

16 minutes ago, The Fish said:

Fuck it @Gemmill it's better with pictures so I'll stick it here.

 

I need to return purely those fixes that have failed status and no success status. 

 

 

Pivot table returns this;

 

so I want to return only the lwfra1oraavp01 and those like it. 

 

The column to the right of the pivot contains the following; =IF(AND(NOT(ISBLANK(K5)),(ISBLANK(L5))),J5,"Success"), which is fine, but I want to return an array with solely the jobs that are not success. I'd prefer to pull from the raw data rather than the pivot. 

 

Have you tried a UNIQUE(FILTER())  formula?

[Dazzler 5215]

3 minutes ago, Dazzler said:

Have you tried a UNIQUE(FILTER())  formula?

Just as a very basic example. Raw data:

 

Unique/Filter formula returns:

 

 

This is based on a formula of =UNIQUE(FILTER('Raw Data'!$A:$A,'Raw Data'!$B:$B="Fail"))

[The Fish 8926]

9 minutes ago, Dazzler said:

Just as a very basic example. Raw data:

 

Unique/Filter formula returns:

 

 

This is based on a formula of =UNIQUE(FILTER('Raw Data'!$A:$A,'Raw Data'!$B:$B="Fail"))

 

Yeah, that just returns the unique job that have a failure, unfortunately. Jobs can fail and succeed on the same day, which is causing the issue. I need jobs that fail, and only fail. 

[Gemmill 34133]

Dave. Stay focused. 

 

Where does the initial data come from? Are you copying it in from somewhere? Do you get an output from some system? 

[The Fish 8926]

16 minutes ago, Gemmill said:

Dave. Stay focused. 

 

Where does the initial data come from? Are you copying it in from somewhere? Do you get an output from some system? 

 

Delivered as an excel, by 3rd party, hosted on a sharepoint. They lift raw data from their server.

[The Fish 8926]

1 hour ago, Gemmill said:

Yeah Dave, we're using power query here fo sho. It'll spit out a table with only the rows you want to see, no formulas required. 

 

Where does the initial data come from? Does that get spat out of some system as a csv? Do you have one continuous file for this or do you get a new one each day? 

 

New one every month

[Gemmill 34133]

OK so if your company has enterprise licences, you can link direct to the share point and you just press refresh in your spreadsheet and it spits out your new table.

 

Failing that, we just shift the excel somewhere else. 

 

Is the above first screenshot an exact version of what the spreadsheet looks like? This one:

 

 

And how many times does it do the test? Are your possible combos basically

 

Pass first time, no second test

Fail first time, pass second test

Fail first time, fail second time 

 

And you want all of the ones that fall into category 3, and don't care about the others? 

[Gemmill 34133]

Also, does the original data have the same column headings? That bit's important. And did you say some columns contain a timestamp which you don't care about? 

 

I'll PM you. 

[The Fish 8926]

15 minutes ago, Gemmill said:

OK so if your company has enterprise licences, you can link direct to the share point and you just press refresh in your spreadsheet and it spits out your new table.

 

Failing that, we just shift the excel somewhere else. 

 

Is the above first screenshot an exact version of what the spreadsheet looks like? This one:

 

 

And how many times does it do the test? Are your possible combos basically

 

Pass first time, no second test

Fail first time, pass second test

Fail first time, fail second time 

 

And you want all of the ones that fall into category 3, and don't care about the others? 

 

Yeah it's a screengrab of the table exactly

 

The fix can be attempted any number of times. So, it could fail once, then work on the second, or fail 6 times and succeed on the 7th. 

 

I want only the fixes that fail and are at no point successful.

[Gemmill 34133]

Right I'll get back to you in a bit. At the vet with the cat atm. 

[The Fish 8926]

43 minutes ago, Gemmill said:

Right I'll get back to you in a bit. At the vet with the cat atm. 

Is that code for having a shit?

[MrBass 2181]

21 hours ago, The Fish said:

Is that code for having a shit?

 

You're getting confused with 'dropping the kids off at the pool'.

 

I think what Gemmill said translates to 'taken my side piece to the doctors'. 

[Dazzler 5215]

49 minutes ago, MrBass said:

 

You're getting confused with 'dropping the kids off at the pool'.

 

I think what Gemmill said translates to 'taken my side piece to the abortion clinic'. 

FYP

[MrBass 2181]

[ewerk 26100]

41 minutes ago, Dazzler said:

FYP

 

You think Gemmill is forking out for a fancy abortion clinic? When he has a wardrobe full of these?

 

[Monkeys Fist 38494]

12 minutes ago, ewerk said:

 

You think Gemmill is forking out for a fancy abortion clinic? When he has a wardrobe full of these?

 

No need. 
He has a nutsack full of these.