Author Topic: Excel help (Read 60321 times)

Riquende · « **Reply #280 on:** September 21, 2021, 09:23:58 am »

Quote from: PaulF on September 21, 2021, 04:45:56 am

IFS might make that a wee bit more readable.

What I've never liked about IFS is that it requires that one of your tests will be true, and if not throws out an ugly N/A error (which is as bad as having a column of 0 results). But having just looked into it again and you can apparently fix it with a final "was any of this true" test, and if not then give your false output.

So Lee0-3Liv, for the formula in question, you could do:

=IFS(D7="","",D7<C2,D7,TRUE,"")

And this would return a blank cell if D7 were either empty or greater than C2. If you wanted a different output for D7 being greater than C2 then you can enter it between the final set of quotation marks.

PaulF · « **Reply #281 on:** September 21, 2021, 12:22:03 pm »

Exactly

.
not sure that's any easier to read than a nestedif. But one more layer and it will be!

Paul

(that said new versions of Excel have lambda functions or whatever they're called which opens up a whole new way of doing things)

Riquende · « **Reply #282 on:** September 21, 2021, 03:45:14 pm »

Quote from: PaulF on September 21, 2021, 12:22:03 pm

Exactly .
not sure that's any easier to read than a nestedif. But one more layer and it will be!

Yeah, with just one it looks like:

=IF(D7="","",IF(D7<C2,D7,""))

But certainly if you're trying to nest several or more IF statements then an IFS is going to be the way to go to keep track of it all.

CornerFlag · « **Reply #283 on:** September 22, 2021, 04:47:35 pm »

Any online (free) recommendations from you guys about learning more about Excel? Like, what are the best resources?

PaulF · « **Reply #284 on:** September 22, 2021, 08:03:54 pm »

Is say it's too big. There are so many different things you can do. If you've got a specific problem, Google it. The same names crop up in the results.
If you've got a business problem to solve and don't know where to start. Ask in here 😃

That said leila gharani keeps popping up on you tube for me and she's very watchable. Not too much fluff to get YouTube advert revenue.

stewil007 · « **Reply #285 on:** December 9, 2021, 04:08:10 pm »

Looking for some help with some automation, i'll explain in simplist terms possible.

I have a start and finish date, which when when entered i want that to fill in that date range to the right of the entered data.

I think this is possible with conditional formatting, but havent figured it out yet.

This is further complicated by having three separate tasks against each system as options which require different colours.

In my mind the columns go:-

system task start date finish date 1/12 2/12 3/12 4/12 5/12 6/12 7/12
001 step 1 1/12 2/12 red red
001 step 2 3/12 4/12 blue blue
001 step 3 5/12 7/12 gold gold gold

So:-

if i enter a start and finish date against any step 1 task, the cells would fill red against the date range
if i enter a start and finish date against any step 2 task, the cells would fill blue against the date range
if i enter a start and finish date against any step 3 task, the cells would fill gold/yellow against the date range

The spreadhseet i have is huge andi have something like 650 systems, each with 3 tasks, so i'm using vlookups to do the start/finish dates, but the colouring in the cells is a pain in the arse, especially if i have to update this monthly with new dates.

Hopefully that made sense, but could really do with some guidance!!

PaulF · « **Reply #286 on:** December 9, 2021, 06:15:33 pm »

Stewil,
is this enough to get you going?

https://www.dropbox.com/t/zvBhOrBfNoRHMf2H

Paul
-- Edit apologies I've used yellow for gold. No idea how to find a gold colour! --

stewil007 · « **Reply #287 on:** December 10, 2021, 08:50:56 am »

Quote from: PaulF on December 9, 2021, 06:15:33 pm

Stewil,
is this enough to get you going?

https://www.dropbox.com/t/zvBhOrBfNoRHMf2H

Paul
-- Edit apologies I've used yellow for gold. No idea how to find a gold colour! --

thats exactly what i needed, cheers - i only used gold as it was easier to format the post above, it would have been yellow regardless

PaulF · « **Reply #288 on:** December 10, 2021, 09:37:19 am »

No problem. Just a heads up, that link expires after about a week

Paul

Elzar · « **Reply #289 on:** May 13, 2022, 08:46:42 am »

I have a spreadsheet of data that was taken as an extract. It's basically got a load of different organisations on and their status at certain periods time.

eg.

Liverpool - Premier league - Date
Liverpool - premier league - Date
Liverpool - Premier league - Date
Arsenal - Premier league - Date
Arsenal - Premier league - Date
Arsenal - Premier league - Date
West Ham - Premier league - Date
West Ham Championship - Date
West Ham - Premier league - Date
Middlesborough Championship - Date
Middlesborough - Premier league - Date
Middlesborough Championship - Date
Portsmouth - League 1 - Date
Portsmouth - Championship - Date
Portsmouth - Premier league - Date

Is there any way/forumla to extract the ones that have stayed as a certain status (say Pick out liverpool that have always been Premier league) and also pick out ones that changed from status to status (in the example say )championship but then went to Premiership

PaulF · « **Reply #290 on:** May 13, 2022, 09:03:45 am »

Easy with a macro. Probably with a helper col and formulas or powerquery.
I'll pm you my email and take a look at the weekend if you like.

Red Raw · « **Reply #291 on:** May 13, 2022, 11:05:53 am »

Quote from: Elzar on May 13, 2022, 08:46:42 am

I have a spreadsheet of data that was taken as an extract. It's basically got a load of different organisations on and their status at certain periods time.
...

Depending on how many organisations you have, and what you want to do with the information afterwards, a pivot table would provide a quick and dirty method - if you are able/prepared to scan the output manually.

If this would help I can elaborate.

Craig S · « **Reply #292 on:** May 13, 2022, 12:24:31 pm »

Quote from: Elzar on May 13, 2022, 08:46:42 am

I have a spreadsheet of data that was taken as an extract. It's basically got a load of different organisations on and their status at certain periods time.
....

Use a pivot table or a column with COUNTIFS to show how many dates that company has not had this status, you can then filter on that column.
Limitation of this is when an item reverts back to a status that it has previously had. eg Lge 1, Lg2, Lge 1 again. There will be the same limitation using the pivot method.
Below the calc in D2 =COUNTIF(A:A,A2)-COUNTIFS(A:A,A2,B:B,B2)

Or a bit more complicated, the next column E indicates when there was a change in status. See Portsmouth has a 1 where they were relegated.
This assumes the data is in chronological order. It is checking if the next sequential row for this team has the same or different status.
Team and status do not need to be sequential, I added Liverpool within Portsmouth data to demo.

calc in E2 =IF( IFERROR( OFFSET($B$1, MATCH(A2,A3:A9999, 0)+ROW(A2)-1, 0 ), B2 ) =B2, 0,1 )
copy that down the column

Note I have only done the match to row 9,999. You can also calc that bit up using INDIRECT function, but it becomes less readable.

Elzar · « **Reply #293 on:** May 13, 2022, 12:46:26 pm »

Thanks all, you're a very helpful bunch

Mr Mingebag Squid · « **Reply #294 on:** May 13, 2022, 03:34:20 pm »

Not Excel - but Business Central....

Does anyone have any experience with creating reports?

Mr Mingebag Squid · « **Reply #295 on:** September 7, 2022, 11:56:30 am »

Wonder if anyone can help with this...

I have a list of employee data, it should be in the format "First name Surname DDMMYYYY(DoB) - Division they work for". Some of the data doesn't include DoB - is there a quick formula I can apply to filter all the lines that dont include DoB?

PaulF · « **Reply #296 on:** September 7, 2022, 12:00:29 pm »

If it's a one off hack. Add a column and get the last four characters. Then another with the first two from that and filter. If it's not 19 or 20 you don't have a dob.

bradders1011 · « **Reply #297 on:** September 7, 2022, 05:07:14 pm »

If the final character of the data is always a number if it's DOB or a letter if there's no DOB, you can have a second column that checks that character using =ISNUMBER() and =RIGHT() and place a string or something there to signify 'No DOB' then filter on that column.

A B
1 | Brian Potter 25121963 | =if(isnumber(right(A1,1)),"DOB","No DOB") would return "DOB"
2 | Jerry Sinclair | =if(isnumber(right(A2,1)),"DOB","No DOB") would return "No DOB"

Mr Mingebag Squid · « **Reply #298 on:** September 12, 2022, 02:09:05 pm »

Awesome - cheers guys!

Riquende · « **Reply #299 on:** October 28, 2022, 03:24:54 pm »

Anyone know much about VBA? I've only ever made simple macros and looking to incorporate a couple of drop-down lists which I want to populate with some options, then use the selected option for a few different things across the sheet. Trouble is, the guides online all assume I'm creating a userform and base their sample code around that.

Essentially, I want to create two comboboxes:

The first one will show numbers 0-7, which is an indicator of lapsed days. I want another button which will insert a date (which will then be modified by the value in the combo box. So as an example, if I want yesterday's date I would drop the list and select '1', then click the 'Insert date' button. This would then insert yesterday's date as a string.

(And I would MUCH prefer to use a datepicker, but the company has installed 64 bit Excel and the MS tool isn't compatible).

The second one will have a list running from s01 to s32 (with some omissions and the ability to expand it later). Really simple, as soon as the user clicks an option I want the selected value to appear in the currently selected cell.

I'm already using data validation in those cells to display a list, but there is some dispute in the team about whether the list should be site names or site numbers, so I'm hoping that being able to use a separate list to put in the numbers will stop arguments.

PaulF · « **Reply #300 on:** October 28, 2022, 06:08:58 pm »

Hi. I can help you out mate.
Will look when back at pc.

Here you go

https://www.dropbox.com/t/o3bRVVV7LgoK8vb4

Maybe not exactly what you are looking for, but enough to push the requirements a bit closer to what you need?

redtillded · « **Reply #301 on:** October 29, 2022, 10:09:58 am »

Any students on here who know excel and looking to help on a small project, we will pay for the service ?

Riquende · « **Reply #302 on:** October 29, 2022, 06:00:38 pm »

Quote from: PaulF on October 28, 2022, 06:08:58 pm

Hi. I can help you out mate.
Will look when back at pc.

Here you go

https://www.dropbox.com/t/o3bRVVV7LgoK8vb4

Maybe not exactly what you are looking for, but enough to push the requirements a bit closer to what you need?

Thanks, I'll see if I can make something of it. I can't use userforms though, the idea is that there is a sequence of 6 buttons (and dropdowns) along the top of the sheet and that they can be clicked in order to populate site visit logs rapidly.

PaulF · « **Reply #303 on:** October 29, 2022, 06:46:23 pm »

Ah ok.
Want to pm me and we can do a quick phone call?

Mr Mingebag Squid · « **Reply #304 on:** November 11, 2022, 10:59:09 am »

Hi All, got probably a simple issue thats giving me a headache...

I have a form where we score certain items and I want to average the total of the boxes. Easy. However, the one box is a Yes/No or N/A rather than 1/2/3. If that box is given a Yes - I want it to essentially score 1 and if No it scores 0

Criteria 1 - Yes/No (Scores 1 for Yes, 0 for No)
Criteria 2 - 1/2/3
Criteria 3 - 1/2/3
Criteria 4 - 1/2/3

Total - Average of Criteria 1 - 4

Can anyone help me pull the formula together? I'm thinking i need in there somewhere/somehow =IF(C3="Yes",1,0) and =AVERAGE(C3:C6) but just hit a brick wall

.adam · « **Reply #305 on:** November 11, 2022, 11:47:10 am »

I'd add an extra (hidden) column after your first criteria one which has the IF statement you previously mentioned. Then I would create an average of the new column and the other three.

I tested it and it works. You could then hide column B.

PaulF · « **Reply #306 on:** November 11, 2022, 12:38:46 pm »

Quote from: .adam on November 11, 2022, 11:47:10 am

I'd add an extra (hidden) column after your first criteria one which has the IF statement you previously mentioned. Then I would create an average of the new column and the other three.

I tested it and it works. You could then hide column B.

That's beautifully laid out!

Mr Mingebag Squid · « **Reply #307 on:** November 11, 2022, 11:33:26 pm »

Quote from: .adam on November 11, 2022, 11:47:10 am

I'd add an extra (hidden) column after your first criteria one which has the IF statement you previously mentioned. Then I would create an average of the new column and the other three.

I tested it and it works. You could then hide column B.

Aaah....good thinking! I'll give it a try!

Mumm-Ra · « **Reply #308 on:** November 17, 2022, 12:32:15 am »

Anyone in here use python with excel? I'm trying to write a dataframe to an existing excel sheet, but keep the formatting of the excel sheet.

My excel template has the columns formatted already (aligned a certain way, % format in one, numbers formatted a certain way, font sizes, etc.)

I just want to paste the values from the dataframe right in there and preserve all the formats. Doing my head in!

bradders1011 · « **Reply #309 on:** November 17, 2022, 02:53:07 am »

YYYY-MM-DD is the best date format and should be mandated worldwide.

Rhi · « **Reply #310 on:** November 18, 2022, 01:12:49 pm »

Quote from: Mumm-Ra on November 17, 2022, 12:32:15 am

Anyone in here use python with excel? I'm trying to write a dataframe to an existing excel sheet, but keep the formatting of the excel sheet.

My excel template has the columns formatted already (aligned a certain way, % format in one, numbers formatted a certain way, font sizes, etc.)

I just want to paste the values from the dataframe right in there and preserve all the formats. Doing my head in!

Which libraries are you using?

Will this work? https://stefanogiostra-it.medium.com/saving-the-pandas-dataframe-data-in-an-excel-file-keeping-the-sheet-format-8c7069ffcda4

Elzar · « **Reply #311 on:** November 18, 2022, 01:19:29 pm »

Quote from: bradders1011 on November 17, 2022, 02:53:07 am

YYYY-MM-DD is the best date format and should be mandated worldwide.

Works best for filing documents and sorting data for sure.

Mumm-Ra · « **Reply #312 on:** November 18, 2022, 06:55:58 pm »

Quote from: Rhi on November 18, 2022, 01:12:49 pm

Which libraries are you using?

Will this work? https://stefanogiostra-it.medium.com/saving-the-pandas-dataframe-data-in-an-excel-file-keeping-the-sheet-format-8c7069ffcda4

Sorry should have specified, but as you guessed, pandas

Thanks for this - it's almost working, it's keeping the formatting for the top row, and the first and last columns, but not the ones in between oddly enough. I'm new to this so am not quite understanding what is happening in that for loop but it's not far away from what I need, so I'll keep plugging away.

Thanks again!

Buck Pete · « **Reply #313 on:** February 27, 2023, 02:59:05 pm »

Help please!!

I have a column of around 90 mobile numbers. The data is within cells C2 through to C90

I want to remove the leading Zero and replace with +44. Eg 07764653231 to become +447764653231

In D2 I can run =REPLACE(C2,1,1,"+44") I can then go on to populate D3 to D90 fine with the new +44 number

What my question is...How the hell do I replace the sodding original number still sat in C2 to C90

Hope this makes sense.

cheers

Buck Pete · « **Reply #314 on:** February 27, 2023, 03:30:04 pm »

Ah think i have sorted it guys

Copied the contents of my new column D with the +44 Numbers and pasted back into Colum C but used "Paste Values", so the formulae was ignored.

But If there's an easier way I'm keen to hear it

Riquende · « **Reply #315 on:** February 27, 2023, 03:45:00 pm »

Quote from: Buck Pete on February 27, 2023, 03:30:04 pm

Ah think i have sorted it guys

Copied the contents of my new column D with the +44 Numbers and pasted back into Colum C but used "Paste Values", so the formulae was ignored.

But If there's an easier way I'm keen to hear it

Probably how I'd have done it. Formulas can't reference themselves so there's no way to convert a column directly via a formula, you have to put the results somewhere else and manually move them back.

You might have had better luck just with Office's "Find and replace" tool if there was a specific unique string to identify, but if you've only got the leading 07 to go on, and that could crop up within a number too, then probably not.

There's probably some VBA that could put the steps together into a single macro, but if it's for a one-off thing then you're quicker just doing the 'paste values' method manually than trying to code it.

Mumm-Ra · « **Reply #316 on:** February 27, 2023, 03:46:01 pm »

Nah that's basically it, no way to change the numbers in place

Buck Pete · « **Reply #317 on:** February 27, 2023, 05:40:37 pm »

Ok lads. Cheers for the confirmation.

PaulF · « **Reply #318 on:** February 27, 2023, 08:14:05 pm »

ChatGPT agrees.
Though it does suggest using find and replace too, which has the obvious problem that any 0 in the number also becomes +44

Lee1-6Liv · « **Reply #319 on:** December 12, 2023, 10:08:06 pm »

VBA question. Vba was working fine yesterday, the menu was in english, today the menu is in german. I tried setting english as the preferred language but nothing changes. I can change it to other languages like french or dutch but not english. I presume an overnight update has done this.