The Excel Thread

Yes! My error, column A is numbers and column C is text, so of course they don't match. Change the C formula to =left(B2, 8) * 1. That should fix it!

That looks to have worked, the only issue i've got is that when I update the list of 8 digit codes in column A with a new list, the columns C & D aren't updating and staying as they were based on the previous input list. Does that sound right? It's not too much of a hassle to just insert the formulas and drag the list down each time but I didn't know if there was something i'm missing!

That looks like the Excel settings. Check that under the Formulas tab, calculation isn't set to manual. Or just try hitting F9, see if that updates. If either list gets longer though you will have to drag them down.

That's sorted it! Never seen that change to manual before but it's back to automatic and working.

Thanks for your help with this.

I can now go back to pretending this job takes hours when it reality it takes 30 seconds. The joys of working from home!

And you can add 'experience in oursourcing' to your CV.

Can anybody help with this question, please?

Let's say I have 1000 rows of data, each one an employee's name with various details about them and their role, and - in the final two columns - the start date and end date of any time off they've taken.

What I'd like to do is rather than just have the start date and end date, have each day in that range split out, and - crucially - on its own row as well.

Imagine the commas below represent separate columns.
Instead of:

Robert Smith, Sales, 01/01/2023, 05/01/2023

I'd have:

Robert Smith, Sales, 01/01/2023
Robert Smith, Sales, 02/01/2023
Robert Smith, Sales, 03/01/2023
Robert Smith, Sales, 04/01/2023
Robert Smith, Sales, 05/01/2023

Does anybody know the simplest way of doing this in Excel? Or if it's even possible without loads of manual work?

The reason I want it on separate rows is to combine it with another data set that's already in that format, and put into a Pivot (and that other data set is already listed with each day split out, unlike this one).

The same employee could be represented more than once in the data (if, for example, they've taken more than one chunk of time off through the year), if that makes a difference.

Thanks for any pointers anyone can provide.

The irony of Lotus asking for help with Excel.

Lotus wrote:Can anybody help with this question, please?

I think the text to columns function is what you want.

Lotus wrote:Instead of:

Robert Smith, Sales, 01/01/2023, 05/01/2023

I'd have:

Robert Smith, Sales, 01/01/2023
Robert Smith, Sales, 02/01/2023
Robert Smith, Sales, 03/01/2023
Robert Smith, Sales, 04/01/2023
Robert Smith, Sales, 05/01/2023

Does anybody know the simplest way of doing this in Excel? Or if it's even possible without loads of manual work?

The reason I want it on separate rows is to combine it with another data set that's already in that format, and put into a Pivot (and that other data set is already listed with each day split out, unlike this one).

The same employee could be represented more than once in the data (if, for example, they've taken more than one chunk of time off through the year), if that makes a difference.

Thanks for any pointers anyone can provide.

Assuming they aren't already in separate columns, text to columns will split out the dates, (so you have four cells separated by the commas, with 05/01/2023 in cell A4)

then insert X number of lines (in this case 4) between person X and person Y on leave event N (so you have five total lines, with column C1 having the value 01/01/2023),

then paste the end date from the cell in the third column on the fifth line (so in cell C5, before the next person),

then do a formula in C2 "=C1+" and drag that down or double click the square in the bottom right to fill series between C2 and C4 (filling the three previously empty cells with sequential dates between cells C1 and C5, which you already have),

then select call range A1:B1 and drag that down (or right click the little square when both are selected) to copy the Robert Smith | Sales values from these cells down to match the dates

Still a bit manual but pretty easy & excel does most of the heavy lifting

Trelliz wrote:

Lotus wrote:Can anybody help with this question, please?

I think the text to columns function is what you want.

Thanks Trelliz, I'll give that a look - appreciate the reply.

Wrathy wrote:

Lotus wrote:Instead of:

Robert Smith, Sales, 01/01/2023, 05/01/2023

I'd have:

Robert Smith, Sales, 01/01/2023
Robert Smith, Sales, 02/01/2023
Robert Smith, Sales, 03/01/2023
Robert Smith, Sales, 04/01/2023
Robert Smith, Sales, 05/01/2023

Does anybody know the simplest way of doing this in Excel? Or if it's even possible without loads of manual work?

The reason I want it on separate rows is to combine it with another data set that's already in that format, and put into a Pivot (and that other data set is already listed with each day split out, unlike this one).

The same employee could be represented more than once in the data (if, for example, they've taken more than one chunk of time off through the year), if that makes a difference.

Thanks for any pointers anyone can provide.

Assuming they aren't already in separate columns, text to columns will split out the dates, (so you have four cells separated by the commas, with 05/01/2023 in cell A4)

then insert X number of lines (in this case 4) between person X and person Y on leave event N (so you have five total lines, with column C1 having the value 01/01/2023),

then paste the end date from the cell in the third column on the fifth line (so in cell C5, before the next person),

then do a formula in C2 "=C1+" and drag that down or double click the square in the bottom right to fill series between C2 and C4 (filling the three previously empty cells with sequential dates between cells C1 and C5, which you already have),

then select call range A1:B1 and drag that down (or right click the little square when both are selected) to copy the Robert Smith | Sales values from these cells down to match the dates

Still a bit manual but pretty easy & excel does most of the heavy lifting

Thanks for all this detail Wrathy, I'll give this a go too and see how I get on. Thanks again.

I'm working on something but have become a bit suck so wonder if anyone here can help me.

I have a table of prices with horizontal and vertical labels. Looking online, I found a suggestion to use both Boolean Logic and XLOOKUP to return a specific price from the table. The Boolean Logic looks to be working because just entering that creates a table of zeroes with a single 1 where the corresponding price should be. When I add this to the XLOOKUP, the result is #VALUE! so I think something is wrong with the formula:

=XLOOKUP(1,(Sheet3!E6:E67='Sheet1'!I27)*(LEFT(Sheet3!F3:M3,5)="Sheet1!B3)*(TEXTAFTER(Sheet3!F3:M3," ")='Sheet1'!L3,Sheet3!F6:M67)

I thought that the issue might be the Return Array of the XLOOKUP but expanding that still gives me an error. Am I going about this the right way?

Hm, can you give a dummy example? E.g. a fake table with fake labels, what inputs you're putting in and what output you'd want (from the fake table)? My solution would usually be to use index-match at anything like this but I want to make sure it'd work.

Is there a way to sum every x row in Excel? I've done a Google and can't find what I need.

I've basically got a table with the totals for that table shown every 16 cells and need a formula that will sum them. Currently, someone is going through and doing a +x+y+z manual thing at the end of each month but I don't know how to automate it.

I did see something which said something like this: =SUM(IF(MOD(ROW($B$22:$B$22),16)=0,$B$24:$B but I can't get that to work successfully either.

Ideally, what I need to be able to do is in K2, do a formula which sums every Total Cost (so B22, B38, B54 etc) if G8/G24/G40 = January. I've got a screenshot below to show the base i'm working with, but I just cannot figure this out!



Anyone have any ideas? This is a brand new sheet to track this so the whole lay-out can be changed to make this work, but without having a long row with those totals appearing in a different column, I don't know how to do this.

Thanks in advance if anyone can help!

I think this will work:

=SUM((B:B)*(MOD(ROW(B:B),16)=6))

A number multiplied by FALSE gives 0. The 6 is in there because the first cell to sum is row 22.

I'd probably avoid splitting every month into a pre defined table and add a transaction date field in instead. Then you can use a simple SUMIFS to tally up the figures for each month, or any period required if that changes at a later date.

Thanks for the suggestions there. I couldn't get the formula to work, then got frustrated and moved on to something else until today when i've had another crack at it. I thought I had figured out a solution where I could just do a bunch of SUMIF formulas to do my totals and the rest of it being pretty straight forward but now this has stumped me too. I really thought this would be so much simpler but I cannot get it working!

I've tried to do =sumIf(M10,V1,C:C), i've tried =sumif(M10,"July",C:C) and this current version of having what would be a hidden reference column in D next to want to sum, and they all come back as 0. All the cells are formatted as 'general' and i've copied the formula from another sheet where i've got many sumif formulas working fine so i'm truly baffled!



Am I missing something stupid here?

Joer wrote:Thanks for the suggestions there. I couldn't get the formula to work, then got frustrated and moved on to something else until today when i've had another crack at it. I thought I had figured out a solution where I could just do a bunch of SUMIF formulas to do my totals and the rest of it being pretty straight forward but now this has stumped me too. I really thought this would be so much simpler but I cannot get it working!

I've tried to do =sumIf(M10,V1,C:C), i've tried =sumif(M10,"July",C:C) and this current version of having what would be a hidden reference column in D next to want to sum, and they all come back as 0. All the cells are formatted as 'general' and i've copied the formula from another sheet where i've got many sumif formulas working fine so i'm truly baffled!

Am I missing something stupid here?

=sumIf(M:M,V1,C:C) would work, if you copied July against each entry in column M, rather than just had it as a header. I.e. fields M12:M23 all state 'July'. I can see what you've intended and I've never used a sumif in that format, so unsure if that's because it wont work, or simply that I've never thought of that logic.

Something to check is that 'July' is the same format in both fields. A date formatted to show July is different to the text July, even if Excel is displaying them in the same manner.

Thank you!

That's done it. I've been able to put a box in M24 that is just a =L10 and then hidden that column. That puts the month I need in that column, and then sums the relevant figures I need.

I made the mistake of showing my boss I was slightly competent because I used conditional formatting on a Yes/No box to go Green/Red and now get all this sort of stuff thrown at me to try and solve as if I know what i'm doing.

You lot keep making me look good though which is handy.

Another issue with this bloody sheet.

Looking at the below, i'm trying to do something in R & S that will show the FWD Reference numbers in column A if that top right box is Yes/No. I can get it to do what I want with a simple =IF(K26="No",A26) formula, but not without having a huge list of false results showing in between each reference number.

I know there's something about putting a 0 in the formula or quotation marks or something like that, but I can't remember what it is or how it works! Can someone help with this one please?

Joer wrote:Another issue with this bloody sheet.

Looking at the below, i'm trying to do something in R & S that will show the FWD Reference numbers in column A if that top right box is Yes/No. I can get it to do what I want with a simple =IF(K26="No",A26) formula, but not without having a huge list of false results showing in between each reference number.

I know there's something about putting a 0 in the formula or quotation marks or something like that, but I can't remember what it is or how it works! Can someone help with this one please?

Do you just want it to be blank if K26 is not No? In which case you want the formula to be:

=IF(K26="No",A26,"")

The test is K26=No, if this test is met it returns the first entry A26, if this test is not met it returns the second entry which is blank (represented by quotation marks with nothing in between them). If you want something else returned if it is not No, then put that instead of the double quotations.

If there is the possibility that K26 will be neither Yes or No and you want a third result returned if K26 is blank, you'll need to embed two IF formulae, something like:

=IF(K26="No",A26,IF(K26="Yes","Yes result or reference here",""),"")

Which would give A26 if No, "Yes result or reference here" if Yes and blanks if it is neither Yes or No.