ClioSport.net

Register a free account today to become a member!
Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

  • When you purchase through links on our site, we may earn an affiliate commission. Read more here.

Excel help - ideally before I resort to Hulk Smash and murder...



Darren S

ClioSport Club Member
First off - MASSIVE kudos to those poor victims of modern society who sit in Excel on a daily basis. I think I would prefer to chew on house-bricks than to spend any length of time trying to work out and at least begin to comprehend the logic used in formulas.

I'm pretty sure that there's a bunch of mathematical perverts sat on six-figure salaries, wanking themselves silly on how they can make the most simple function, beyond the mental realms of the common man. What I want is to total a column and check a value - not to learn borderline code for (=conconnnectate($A:$Z)*wednesday/3.41% shoe size)))))))))))) b****cks.....

Anyways..... rant box stepped down from. What I'm after is a total count against each member of staff that has reported a task.

I have used Excel in the 4,000+ tasks that I've logged on the system, to tell me how many unique staff name entries there in the 'reporter name' column. Excel came back with 121 unique members of staff across the 4,000 records - all good so far.

What I want to be able to do now is to quantify just how many times that person has reported a task. So for Step 1, I'd have the following raw data....


Reporter Name

John
John
Dave
Richard
Debbie
Sharon
John
Debbie
Tracey
Dave
Dave
Richard
etc...


For Step 2 - I've already got the names in an alphabetical order using a unique count...

Dave
Debbie
John
Richard
Sharon
Tracey...


For Step 3 - I want Excel to go through the 4,000 rows and tell me through a count, something like...

Dave 85
Debbie 10
John 24
Richard 5
Sharon 110
Tracey 18


I've been looking at COUNTIF statements and getting close, but to be honest - my level (and lack) of understanding is just frustrating me. I want to do something really quite simple, but the online texts that I've read are annoyingly just short of giving me the eureka answer.

Any help or assistance would be much appreciated. Using Excel should come with a health warning. How can something so mundane increase your stress and heart-rate to such levels!?
 
  Civic Type R FN2
Yes you need to use a countif formula.
Something like: =countif(a:a,d1)

Where A:A is the column containing your list of names who have done reports, and D1 is your unique list.
 

Darren S

ClioSport Club Member
Yes you need to use a countif formula.
Something like: =countif(a:a,d1)

Where A:A is the column containing your list of names who have done reports, and D1 is your unique list.
That cracked it - thanks!

I may have been complicating things slightly be referring to columns on other sheets, but I just copied that across to the main data sheet. Regardless - done!
 

Jamie

ClioSport Club Member
I always use sum and count ifs, even if i am considering only one variable, as I prefer the way the forumla is laid out. Just makes more sense to me.
 

Deeg

ClioSport Club Member
I always use sum and count ifs, even if i am considering only one variable, as I prefer the way the forumla is laid out. Just makes more sense to me.

I use them a lot as sense checks when using multiple data sets.

Probably my most used formulas, i’d guess.
 

GiT

ClioSport Club Member
  Shit little Yaris...
Excel guys and girls...

I've started a new job at work and we use far too many out of date and frankly awful looking excel sheets... but!

I have a column with say 15 cells that all have differing formulas (Some connecting to different sub-sheets / tabs on the same file).

How do I mass copy and paste so the formula's move? The only way it's doing it, is cell by cell by actually copying the formula from the formula bar to the next cell. It's properly not doing it as I expected (Copy - Paste (Formulas) )

Help.
 
  Civic Type R FN2
Can't you just copy and paste the cells as they are in the original spreadsheet, straight into the 2nd spreadsheet? Or when you go to paste, right click>Paste Special>Formulas
 

GiT

ClioSport Club Member
  Shit little Yaris...
Can't you just copy and paste the cells as they are in the original spreadsheet, straight into the 2nd spreadsheet? Or when you go to paste, right click>Paste Special>Formulas

Exactly what I thought, but the formulas don't go across. Example... I have formula's in C3 to C10 and want to copy them from there to E3 to E10. Highlight C3 to C10. Copy and then paste special (Formulas) and it just shows #REF or similar. Look in the formulas and they are incorrect.

I'll whip the sheet home with me at the weekend to give you an idea what I mean (I'll screen record it)

:)
 

Deeg

ClioSport Club Member
If you want to keep the reference points the same, but move the formula, then $ the reference points and then move then drag the formula, that should keep the reference points fixed.
 

Tim.

ClioSport Club Member
Would inserting a couple of columns ahead of column C work, or would that alter the references (assuming they haven’t been $’d)?
 
  Civic Type R FN2
Exactly what I thought, but the formulas don't go across. Example... I have formula's in C3 to C10 and want to copy them from there to E3 to E10. Highlight C3 to C10. Copy and then paste special (Formulas) and it just shows #REF or similar. Look in the formulas and they are incorrect.

I'll whip the sheet home with me at the weekend to give you an idea what I mean (I'll screen record it)

:)

Then it's probably a different version of Excel (2003 vs 2016 or something). Try saving the old file down as the same version of Excel then trying to copy and the formulas across. Like if the formula you want is in a .xls file try saving it as a .xlsx version or whatever the other spreadsheet file type is.
 

Oggy997

ClioSport Club Member
  997.1, Caddy, e208
Can you send me the sheet? We can go through it on WhatsApp or something...?
 

Darren S

ClioSport Club Member
No rage this time, but I was wondering what's the best way to have a quick query based on the date details below?


84​
29/05/2019​
11/06/2019
85
12/06/2019
25/06/2019
86
26/06/2019
09/07/2019
87
10/07/2019
23/07/2019
88
24/07/2019
06/08/2019
89
07/08/2019
20/08/2019
90
21/08/2019
03/09/2019
91
04/09/2019
17/09/2019
92
18/09/2019
01/10/2019
93
02/10/2019
15/10/2019
94
16/10/2019
29/10/2019
95
30/10/2019
12/11/2019
96
13/11/2019
26/11/2019


Essentially, I want on a separate sheet to be able to pick a date and it would return the value in the appropriate cell from Column A. So if I picked the 14th July 2019 for example, Excel would return the value of 87 as it's in the corresponding date range of Columns B and C.

Cheers,
D.
 

Tim.

ClioSport Club Member
You could use...

=LOOKUP(2,1/
($B$1:$B$7<=A11)/
($C$1:$C$7>=A11),
$A$1:$A$7)

Where your start dates are between B1 and B7
Your end dates are between C1 and C7
The number you want to return is between A1 and A7
And the date you enter into cell A11
 

Darren S

ClioSport Club Member
You could use...

=LOOKUP(2,1/
($B$1:$B$7<=A11)/
($C$1:$C$7>=A11),
$A$1:$A$7)

Where your start dates are between B1 and B7
Your end dates are between C1 and C7
The number you want to return is between A1 and A7
And the date you enter into cell A11

Thanks Tim - I'll have a try at that....
 

Darren S

ClioSport Club Member
You could use...

=LOOKUP(2,1/
($B$1:$B$7<=A11)/
($C$1:$C$7>=A11),
$A$1:$A$7)

Where your start dates are between B1 and B7
Your end dates are between C1 and C7
The number you want to return is between A1 and A7
And the date you enter into cell A11
Great stuff @Tim. - that worked fine.

Thanks again,
D.
 

GiT

ClioSport Club Member
  Shit little Yaris...
Right then bunch. New issue.

I have 2 Spreadsheets and the 2nd is used to read certain info from the first.

Now - I have a row being read from say B to Z.

What I am doing, is on the 2nd spreadsheet highlighting all the cells and mass formula changing the row selected in "Replace". This is very simple.

Is there a way to enter the row number I require into a cell, the press "Submit" or similar for a macro to run and do the change? This is preferred to each time selecting all the cells and mass changing in "Replace", It would feel / look more slick.

=[Test1.xlsx]Sheet1!B000 This is the current formula in each cell on the 2nd spreadsheet. I then run...

1430024


This will then replace the "000" with the 7 - thus showing the info in Row 7 from Spreadsheet 1.

GO!
 

Oggy997

ClioSport Club Member
  997.1, Caddy, e208
I can write this when I'm home for you, bit you'll need to alter the macro for your column/sheet names
 
  • Like
Reactions: GiT

Oggy997

ClioSport Club Member
  997.1, Caddy, e208
I'll be free to do this shortly, are you on Skype/Teams/MSN/AOL/IRC/ICQ or similar so I can live chat with you to ensure its how you want
 

Tim.

ClioSport Club Member
Could you do it a different way and replace 000 with ‘&A1’.

In cell A1 you’d populate the row you want to reference I.e. 7.

Not even sure if that would work, haven’t looked at excel for two weeks.
 

GiT

ClioSport Club Member
  Shit little Yaris...
...are you on Skype/Teams/MSN/AOL/IRC/ICQ or similar so I can live chat with you to ensure its how you want

Skype (Nope) /Teams (The hell is that?) /MSN (It's 2019, not 1999!) /AOL (See previous lol) /IRC (See previous minus 4 years!) /ICQ (YES!!! No - not really lmao.)


Erm Twatter? FB Messenger? :)
 

GiT

ClioSport Club Member
  Shit little Yaris...
Could you do it a different way and replace 000 with ‘&A1’.

In cell A1 you’d populate the row you want to reference I.e. 7.

Not even sure if that would work, haven’t looked at excel for two weeks.

If that would work, that's all i'd need to do - would be perfect!
 

Oggy997

ClioSport Club Member
  997.1, Caddy, e208
Code:
=INDIRECT("'[Book1.xlsx]" & "Sheet1" & "'!" & "B" & I$1)

Book1.xlsx = the workbook in question
"Sheet1" = The worksheet fromt he 2nd workbook
"B" = The column you want to look up
I$1 = The cell where the row number you want is entered

*N.B. - Indirect requires the referenced workbook to be open or the formula fails.
 

Darren S

ClioSport Club Member
Right - I'm clearly being stupid here!

I've found a Worldpay webpage that lists many field and format types that I'm trying to import into Excel. It imports fine, apart from a few expected formatting issues.

The obvious one comes under one where it says field length of 1-50. Excel in its infinite wisdom tries to apply this figure as a date field - so that all 46 examples of this within the newly created spreadsheet appear as Jan-50. Worse.... you click on those cells and it reads it as 01/01/1950. FFS Excel - just paste it as plain text - except plain text isn't an option. Using Paste Special, the only option that allowed an import was a Refreshable Web Query - and I suspect that this is where the spanners are getting thrown in.

That said, the output looks fine - but being Excel, it completely ignores the selection of text I've made on the webpage and decided to import the entire page itself. Great.

Anyways, I go straight to Find & Replace. Find any instance of Jan-50....... can't find any. What? Like that one immediately on Row 33 and the other 45 examples that I can see with my naked eye? Nope. OK - what about finding 01/01/1950.....46 examples reported. This is good. Replace all 01/01/1950 examples with 1-50. Excel says - Ta-da! Done it - I've made 46 replacements. Except you haven't, have you (you lying little s**t).... they are still showing Jan-50. I've tried it with both the Match Case and Match entire cell contents boxes ticked - makes no difference.

Why do I have to try and unpick Excel being clever - applying date formats when none existed before and it telling me that it's done stuff that is clear, blatant lies. So close to just manually typing them in - 46 cells by hand will be much quicker than getting my head around Excel logic.

1588667441704.png
 

Darren S

ClioSport Club Member
Thanks for that, but it won't find any instance of 18264. It does mention that I might be trying to do this on a Protected Sheet, but I'm pretty sure it isn't.

I was just after transferring the table contents from the link below into Excel.

 

Darren S

ClioSport Club Member
Hi all,

Another simple one (and surprisingly, I've not vented with this yet!) - just with regards to a days passed calculation.

I've basically got a column like so:-

Due Finished
22/07/2021
20/08/2021
03/09/2021
28/07/2021
13/08/2021
27/08/2021


And would just like a column next to it that calculated the days since that due date and today.

I've kind of got lost in DATE, DATEIF, TODAY() commands - and rather than plug away at it, was hoping for someone far more patient and switched on than I, to help out.

Cheers!
 
  172 Cup
I usually just add a hidden cell containing =TODAY() (this will return todays date), lets say this was cell A1
Then formula will be =$A$1-B1, for the first date in the column (say all dates are in the B column)
 


Top