Excel question

[TenEightyOne]

Sorry for not responding, I've been busy. I think I figured it out, at least manually. Equal split of $2000 on four months is $500 per month. But if a guy worked only 10 days in the last month, he would receive only $166,6 (500 / 30 = 16,66 and then 166,66 * 10 = 166,6). That means if the instalment for the last month is now reduced, others in the three remaining months have to be equally increased now to get $2000. I did 2000 - 166,6 = 1833,4 and then 1833,4 / 3 = 611,13.

There is probably a good formula that can summarize all this, but I can't figure out which one it would be.

So the ContractTotal is 2000 and the AmountPaid is 166.6, and you have InstalmentsRemaining yet to pay. The total for each of those instalments is =(ContractTotal-AmountPaid)/InstalmentsRemaining.

As I've been trying to point out there's still a difficulty in how you're deciding what to pay in the first month - if you continue with that system and the contractor only works 10 hours in the next two months then you're back on your head. It seems odd to have a fixed amount for the work but to then calculate an hourly rate. What does the contract with this employee say?

 

[Matej]

As I've been trying to point out there's still a difficulty in how you're deciding what to pay in the first month - if you continue with that system and the contractor only works 10 hours in the next two months then you're back on your head. It seems odd to have a fixed amount for the work but to then calculate an hourly rate. What does the contract with this employee say?

We know this:

- he needs to receive 2000 for three months (each is 30 days) and 10 days of his work, or 100 days in total.
- we have to equally split the amount in four instalments
- in months when he worked all 30 days each instalment has to be the same
- in the month when he worked only 10 days the instalment has to be 33% of the instalment he would have received if he had worked all 30 days (because 10 is 33% of 30)
- when all four instalments are summed up, we need to get 2000

 

[TenEightyOne]

We know this:

- he needs to receive 2000 for three months (each is 30 days) and 10 days of his work, or 100 days in total.
- we have to equally split the amount in four instalments
- in months when he worked all 30 days each instalment has to be the same
- in the month when he worked only 10 days the instalment has to be 33% of the instalment he would have received if he had worked all 30 days (because 10 is 33% of 30)
- when all four instalments are summed up, we need to get 2000

What if he only works 10 days in each of the remaining months but completes the work? As it is you're describing a monthly rate (2,000 for three months) but attributing an hourly calculation. If you're trying to pay ahead of time on an hourly rate without knowing how many hours he works... as @Rallywagon said some time ago you need to read the future.

 

[Matej]

What if he only works 10 days in each of the remaining months but completes the work? As it is you're describing a monthly rate (2,000 for three months) but attributing an hourly calculation. If you're trying to pay ahead of time on an hourly rate without knowing how many hours he works... as @Rallywagon said some time ago you need to read the future.

We know he has to finish the work in 100 days or less, and we know how much he is going to get for it. If he spends more time working, he is not going to get paid for that, and if he finishes earlier and takes all the remaining days for rest, he won't receive any reductions either.

That's why I did not pay specific attention to how many hours he would exactly work because the contract is formed based on estimation and job standards, not actual hours spent. This makes the contract slightly easier to form, though from mathematical point of view, it is tricky because I need one compact formula to sum this up:

- we have to equally split the amount in four instalments
- in months when he worked all 30 days each instalment has to be the same
- in the month when he worked only 10 days the instalment has to be 33% of the instalment he would have received if he had worked all 30 days (because 10 is 33% of 30)
- when all four instalments are summed up, we need to get 2000

 

[TenEightyOne]

I did not pay specific attention to how many hours he would exactly work because the contract is formed based on estimation and job standards, not actual hours spent

- in months when he worked all 30 days each instalment has to be the same
- in the month when he worked only 10 days the instalment has to be 33% of the instalment he would have received if he had worked all 30 days (because 10 is 33% of 30)

So you have a timed rate but you want to pay in equal instalments. It makes no sense.

- when all four instalments are summed up, we need to get 2000

It doesn't actually, if 2,000 is the max you're going to pay then just work out the payment for accumulated days and, as the contract says, cap it at 2,000.

 

[Matej]

Guys, I need little help again. I have four columns in Excel, each contains certain type of data. I also have a Word document with a custom-made table. How can I transfer the Excel content to Word so that each row fills one cell in the Word table? As you can see from the attachments below, the idea is to create labels you can print and paste on envelopes, content from certain columns have predefined positions where they should be. I'm supposed to use Word's mail recipients function, and a VLOOKUP formula in Excel, but can't figure out how to do it. This would save me a lot of time, otherwise I'll have to manually type everything in.

 

[TenEightyOne]

Is this what you're looking for? https://support.microsoft.com/en-us...eadsheet-858c7d7f-5cc0-4ba1-9a7b-0a948fa3d7d3

 

[Matej]

Yeah, but the support site doesn't have the details I need, it's too vague. I'm getting some results but can't really understand how I got what I got.

 

[DG_Silva]

EDIT: forum knackered, will try again later

 

[TenEightyOne]

the support site doesn't have the details I need, it's too vague.

That's impossible, it's a Microsoft support site!

I don't have much more than that, I've done it (long ago) and I believe that I wrote a macro to solve it. How are you with macros/VB?

 

[DG_Silva]

Seeing as copy and paste just isn't working...

EDIT: I forgot to add, you need to click Word Wrap on each cell to make this work like this as well.

 

[Matej]

I don't have much more than that, I've done it (long ago) and I believe that I wrote a macro to solve it. How are you with macros/VB?

What's that?

Seeing as copy and paste just isn't working...

View attachment 1013011

EDIT: I forgot to add, you need to click Word Wrap on each cell to make this work like this as well.

Thanks for the tip, I'll try it, since I'll need to create a new set of labels very soon. I solved my current situation somehow, but I can't even explain how, I guess I shouldn't bother trying to decipher it since I got what I needed.

 

[DG_Silva]

Wonderful Excel experts, I need your help please.

I need to develop a Pivot Table from two tables, but I just can't get the data to link correctly. If you see the attached file, it will make sense.

What I need to do is call the %Occupancy from Planned hours sheet into the Pivot table for each date, so that it shows the individual day's occupancy rate. For some reason, it's just not working for me and it's coming out with garbage numbers, basically the total of the percentage numbers.

Can anyone help with this please?

 

[DG_Silva]

Hi all, me again.

This time I'm struggling to make a macro work. I've got two slicers on which control four pivot tables - one slices the divisions and the other slices the regions, so the structure in the pivot table goes Division > Region > Store. What I'd like to do is when the region slicer is clicked then it opens the second level of pivot up so that it shows the stores in that region; when you click on the division slicer, I'd like it to close the region pivot, so the division only shows down to region level.

So far, I've got this coding, but

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

If ActiveWorkbook.SlicerCaches("Slicer_Region").Selected = True Then
    Application.ScreenUpdating = False
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Centre").ShowDetail = True
        ActiveSheet.PivotTables("PivotTable2").PivotFields("Centre").ShowDetail = True
        ActiveSheet.PivotTables("PivotTable3").PivotFields("Centre").ShowDetail = True
        ActiveSheet.PivotTables("PivotTable5").PivotFields("Centre").ShowDetail = True
    Application.ScreenUpdating = True
ElseIf ActiveWorkbook.SlicerCaches("Slicer_Division").Selected = True Then
    Application.ScreenUpdating = False
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Centre").ShowDetail = False
        ActiveSheet.PivotTables("PivotTable2").PivotFields("Centre").ShowDetail = False
        ActiveSheet.PivotTables("PivotTable3").PivotFields("Centre").ShowDetail = False
        ActiveSheet.PivotTables("PivotTable5").PivotFields("Centre").ShowDetail = False
    Application.ScreenUpdating = True
End If

End Sub

However, it seems the pivot slicer doesn't accept Select, Selected or Click as an event to capture, so I'm at a loss as to how to start the macro running. Does anyone have any idea on this one please?

 

[Matej]

Stumbled across another problem. I have 500 items in column A, over 8000 in column B and over 8000 in column C (but with a different content than from the column B).

I would like a formula that deletes all the content from the column B that is not a match to content from A and then on the remaining content in column B add adjacent content from cells in column C.

 

[DG_Silva]

Stumbled across another problem. I have 500 items in column A, over 8000 in column B and over 8000 in column C (but with a different content than from the column B).

I would like a formula that deletes all the content from the column B that is not a match to content from A and then on the remaining content in column B add adjacent content from cells in column C.

If you wanted it in a formula, you could put a couple of helper columns in C and E, with (assuming you have headers in row 1) =IF(COUNTIF(A:A,B2)=1,B2,"") in C2, then repeat for E2, changing the formula to =IF(COUNTIF(A:A,D2)=1,D2,""). Copy and paste columns B-E as values, sort the rows as required, then delete columns B and D.

However, in a macro:

Sub Delandsort ()
Dim lra As Long, lrb As Long, lrc As Long, lrd As Long, i As Long
With Sheets("Sheet1")
lra = .Cells(Rows.Count, "B").End(xlup).Row
For i = lra To 1 Step -1
If .Cells(i,"B").Value <> Sheets("Sheet1").Range("A2:A501").Value Then .Cells(i,"B").ClearContents
Next i
End With
lrb = .Cells(Rows.Count, "B").End(xlup).Row
Range("B2:B" & lr).Sort order1:=xlAscending, Header:=xlNo
With Sheets("Sheet1")
lrc = .Cells(Rows.Count, "B").End(xlup).Row
For i = lrc To 1 Step -1
If .Cells(i,"C").Value <> Sheets("Sheet1").Range("A2:A501").Value Then .Cells(i,"C").ClearContents
Next i
End With
lrd = .Cells(Rows.Count, "C").End(xlup).Row
Range("C2:C" & lr).Sort order1:=xlAscending, Header:=xlNo
End Sub

It's been a while since I dived into macros, but that should do the trick. The four separate lrX values are there so they don't interfere with each other, but in theory just using lra for all four should work.

 

[R55NA]

Stumbled across another problem. I have 500 items in column A, over 8000 in column B and over 8000 in column C (but with a different content than from the column B).

I would like a formula that deletes all the content from the column B that is not a match to content from A and then on the remaining content in column B add adjacent content from cells in column C.

Have you tried ChatGPT.

I was using that to create scripts at work for my Excel needs (then the company banned all uses of AI so I am screwed)

 

[eran0004]

Stumbled across another problem. I have 500 items in column A, over 8000 in column B and over 8000 in column C (but with a different content than from the column B).

I would like a formula that deletes all the content from the column B that is not a match to content from A and then on the remaining content in column B add adjacent content from cells in column C.

Easy way: Format the whole thing as a table, then insert a new column D with a countif function searching for B in column A. Then filter the table by column D > 0. In column E you can do B+C.