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!

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



Darren S

ClioSport Club Member
  Black Gold 182
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!?
 

yourcodenameisnick

ClioSport Club Member
  182 Cup
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
  Black Gold 182
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
  Audi S4
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
  208 GiT BPS
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.
 

yourcodenameisnick

ClioSport Club Member
  182 Cup
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
  208 GiT BPS
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)?
 

yourcodenameisnick

ClioSport Club Member
  182 Cup
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.
 

Oggy172

ClioSport Club Member
  RB182, 987, Lupo Gti
Can you send me the sheet? We can go through it on WhatsApp or something...?
 

Darren S

ClioSport Club Member
  Black Gold 182
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
  Black Gold 182
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
  Black Gold 182
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
  208 GiT BPS
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!
 

Oggy172

ClioSport Club Member
  RB182, 987, Lupo Gti
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

Oggy172

ClioSport Club Member
  RB182, 987, Lupo Gti
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
  208 GiT BPS
...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
  208 GiT BPS
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!
 

Oggy172

ClioSport Club Member
  RB182, 987, Lupo Gti
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.
 


Top