Forum Widgets
Latest Discussions
Excel help
Hi all, I have list of LGA location names in excel file. An example are as follows: Currently after I extract the data, I use look up function to get the New LGA column to replace the existing data. This is a manual task. The original file I have download from MS SQL and the file format is csv. Is there any other way that can do this more efficiently and automatically? Thankskpan8Feb 14, 2025Copper Contributor23Views0likes2CommentsCreate a Power query or Dax to find Open Status more than 1 hour
Hi Team, I have the door table Here I want to find that whether door is being open continuously(having open status & not closed) for more than 1 hour for each DeviceId. Data Description: The Status column shows whether door is open or Closed. CreatedOn column shows time duration. DeviceId column shows Deviceid. My first requirement is: We need to add the minutes(createdon) between each row for same deviceid where the status = open. But where a status = closed (if closed comes in between open) we reset the number of minutes. So we sum by the previous value where status = Open - Something like If current status = open and previous status = open then sum the time and do the below: Sum(previous value + new sum) Where/if status = closed set time count = 0 Sum(previous value + new sum) is going to take into account only the consecutive open status >60 min for same divice id whenever status is closed, this Sum(previous value + new sum) will be reset to/equal to 0 So on the first open status, the count will be 0. Then the next open status it will be time between 2 open statuses, then on the third it will be the time between two open statuses add the previous time.but any close status then time is reset. Could you please help me with a power query logic/dax logic? Second Requirement: This is only a sample data. But we have billions of rows of data in our dataflow that comes from SQL views. So, thought not to use DAX for this calculation in future. we can precalculate the maximum open state duration by preprocessing data (SQL, Power Query(dataflow), Spark, anything else...). So can you help me prepare data upfront. we need to solve the problem with a with a proper data model. for example, create a table with the longest open events upfront (in whatever technique you are comfortable with) Could you please help me to achieve this? PFA file here B&M.pbix Thanks in advance!Excellove15Feb 14, 2025Iron Contributor69Views0likes7CommentsHelp creating a matrix table
Good day wonderful people, I am trying to create a matrix table that shows correlations between types of issues people may be worrying about. The end result is to enable me to bring it into Power BI and utilize the chord visualization diagram to show where certain issues are connected and the strength of those connections. I have an excel sheet with a range (se below). The first column is a unique client ID. As the data may be collected over a period of time, the same client IDs may appear more than once (if they come back to the service provider). The data may include up to a maximum four issues that they are worrying about, and these are in adjacent columns to the client ID. As an example, in the below table, homelessness has low level interconnectivity with 'Priority Debt'; 'Disabled' & 'Mental Health' as they appear as issues alongside with that string; "Mental health" has a strong connection to "Non-Priority Debt" (and of course vice versa) with it occurring each time the other is mentioned. This is what I am aiming to show. Due to the way in which this data is laid out (from a database export), I am struggling to bring it into a matrix table (if that is the best method) and then on into Power BI. I've spent quite a bit of time in the forums, but can't find a way to apply the learning in them to the table output, or the right way to transform the data (via power query or otherwise) to help identify the interconnectivity. If anyone can point me in the right direction to solve this, I would be most grateful! Thank you in advance :-)157Views0likes9CommentsImport Tab Delimited File from Clipboard
I'm looking to create a macro in excel to import a file from clipboard. I have tried to record a macro and that did not work. I do not want to enter a file name. I would like to copy from notepad and execute macro to import the data with pipe delimits. Sorry if this is a repeat, I ould not fine an answr anywhere. Thanks.SolvedJimKFeb 14, 2025Occasional Reader28Views0likes3CommentsREMOVE A CHECKBOX FROM EXCEL WORKSHEET
I was handed an Excel worksheet that has three checkboxes that I wish to remove but cannot seem to remove them. Excel Help says to right click the Box and then hit Delete. There is no Delete in the ensuing dropdown menu and Delete on the keyboard does not work. Any suggestions?tjbCanton2020Feb 14, 2025Copper Contributor317KViews4likes23CommentsHow to access temp selection?
Hi, is there a way to determine the temporary selection address from vba? The steps to reproduce are as follows: Select cells [A1:A3]. Hold the right mouse button and drag the selection and move to A10, now release the right mouse button and I have a temporary drag and drop area of [A1:A10]. However it is not the current selection (the result from Application.Selection is still "A1:A3") Thanks you!JaminFeb 14, 2025Copper Contributor8Views0likes0CommentsConcatenating two lists of numbers of different lengths
I am attempting to concatenate two lists of numbers of differing lengths. Unfortunately, I am extremely new to this, so any help provided would be greatly appreciated. In one list I have from 001 to 365 and in the other list I have 1 to 10. For each number from 001 to 365, I need them to have 10 iterations. I hope the image below explains it better. How can this be accomplished in VBA?GlaciereFeb 14, 2025Occasional Reader27Views0likes2CommentsPreventing the user from going to the next page in UserFrom
Hello , like the title says I have a problem in the UserForm.When the fields are blank in the first page(employee) then if the user clicks on the second page (training) , the message should pop-up and the user should stay on the first page.However, it looks like it stays on the first page but the controls(fields) of the second page (training) are visible , so only the page tab reverts back as employee but the fields are from the training page.Where is the mistake ? I would appreciate the answer ! Screenshots : Before clicking the training(2.page) when all fields are empty: after clicking training page: after closing the message : The code: Private Sub MultiPage1_Change() Dim isValid As Boolean isValid = True ' When trying to navigate to Page 2 (Training), validate fields first If MultiPage1.Value = 1 Then isValid = ValidateFields() If Not isValid Then MsgBox "Please complete all employee fields before proceeding to the Training page." MultiPage1.Value = 0 End If ' Load the training list now that we're accessing Page 2 Call LoadTrainingList("") End If End Sub Private Function ValidateFields() As Boolean ' Initially assume all fields are valid ValidateFields = True ' Check name field If Me.txtName.Value = "" Then Me.txtName.BackColor = RGB(255, 200, 200) ' Light red for missing data ValidateFields = False Else Me.txtName.BackColor = RGB(255, 255, 255) ' Reset back color if correct End If ' Check role field If Me.txtRole.Value = "" Then Me.txtRole.BackColor = RGB(255, 200, 200) ValidateFields = False Else Me.txtRole.BackColor = RGB(255, 255, 255) End If ' Check ID field If Me.txtID.Value = "" Then Me.txtID.BackColor = RGB(255, 200, 200) ValidateFields = False Else Me.txtID.BackColor = RGB(255, 255, 255) End If ' Check section field If Me.txtSection.Value = "" Then Me.txtSection.BackColor = RGB(255, 200, 200) ValidateFields = False Else Me.txtSection.BackColor = RGB(255, 255, 255) End If End Function Private Sub UserForm_Initialize() ' Set the initial page to Employee (Page1, assuming index 0) MultiPage1.Value = 0 ' Ensure all relevant controls are reset or visible/invisible as needed ResetFieldColors ' Start/resetting particular fields or visibility information here if required End SubElla123Feb 14, 2025Copper Contributor13Views0likes2Comments
Resources
Tags
- excel42,209 Topics
- Formulas and Functions24,466 Topics
- Macros and VBA6,356 Topics
- office 3655,939 Topics
- Excel on Mac2,620 Topics
- BI & Data Analysis2,335 Topics
- Excel for web1,879 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,609 Topics