Author Topic: Excel help  (Read 59472 times)

Offline tubby

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 25,082
  • Destroyed Cowboy
Re: Excel help
« Reply #200 on: April 26, 2019, 03:52:57 pm »
You just need them displayed horizontally instead of vertically?  Should be able to just copy and paste special > transpose.  Unless you're looking for something else?
Sit down, shock is better taken with bent knees.

Offline Titi Camara

  • Hey, wanna hear the new dubstep song I wrote? Wub, Wub, Wub! Wubba Lubba Dub Dub! I'm Pickle Rick with hirsute areolae!
  • RAWK Staff
  • Legacy Fan
  • ******
  • Posts: 19,211
  • Number 21 of the Crazy 88
Re: Excel help
« Reply #201 on: April 26, 2019, 03:59:24 pm »
Hopefully somebody can help me with this as I’m completely baffled, I managed to do this once and have lost the URL of the page that explained this to me  >:(

Basically I have a list of around 500 numbers excel such as:

123
12234
12345
123456
10989
2818
121313

Etc Etc

What I want to do is organise this so that they are not repeated as above, but instead like:

123; 12234; 12345; 123456; 10989; 2818; 121313

I know there’s a manual way of doing this but I know there is some other way that is more automatic, I also can’t remember whether this involves just excel or takes in word as well?

Gah, hopefully somebody can help, thanks in advance!
If cell A1 contains "123"...

In cell B1 enter =A1&";"

In cell B2 enter =B1&" "&A2&";"

Extend the formula in cell B2 to the end of the data by double clicking in the right hand corner of the cell when a cross appears.

The last populated cell in column B will show the data in the format you require.

Offline Peabee

  • SKPB! Is goin' down der Asd.....der Waitrose.....anyone wannany hummus?
  • Matchday Commentator
  • Legacy Fan
  • ******
  • Posts: 16,366
Re: Excel help
« Reply #202 on: May 5, 2019, 02:59:29 am »
Hopefully somebody can help me with this as I’m completely baffled, I managed to do this once and have lost the URL of the page that explained this to me  >:(

Basically I have a list of around 500 numbers excel such as:

123
12234
12345
123456
10989
2818
121313

Etc Etc

What I want to do is organise this so that they are not repeated as above, but instead like:

123; 12234; 12345; 123456; 10989; 2818; 121313

I know there’s a manual way of doing this but I know there is some other way that is more automatic, I also can’t remember whether this involves just excel or takes in word as well?

Gah, hopefully somebody can help, thanks in advance!

as Tubby suggests: copy the cells and 'paste special -> transpose' in another sheet (assuming you want it in a separate sheet).

OR

highlight the same number of cells horizontally and type '=TRANSPOSE(ARRAY)' where ARRAY is your data, then SHIFT+CTRL+ENTER.  You can do this in the same spreadsheet or a new sheet.

I'm assuming you just want your data in a row rather than actually having semi-colons? Otherwise, follow Titi's advice if that's what you require, or type ' =TRANSPOSE(ARRAY)&";" ' in the above.



 
We aren't walking through the storm now - we are the storm.

Offline PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #203 on: May 17, 2019, 11:23:25 pm »
I am stuck at a problem with excel and was wondering if anyone here could help me with the right formula.
I am creating an excel table for my peer group where they login their working hours, and it will be used to calculate their overtime.

Cell A5 has number of working days in a month. This is of "general" Format.
Cell A6 has worked/clocked hours. This is of "hh:mm:ss" format.

This is where it gets difficult for me.

Let us assume each person has to work 4 hours a day. If a person works for 3 days, it must return 12:00:00. If a person works for 5 days, it must return 20:00:00.

How do I convert the "general" format to "hh:mm:ss" format?
I'll happily come and solve all your Excel issues if I get overtime after four hours a day!
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline Giz a Gobble

  • Scouse Turkey that's hard to swallow.....
  • Main Stander
  • ***
  • Posts: 147
  • JUSTICE FOR THE 97.
Re: Excel help
« Reply #204 on: June 19, 2019, 02:57:17 pm »
Thanks for the previous help everyone. I've got headbanging issue that I was hoping had a much simpler solution.

I have a spreadsheet with around 11,000 names and telephone numbers ie:

Invoice No   Name  Email Address
1760 John Smith    John@smith.com
1760 John Smith    Smith@John.com
1760 John Smith    Johnnysmith@me.com

1780 Jodie Smith   Jodie@smith.com
1780 Jodie Smith   Smith@john.com
1780 Jodie Smith   Jodiesmith@me.com

What I'm trying to do is merge this all into just two rows and across five rows at the top, ie:

1760   John Smith    John@smith.com   Smith@John.com   Johnnysmith@me.com
1780   Jodie Smith   Jodie@smith.com   Smith@jodie.com  Jodiesmith@me.com

Does this make sense? I've tried multiple things (Vlook up etc) and it isn't getting anywhere. I know about Transforming the range but that is quite manual for each entry and as there are so many, it'll take forever to do one by one...

Offline Dan The Man 28373

  • Legacy Fan
  • ******
  • Posts: 1,209
  • We Always win in Rome!!
Re: Excel help
« Reply #205 on: June 19, 2019, 05:42:33 pm »
Thanks for the previous help everyone. I've got headbanging issue that I was hoping had a much simpler solution.

I have a spreadsheet with around 11,000 names and telephone numbers ie:

Invoice No   Name  Email Address
1760 John Smith    John@smith.com
1760 John Smith    Smith@John.com
1760 John Smith    Johnnysmith@me.com

1780 Jodie Smith   Jodie@smith.com
1780 Jodie Smith   Smith@john.com
1780 Jodie Smith   Jodiesmith@me.com

What I'm trying to do is merge this all into just two rows and across five rows at the top, ie:

1760   John Smith    John@smith.com   Smith@John.com   Johnnysmith@me.com
1780   Jodie Smith   Jodie@smith.com   Smith@jodie.com  Jodiesmith@me.com

Does this make sense? I've tried multiple things (Vlook up etc) and it isn't getting anywhere. I know about Transforming the range but that is quite manual for each entry and as there are so many, it'll take forever to do one by one...

It's how you set up the raw data.  If you have the original table set up as 5 rows by 3 columns (new data added to a new column of 5 rows).

                   Column
Row             A                 B                                C                               
1                 Invoice No    1760                           1780
2                 Name         John Smith                   Jodie Smith
3                 Email         John@smith.com           Jodie@smith.com
4                                 smith@john.com           smith@jodie.com
5                                 johnnysmith@me.com   jodiesmith@me.com

So if you copy & transpose that original table, you will get it in the format you want

Invoice No            Name                                          Email
1760                    John Smith                                  John@smith.com               smith@john.com               johnnysmith@me.com
1780                    Jodie Smith                                 Jodie@smith.com               smith@jodie.com              jodiesmith@me.com

Not sure if this will help you though if you can't easily change the layout of your original data.

Offline Red_Bear

  • RAWK Supporter
  • Anny Roader
  • ******
  • Posts: 302
  • We all Live in a Red and White Kop
Re: Excel help
« Reply #206 on: June 20, 2019, 07:47:28 am »
Are there always 3 rows for each person in the initial data, or does that vary? Either way, I’m pretty sure I can come up with something that should do it for you, but it’ll be slightly different depending on that.

Offline -Willo-

  • -the wisp-
  • Legacy Fan
  • ******
  • Posts: 13,483
Re: Excel help
« Reply #207 on: June 22, 2019, 08:34:15 am »
I don't know how to explain this so sorry if this is awful, I need to create a sheet on excel that leads the user to the end based on what they select.

So they begin with OPTION1, OPTION 2, OPTION 3.

And if they select OPTION 1, then this brings them to another bunch of options, and this repeats a few times until it finishes. I've tried to lay it out below.


OPTION 1             OPTION 2               OPTION 3

*SELECTS OPTION 1*

OPTION 1.1        OPTION 1.2            OPTION 1.3

*SELECTIONS OPTION 1.1*

OPTION 1.11       OPTION 1.12          OPTION 1.13           OPTION 1.14

*SELECTIONS OPTION 1.12*

OPTION 1.121        OPTION 1.122         OPTION 1.123        OPTION 1.1234


ETC...

Is this possible?

Offline Thush

  • Spawwow, Tit. Anal Chat is "Equidistant between chit-chat and analysis"
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 3,072
  • It's pronounced "Toosh"
Re: Excel help
« Reply #208 on: June 22, 2019, 09:46:12 am »
Something like this sounds like what you want.

https://trumpexcel.com/dependent-drop-down-list-in-excel/

Offline TepidT2O

  • Deffo NOT 9"! MUFC bedwetter. Grass. Folically-challenged, God-piece-wearing, monkey-rubber. Jizz aroma expert. Operating at the lower end of the distribution curve...has the hots for Alan. Bastard. Fearless in transfer windows with lack of convicti
  • Lead Matchday Commentator
  • Legacy Fan
  • ******
  • Posts: 93,586
  • Dejan Lovren fan club member #1
Re: Excel help
« Reply #209 on: June 22, 2019, 10:03:34 am »
Ohh... that’s useful
“Happiness can be found in the darkest of times, if one only remembers to turn on the light.”
“Generosity always pays off. Generosity in your effort, in your work, in your kindness, in the way you look after people and take care of people. In the long run, if you are generous with a heart, and with humanity, it always pays off.”
W

Offline PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #210 on: June 22, 2019, 10:10:10 am »
Something like this sounds like what you want.

https://trumpexcel.com/dependent-drop-down-list-in-excel/
The Trump excels. The biggliest Trump. The bestest Trump.
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline -Willo-

  • -the wisp-
  • Legacy Fan
  • ******
  • Posts: 13,483
Re: Excel help
« Reply #211 on: June 22, 2019, 10:24:54 am »
Something like this sounds like what you want.

https://trumpexcel.com/dependent-drop-down-list-in-excel/

Thank you, much appreciated.

Will give this a go, mine is going to be drop down lists within drop down lists so hopefully I don't get confused halfway ;D

Offline Giz a Gobble

  • Scouse Turkey that's hard to swallow.....
  • Main Stander
  • ***
  • Posts: 147
  • JUSTICE FOR THE 97.
Re: Excel help
« Reply #212 on: June 24, 2019, 10:56:55 am »
Thanks everyone, managed to sort it in the end!  :D

Offline CHOPPER

  • Bad Tranny with a Chopper. Hello John gotta new Mitre? I'm Jim Davidson in disguise. Undercover Cop (Grammar Division). Does Louis Spence. Well. A giga-c*nt worth of nothing in particular. Hodgson apologist. Astronomical cock. Hug Jacket Distributor
  • Legacy Fan
  • ******
  • Posts: 52,393
  • Super Title: Not Arsed
Re: Excel help
« Reply #213 on: July 2, 2019, 03:54:40 pm »
sorted now.



 
« Last Edit: July 2, 2019, 05:58:26 pm by CHOPPZBOT »
@ Veinticinco de Mayo The way you talk to other users on this forum is something you should be ashamed of as someone who is suppose to be representing the site.
Martin Kenneth Wild - Part of a family

Offline jackh

  • Has a blog but doesn't like to talk about it. Slightly obsessed with the colour orange for some weird reason......
  • RAWK Scribe
  • Legacy Fan
  • ******
  • Posts: 10,692
    • @hartejack
Re: Excel help
« Reply #214 on: August 3, 2019, 12:51:56 pm »
I'm getting a message saying one of my .xlsx files cannot be opened: "file format or file extension is not valid - verify that the file has not been corrupted and that the file extension matches the format of the file".

Still struggling to open this, despite having run through a few quick suggestions I found ... any ideas?

Offline Lee1-6Liv

  • Daddy Discord
  • Legacy Fan
  • ******
  • Posts: 3,919
Re: Excel help
« Reply #215 on: October 9, 2019, 04:26:09 pm »
Hi All

I'm in need of some help - i'm trying to use conditional formatting to highlight individual cell when a word is typed. So if i start to type the word google - the cell containing the word google highlights red with a yellow font.

So if i type g or goo the cell containing the word google is highlighted.

In the past i've used this formula =IF(ISBLANK($G$3), 0,SEARCH($G$3,$B5&$C5&$D5&$E5&$F5)) which works a treat to highlight a row.

but i just want to highlight a single cell rather than an entire row.

My search box is in cell G3

the only cells that contain any text are in
C5,C7,C9,C11, C14, C16, C18, C20
E5,E7,E9,E11, E14, E16, E18, E20
G5,G7,G9,G11, G14, G16, G18, G20
I5,I7,I9,I11, I14, I16, I18,I20.

All these cells are actually hyperlinks, so if you click on C5 it will take you to google, if you click on c7 it will take you to mrexcel, c9 youtube etc etc.

The spreadsheet is just hyperlinks or blank cells.
The spreadsheet goes from cell B4 to J21.

Any advice or guidance gratefully received.


Offline PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #216 on: October 9, 2019, 05:04:35 pm »
Are we allowed to use VBA macros for this?
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline RobbieRedman

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 10,965
Re: Excel help
« Reply #217 on: January 7, 2020, 01:35:39 pm »
Hello

I'm trying to use data validation for a drop down selection where you can choose more than one option. From my research i think the user will need to go back and choose option 2, then again go back and choose any more if need be, no problem.

However the vba macros i have tried just dont work

so is there a way i can do this without vba and just a formula>?

thanks

Offline TepidT2O

  • Deffo NOT 9"! MUFC bedwetter. Grass. Folically-challenged, God-piece-wearing, monkey-rubber. Jizz aroma expert. Operating at the lower end of the distribution curve...has the hots for Alan. Bastard. Fearless in transfer windows with lack of convicti
  • Lead Matchday Commentator
  • Legacy Fan
  • ******
  • Posts: 93,586
  • Dejan Lovren fan club member #1
Re: Excel help
« Reply #218 on: January 7, 2020, 02:32:15 pm »
Can’t you just validate the cells and define it as a list?

Then you type in your list comma separated below
“Happiness can be found in the darkest of times, if one only remembers to turn on the light.”
“Generosity always pays off. Generosity in your effort, in your work, in your kindness, in the way you look after people and take care of people. In the long run, if you are generous with a heart, and with humanity, it always pays off.”
W

Offline RobbieRedman

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 10,965
Re: Excel help
« Reply #219 on: January 7, 2020, 02:44:13 pm »
Can’t you just validate the cells and define it as a list?

Then you type in your list comma separated below
Cheers mate, I think a manual approach is what is needed anyway!

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: Excel help
« Reply #220 on: May 5, 2020, 06:11:44 pm »
I have 2 spreadsheets in work for attendance at the minute.

One is to record the daily attendance of each person as follows

W = worked
W/A = half day work, half day annual leave
W/F = half day work, half day flexi leave
There is a different tab for each month.

The second is to record the total number of days someone has worked since 25th March, which is the date we started implementing a week in week off rota.

Is it possible to add a formula to the second spreadsheet that will collect the above data from the first spreadsheet and tally it automatically?
So if beside my name I have w w w w/f w for this week, it will total 4.5 beside my name on the second sheet?

I've to do this for 120 staff so doing it manually is a pain in the hole.
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #221 on: May 5, 2020, 06:26:07 pm »
Formulas can reference another workbook easily enough, but an extra tab in the first book is surely simpler?
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #222 on: May 5, 2020, 06:28:35 pm »
Presumably you tally each row on each sheet too, so you have a single column for each month with the employee total for that month?
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: Excel help
« Reply #223 on: May 5, 2020, 06:58:39 pm »
Formulas can reference another workbook easily enough, but an extra tab in the first book is surely simpler?

I suggested that, but management want it kept separate for a reason only known to themselves
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: Excel help
« Reply #224 on: May 5, 2020, 07:01:37 pm »
Presumably you tally each row on each sheet too, so you have a single column for each month with the employee total for that month?

Not on these new ones, as some dickhead created them and fucked with all the formulas from the old ones. Wouldn't take too long to fix them I suppose.
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Legacy Fan
  • ******
  • Posts: 36,042
  • .
  • Super Title: Southern Pansy
Re: Excel help
« Reply #225 on: May 5, 2020, 07:05:45 pm »
This example should give you enough on the sum of lookup values codes:

https://exceljet.net/formula/sum-lookup-values-using-sumif

Offline smithy

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 2,011
Re: Excel help
« Reply #226 on: May 5, 2020, 07:14:47 pm »
There is a way of doing this but its a bit of a faff for multiple sheets.

If you simply want to count the number of "W" or subtract "W/F" then use countif().

If you need to dynamically match the name you could use match() to build the range string, but you'd need to wrap that in indirect() to ensure Excel reads it as a range.

My simple example:
=COUNTIF(INDIRECT("[Book1]Sheet1!$B$"&MATCH(A2,[Book1]Sheet1!$A:$A,0)&":$F$"&MATCH(A2,[Book1]Sheet1!$A:$A,0)),"W")

Counts the number of "W" values in book1, sheet1 between columns B and F for whatever name is in column A of both.

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: Excel help
« Reply #227 on: May 6, 2020, 06:51:30 pm »
Sorted folks. Cheers.
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #228 on: July 26, 2020, 11:24:55 am »
Hi all, I'm brushing up my VBA skills in August.  If anyone has some small Excel projects they want doing, I'd rather do something useful than made up nonsense.  PM or post here if you've got something in mind.
Cheers
Paul
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: Excel help
« Reply #229 on: August 26, 2020, 10:23:30 am »
A file I am opening keeps opening in protected view. I've used it before so not sure why this is happening.

Have tried the steps found on google File > Options > Trust Center > Trust Center Settings > Protected View and unchecked all these boxes but it is still opening in protected view.

Got a colleague to email me their copy of the same file. They are able to open it fine, but when I try to open it, it's protected view. All other excel files seem ok.

Anyone know how to resolve this?
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Legacy Fan
  • ******
  • Posts: 36,042
  • .
  • Super Title: Southern Pansy
Re: Excel help
« Reply #230 on: August 26, 2020, 08:11:07 pm »
Nuclear option is to move all of the sheets in the file to a new Workbook. It may clear it.

Offline Red Raw

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 4,821
  • Klopptimistic
Re: Excel help
« Reply #231 on: August 28, 2020, 08:58:05 am »
Do the main menu options refer to general settings rather than individual file settings?  Try looking in the 'Review' tab for protect/unprotect worksheets and workbooks.

This is a sheet with protection - I click to unprotect and get asked to put in a password if one was set when the sheet was protected (optional).


Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: Excel help
« Reply #232 on: August 29, 2020, 02:46:55 pm »
Every other excel file is fine, just not this one. I'll check that review tab in the morning.
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline Dan The Man 28373

  • Legacy Fan
  • ******
  • Posts: 1,209
  • We Always win in Rome!!
Re: Excel help
« Reply #233 on: September 7, 2020, 12:33:47 pm »
I'm setting up an Excel spreadsheet with detailed stats on LFC since 2000, pretty simple stuff using COUNTIFs/SUMIFs etc, to get top scorers, managerial performance, average points/goals per game, H & A (and for neutral games like finals), clean sheets etc.

I have tabs per season & a master table with all the data.  I want to do a longest winning/losing/clean sheets streak, for all competitions & ideally for the Premier League.

I know I need to use MAX & FREQUENCY functions but my attempts don't seem to work & anything I look for online doesn't have a formula to filter down to Premier League only.

Any help/pointers would be appreciated.

Offline Red Raw

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 4,821
  • Klopptimistic
Re: Excel help
« Reply #234 on: September 7, 2020, 04:20:11 pm »
I'm setting up an Excel spreadsheet with detailed stats on LFC since 2000, pretty simple stuff using COUNTIFs/SUMIFs etc, to get top scorers, managerial performance, average points/goals per game, H & A (and for neutral games like finals), clean sheets etc.

I have tabs per season & a master table with all the data.  I want to do a longest winning/losing/clean sheets streak, for all competitions & ideally for the Premier League.

I know I need to use MAX & FREQUENCY functions but my attempts don't seem to work & anything I look for online doesn't have a formula to filter down to Premier League only.

Any help/pointers would be appreciated.
This is a bit crude but it seems to work and saves faffing about with array functions or macros.



You can always hide the columns (or use a hidden sheet) and use a cell reference for the result if this is a bit messy for your layout.

Offline PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: Excel help
« Reply #235 on: September 8, 2020, 09:53:09 am »
Dan the Man, can you post a screenshot of how the results are 'labelled' , so we can see where the PL results are, and accordingly calculate for just those. Red Raw Burp has done 90% of the work for it already I suspect.
Oh, and the latest , greatest XL has a new "function" , Let which essentially allows you to define your own function and re-use it.  So that rather long expression in row 14, can be wrapped up and reused with relative ease and readability.
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline Dan The Man 28373

  • Legacy Fan
  • ******
  • Posts: 1,209
  • We Always win in Rome!!
Re: Excel help
« Reply #236 on: September 8, 2020, 12:32:55 pm »
Here's a sample of my spreadsheet, there's 1 tab per season & then a TOTAL tab which is all linked to the individual tabs.

I ideally want to track maximum winning/losing/unbeaten/clean sheet streaks for all competitions, but hopefully for Premier League only.

The result column is a formula not entered.

Let me know your suggestions.  I've been looking at MAX & FREQUENCY formulas, but can't filter down to 1 specific competition.
« Last Edit: September 8, 2020, 12:44:01 pm by Dan The Man 28373 »

Offline aggerdid

  • Reads the subtitles on "Queer as Folk" from right to left
  • Legacy Fan
  • ******
  • Posts: 4,345
Re: Excel help
« Reply #237 on: September 23, 2020, 12:18:22 am »
I’d have to sit down and go through the formula but i’m thinking. A countif for W from your current row and up to the top of the column(absolute that). Then you want to do a find/search for the previous D of L in the W-D-L result column. I’ll have a crack at this tomorrow.

I’m always messing about with football stuff. Plotted klopp vs guardiolas rolling point averages in england yesterday. Would post it in here but I have no idea how to post images
We must embrace pain and burn it as fuel for our journey - Kenji Miyazawa

Offline Dan The Man 28373

  • Legacy Fan
  • ******
  • Posts: 1,209
  • We Always win in Rome!!
Re: Excel help
« Reply #238 on: September 29, 2020, 11:01:36 pm »
I’d have to sit down and go through the formula but i’m thinking. A countif for W from your current row and up to the top of the column(absolute that). Then you want to do a find/search for the previous D of L in the W-D-L result column. I’ll have a crack at this tomorrow.

I’m always messing about with football stuff. Plotted klopp vs guardiolas rolling point averages in england yesterday. Would post it in here but I have no idea how to post images

Thanks for that, I've managed to get a winning streak formula set up, but that is for all competitions, so can't calculate the streak for one particular competition using the formula from Red Raw Burp.  Also how would I do an unbeaten streak, I assume it is adding 2 strings together.

Offline red_lfc_costello

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 2,436
Re: Excel help
« Reply #239 on: October 21, 2020, 04:16:01 pm »
I've googled this but struggling so hoping someone might be able to help easily enough....

I opened an excel document with the view of updating it, i deleted some data and clicked save instead of save as! i've tried the 'previous versions' but there isn't any previous temporary versions as it was only a matter of seconds.

Any help how to get the last saved version back?!
You appear to hve mistaken 'the funny photo thread' for the 'pointless, pre-pubescent nonsensical not even porn but "look, look, it's a girl" thread'