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

[Darren S]

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]

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]

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!

 

[Pep]

Does Sharon get a bonus for doing more work than the others?

 

[MarkCup]

There's also COUNTIFS if you want to total things based on more than one variable/key.

 

[Jamie]

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.

 

[jamesycc]

Excel has a strange logic to it.

Just when I think I've cracked it goes all bloody spazmo on me... ?

 

[Deeg]

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]

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]

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]

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]

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.]

Would inserting a couple of columns ahead of column C work, or would that alter the references (assuming they haven’t been $’d)?

 

[Deeg]

The reference would move without the $’s.

 

[yourcodenameisnick]

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]

Can you send me the sheet? We can go through it on WhatsApp or something...?

 

[Darren S]

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.]

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]

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]

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]

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...

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

GO!

 

[Oggy997]

I can write this when I'm home for you, bit you'll need to alter the macro for your column/sheet names

 

[GiT]

I can write this when I'm home for you, bit you'll need to alter the macro for your column/sheet names

Sheer legend fella!

 

[Oggy997]

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.]

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]

...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]

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!

 

[Jamie]

Not all hero’s wear capes.

 

[Oggy997]

=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]

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.

 

[The Psychedelic Socialist]

1st Jan 50 is actually just Excel formatting the number '18264'.

Try finding and replacing that number.

 

[Darren S]

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.

Payout Batch

support.worldpay.com

 

[MarkCup]

Have you tried searching for 54789?

18,264 is January 1950 whereas 54,789 is January 2050.

 

[Darren S]

Have you tried searching for 54789?

18,264 is January 1950 whereas 54,789 is January 2050.

Going to try that tomorrow! Thanks. 👍

 

[Darren S]

Going to try that tomorrow! Thanks. 👍

Didn't like that either. Must be something I'm doing wrong.

Happy for you to have a look, though I understand why the forum won't allow the attachment of XLSX files.

 

[Tommo.]

@Darren S - I've done it, PM me your email address and I'll send it over.

 

[Darren S]

@Tommo. PM sent matey!

 

[Darren S]

Spot on - perfect!

Cheers,
D.

 

[Darren S]

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!

 

[Rob G]

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)