Connect with experts and redefine what’s possible at work – join us at the Microsoft 365 Community Conference May 6-8. Learn more >

User Profile

MichielS340

Copper Contributor

Joined 8 months ago

16 Posts1 Like

View All Badges

User Widgets

Recent Discussions

Re: Compare two columns and values with array function
Elegant as well!
Dec 01, 2024 Place Excel
51Views
1like
0Comments
Re: Compare two columns and values with array function
Thank you very much, This does the trick. The split in P_1 and P_2 is something I should come up with earlier. 🙏
Dec 01, 2024 Place Excel
47Views
0likes
0Comments
Compare two columns and values with array function
Hi, Somebody has a suggestion how to compare two tables that have keys and values to get a list of the differences in key but also when the value is not the same. In the attachment I would like to have a list of keys and values which are different between table 1 and table 2. Many thanks for a push in the right direction, regards, Michiel
Solved
Dec 01, 2024 Place Excel
BI & Data Analysis
excel
Formulas and Functions
office 365
124Views
0likes
6Comments
Re: Excel Groupby on table with Lambda
I was not aware of the values argument having this flexibility, but this would definitely be the most simple solution. Thanks for your quick advice 🙏
Nov 17, 2024 Place Excel
54Views
0likes
0Comments
Excel Groupby on table with Lambda
Hi, In the attached document I would like to groupby on the column "type" and then sum the values of the column "waarde" but before doing so multiply it by a certain chance (column "perc"). However I am not sure how to multiply it by the column "perc" before summing the values. Anybody any advice on the adjustment of the groupby formula that I created in the sheet? Many thanks in advance, Michiel
Nov 17, 2024 Place Excel
excel
Formulas and Functions
office 365
71Views
0likes
2Comments
Re: Loop through table column with spill formula
PeterBartholomew1 🙇‍:male_sign:unbelievable
Sep 11, 2024 Place Excel
863Views
0likes
0Comments
Re: Loop through table column with spill formula
Thanks for your input!
Sep 09, 2024 Place Excel
991Views
0likes
0Comments
Re: Loop through table column with spill formula
Many thanks, I need some time to digest, but it works quite elegant!
Sep 09, 2024 Place Excel
995Views
0likes
1Comment
Loop through table column with spill formula
Hi, I get a nested array calculation error if I try to loop through the rows and for each row prepare a list of working days. See attached file. Anyone a suggestion how I can work my way around this? many thanks,
Solved
Sep 08, 2024 Place Excel
excel
Formulas and Functions
1.2KViews
0likes
9Comments
Re: Excel save as csv on Sharepoint with VBA
thanks, I was hoping an easier approach (in the sense of no powershell) was possible...
Sep 06, 2024 Place Excel
471Views
0likes
0Comments
Excel save as csv on Sharepoint with VBA
Hi, Hopefully somenone knows how to do the following. I have the following script to create a csv file and store it locally. Now I want to save the csv file on Sharepoint but I am not able to create the right path for it. How would I need to construct the myCSVfilename_helprequired in order to have it saved directly in a Sharepoint directory? Many thanks, Michiel Option Explicit Sub ExportData() Dim myCSVFileName As String Dim fNum As Integer Dim csvVal As String Dim i As Long Dim j As Integer Dim fileNumber Dim vaData As Variant vaData = Blad1.Range("B2:I27").Value myCSVFileName = ThisWorkbook.Path & "/test.csv" myCSVFileName_HelpRequired = "https://company.sharepoint.com/sites/DTCM/Gedeelde%20documenten/General/Forecast%20actueel/DummyName/DummyName" & "/test.csv" csvVal = "" fNum = FreeFile Open myCSVFileName For Output As #fNum For i = 1 To UBound(vaData) For j = 1 To UBound(vaData, 2) Select Case j Case 1, 3, 4 csvVal = csvVal & Chr(34) & vaData(i, j) & Chr(34) & "," Case 2, 7 csvVal = csvVal & Format(vaData(i, j), "0") & "," Case 5, 8 csvVal = csvVal & Format(vaData(i, j), "yyyy-mm-dd") & "," Case 6 csvVal = csvVal & Replace(Format(vaData(i, j), "0.00"), ",", ".") & "," End Select Next j Print #fNum, Left(csvVal, Len(csvVal) - 1) csvVal = "" Next i Close #fNum End Sub
Solved
Sep 06, 2024 Place Excel
excel
Macros and VBA
560Views
0likes
2Comments
Re: Excel spill formula days of the week to actual dates
mathetes I gave it another try and came up with the attached. However I think it might be more efficient to first prepare the data with power query and put the days on the rows.
Sep 04, 2024 Place Excel
725Views
0likes
1Comment
Re: Excel spill formula days of the week to actual dates
mathetes true i was not clear in my previous post. Basically what I want to achieve is for the upper table to loop through all values (hours noted in F5:L11), put those in rows and add the following information on those rows: - the concurring date (which can be derived by the formula I added) and - the employee number - the holiday code i thought a spilling formula would be a nice solution in such a situation but I was not able to achieve it with map and lambda and use the cell references of each cell. I adjusted the attachment to be more clear with the example output. Many thanks,
Sep 03, 2024 Place Excel
805Views
0likes
3Comments
Excel spill formula days of the week to actual dates
Hi, hopefully someone can give me a little push in the right direction. I have a table regarding holiday hours with years and weeks on the rows and in the columns the different days of the weeks (see attached). How would I be able to prepare a list of dates (including employee number and holiday code on the row) from such a table in order to compare that to another table that already contains dates? The formula to derive the actual date from the column is on the right but not sure how to prepare a flat table from it. Many thanks in advance
Solved
Sep 03, 2024 Place Excel
excel
950Views
0likes
5Comments
Excel combine lambda and countif in formula
Hi, I have the following formula to get a range of values (col) of which i want to check how many times the values appear in the range and then filter for more than one time (duplicates). Somehow I am not able to get the formula working pr =LET(tbl;UNIQUE(VSTACK(HSTACK(tbl_Prognose[PRJ_NR];tbl_Prognose[DESC]);HSTACK(tbl_Orderportefeuille[Nummer];tbl_Orderportefeuille[Omschrijving]))); col;CHOOSECOLS(tbl;1); BYROW(col;LAMBDA(r;COUNTIF(col;r)))) operly. Someone any advice on this 😅 Many thanks in advance
Jul 25, 2024 Place Excel
excel
office 365
1.1KViews
0likes
4Comments
Re: Excel iterate over table and list output with spilling formula
PeterBartholomew1 very elegant solution 🙇‍:male_sign:
Jul 23, 2024 Place Excel
735Views
0likes
0Comments

Groups

Recent Blog Articles

No content to show