User Profile
naveen73
Copper Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Excel / VBA - filter data and save in new file
Hi All, I need some help with Excel / VBA. I have a dataset with a column with different categories and want to filter on each category and save the filtered data in a separate file. This way the other data will not be visible in that data. I have tried ChatGPT/Grok but it just not seem to work. After a number of attempts, please see the VBA. The macro only creates Cat_A.xlsx and skips the rest with a "No data found" message. This is strange as the rest of the categories do have data. Do you think you can help me? =========================================== Sub ExportEachCategoryToFile() Dim wsSource As Worksheet Dim wbDest As Workbook Dim lastRow As Long Dim dataRange As Range, visibleRange As Range Dim dict As Object, cell As Range Dim cat As Variant Dim fileName As String Dim outputPath As String Set wsSource = ThisWorkbook.Sheets("Data") Set dict = CreateObject("Scripting.Dictionary") ' Define the path to save files outputPath = ThisWorkbook.Path & Application.PathSeparator ' Determine the data range lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row Set dataRange = wsSource.Range("A1:E" & lastRow) ' Get unique categories from column C For Each cell In wsSource.Range("C2:C" & lastRow) If Not dict.exists(cell.Value) Then dict.Add cell.Value, 1 End If Next cell ' Loop through each category For Each cat In dict.keys ' Clear any previous filters If wsSource.FilterMode Then wsSource.ShowAllData If wsSource.AutoFilterMode Then wsSource.AutoFilterMode = False ' Apply filter on Category (column 3) dataRange.AutoFilter Field:=3, Criteria1:=cat ' Get visible rows On Error Resume Next Set visibleRange = dataRange.SpecialCells(xlCellTypeVisible) On Error GoTo 0 ' If we have data, export If Not visibleRange Is Nothing And visibleRange.Rows.Count > 1 Then Set wbDest = Workbooks.Add(xlWBATWorksheet) visibleRange.Copy Destination:=wbDest.Sheets(1).Range("A1") wbDest.Sheets(1).Columns.AutoFit fileName = outputPath & "Cat_" & cat & ".xlsx" Application.DisplayAlerts = False wbDest.SaveAs fileName, FileFormat:=xlOpenXMLWorkbook wbDest.Close SaveChanges:=False Application.DisplayAlerts = True Else MsgBox "No data for category '" & cat & "' – skipping.", vbInformation End If Set visibleRange = Nothing Set wbDest = Nothing Next cat ' Clear filters If wsSource.FilterMode Then wsSource.ShowAllData MsgBox "All category files saved successfully!", vbInformation End Sub =========================================== Thanks, Naveen194Views0likes3CommentsError when importing CSV file
Hi, I am new at MS SQL and have the community edition in my local machine. Trying to import a CSV file but getting the following error message: Total Defect Qty:=SUM([Defect Qty]) TITLE: Microsoft SQL Server Management Studio ------------------------------ Error inserting data into table. (Microsoft.SqlServer.Import.Wizard) ------------------------------ ADDITIONAL INFORMATION: Error inserting data into table. (Microsoft.SqlServer.Prose.Import) ------------------------------ The given value '[name of the column]' of type String from the data source cannot be converted to type nvarchar for Column 4 [Label]. (Microsoft.Data.SqlClient) ------------------------------ String or binary data would be truncated in table '[dbo].[name of the file]', column 'Label'. Truncated value: '[name of the column '. (Microsoft.Data.SqlClient) ------------------------------ BUTTONS: OK ------------------------------ Do you think you can help me please? Thanks, NaveenSolved443Views0likes5CommentsPower Pivot running total without date column - NOT in Power BI
Hi all, I am using Power Pivot in Excel and want to calculate the running total without Date column. I found the following tutorial, which is for DAX in Power BI and tried to convert this to Excel Power Pivot but I am not able to do so. Please can you help me converting this so that I can use it in Excel. https://www.antmanbi.com/post/how-to-create-running-total-on-non-numeric-fields. I am attaching a sample data table. Running Total Brands = VAR CurrentBrandSales = [Total Sales] VAR BrandsWithSales = ADDCOLUMNS ( ALLSELECTED ( Products[Brand] ), "@Sales", [Total Sales] ) VAR BrandsWithHigherSales = FILTER ( BrandsWithSales, [@Sales] >= CurrentBrandSales ) VAR Ranking = -- Used only for confirming the ranking COUNTROWS ( BrandsWithHigherSales ) VAR Result = SUMX ( BrandsWithHigherSales, [@Sales] ) RETURN Result Thanks, Naveen497Views0likes1CommentExcel maps zoom automatically
Hi, I am using the map functionality in Excel. Whenever the data updates, I am using the pivot table with slicers, and the size of the maps changes. For example, when the UK has to be highlighted, the map automatically zooms in. However, when Australia and US need to be highlighted, the maps zooms out. Is there a way for me fix this, please? I just do not want it to zoom in and out all the time. Thanks, Nav848Views0likes0CommentsThe name 'my-keyvault-NNN' is already in use
hi all, I am doing Part - 6 of AZ 900 and trying to create a "Key Vault" and getting the following error message: "The name 'my-keyvault-NNN' is already in use, or is still being reserved by a vault which was previously soft deleted. Please use a different name." I have look at the documentation: https://docs.microsoft.com/en-us/azure/key-vault/general/key-vault-recovery?tabs=azure-portal#list-recover-or-purge-a-soft-deleted-key-vault However, when I am the step where it says: "Manage deleted key vaults", the concerning key vault cannot be found under the subscription. Please can you help? Thanks, Naveen8KViews1like6CommentsVM not connecting DenyAllInBound
Hi all, I am trying to do the AZ 900 certification and created a virtual machine. I am trying to connect to this VM again but it is not letting me and I landed on this page: https://docs.microsoft.com/en-us/azure/virtual-machines/troubleshooting/troubleshoot-rdp-connection I am doing “Use IP flow verify” and I am getting the following error message: “Access denied” Security rule “DenyAllInBound” I understand from another forum that I need to create this inbound rule in the associated Network Security Group (NSG). Could you point me to some docs that help me solving this issue, please? thanks, Naveen1.1KViews0likes0CommentsError message in "Work with Compute" in DP-100 path: AzureMLAggregatedException'
https://docs.microsoft.com/answers/users/4274529/naveen-5899.html asked · Jan 12 at 11:32 PM Error message in "Work with Compute" in DP-100 path Hi, I am trying to do the https://microsoftlearning.github.io/mslearn-dp100/instructions/07-work-with-compute.html In the notebook "work-with-compute"-notebook there is a cell that is giving the following error message ImportError: cannot import name 'AzureMLAggregatedException'. Do more people get that problem? thanks, Naveen589Views1like0Comments
Recent Blog Articles
No content to show