data base report selection criteria

[harry_hobson]

In a file folder I have a spread sheet with 9 columns and many rows. In the file I have several sheets to collect by sort criteria the file information pertenant to the selection and reading the changes in real time... I want to select the records by one column, sort those in order by another column by key record#, read the other 7 columns making the file in a list in the sheet or sub folder.  guess excell does not have a look up wizard any more or a Macro formula button so I cant figure this out. 

Harry

[#2]

@harry_hobson 

You can achieve the desired functionality using Excel's built-in features along with some VBA (Visual Basic for Applications) code. Below is a step-by-step guide on how to accomplish this:

1. Filter and Sort Data:
• Select the column you want to filter by and use Excel's filter feature to display only the records that meet your selection criteria. You can do this by clicking on the filter icon in the column header and selecting the desired values.
• Once filtered, sort the filtered data by the column containing the key record numbers.
2. Read and Display Data:
• Now that your data is filtered and sorted, you can read and display the relevant information in another sheet or subfolder.
• You can use Excel formulas such as VLOOKUP or INDEX/MATCH to retrieve data from the filtered and sorted dataset and display it in the desired format.
• If you prefer automation, you can use VBA to write a macro that performs these tasks automatically. The macro can loop through the filtered and sorted data, read the required information, and populate it into another sheet or subfolder.
3. Creating a VBA Macro:
• Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
• Insert a new module by right-clicking on your workbook name in the Project Explorer window and selecting Insert > Module.
• Write your VBA code in the module to automate the process. Below is a basic example of VBA code that reads filtered and sorted data from one sheet and copies it to another:

Vba code is untested, please backup your file first.

Sub CopyFilteredData()
    Dim wsSource As Worksheet
    Dim wsDestination As Worksheet
    Dim lastRow As Long, i As Long
    
    ' Define source and destination worksheets
    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your source sheet name
    Set wsDestination = ThisWorkbook.Sheets.Add ' Add a new sheet for destination
    
    ' Find the last row in the source sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' Copy header row
    wsSource.Rows(1).Copy Destination:=wsDestination.Rows(1)
    
    ' Loop through data and copy filtered rows to destination sheet
    For i = 2 To lastRow
        If wsSource.Cells(i, 1).Value = "YourFilterCriteria" Then ' Change "YourFilterCriteria" to your actual criteria
            wsSource.Rows(i).Copy Destination:=wsDestination.Rows(wsDestination.Cells(wsDestination.Rows.Count, "A").End(xlUp).Row + 1)
        End If
    Next i
End Sub

1. Run the Macro:
• Close the VBA editor and return to your Excel workbook.
• Press Alt + F8 to open the "Run Macro" dialog.
• Select the macro you created (e.g., CopyFilteredData) and click "Run" to execute it.

This is a basic example to get you started. You may need to customize the code to fit your specific requirements and dataset structure. FormularbeginnThe texts, steps and code were created with the help of AI.

In the end, however, without more information about your specific situation, it's difficult to say for sure that this will solve the problem. Try changing the steps as suggested and see if that fixes the problem. The text was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.