Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by combo

  1. combo

    MS EXCEL 2016 LINE CHART NOT DROP TO ZERO - USING NESTED IF AND COUNTIF

    Some of the hard-coded ranges in formula have spaces, other not, is it ok? It would be nice to have a selection of your workbook with some dummy data that replicates this issue.
  2. combo

    Apply Filter

    Excel has two handy Power BI tools: Power Query and Power Pivot for data manipulation. You can find both solutions in attached workbook. In both cases source data have to be converted to table first. Next: 1. Power Query: A series of simple (recorded) queries, create full list of cases, a list...
  3. combo

    Need Genius Help - Finding multiple duplicates between columns - EXCEL

    I don't know if you ever worked with power query. Since a couple of recent Excel versions it is a built-in com add-in. A single query is a sequence of transformations that are recorded in power query desktop. The steps can be edited, it is also possible to write query without recording. The full...
  4. combo

    Need Genius Help - Finding multiple duplicates between columns - EXCEL

    BTW, it is a result of the queries with your data:
  5. combo

    Need Genius Help - Finding multiple duplicates between columns - EXCEL

    But it is how you described the problem. A value can be in any two or more columns. Whatever your problem is, I would start with defining structured table from your data. Next process it in Power Query: - add index column to locate row, - unpivot other columns transformation (relative to added...
  6. combo

    Hash encryption 64 bit

    32bit or 64bit refers to VBA here, so you need '#If Vba7...' instead of '#if Win64...'. Additionally, declaration 'As Long' should be replaced by 'As LongPtr' or 'As LongLong'. More...
  7. combo

    vba hangs

    Can you compile the code? What happens when you comment On Error...? MS Learn says (https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/on-error-statement): Statement Description On Error GoTo line Enables the error-handling routine that starts at line...
  8. combo

    Query expression negative to a 0

    I agree that UDF is the best tool to structure conditions. For simple calculations I frequently use: Function Max2(Val1 As Double, Val2 As Double) As Double If Val1 > Val2 Then Max2 = Val1 Else Max2 = Val2 End Function I would also check if your formula is proper. In: IIf(...
  9. combo

    Convert PDF file to text

    In Word VBA: Sub test() Dim wdDoc As Document Application.DisplayAlerts = wdAlertsNone Set wdDoc = Documents.Open(FileName:="PathAndFileName.pdf", AddToRecentFiles:=False) wdDoc.SaveAs2 FileName:="PathAndFileName.txt", FileFormat:=wdFormatText, AddToRecentFiles:=False wdDoc.Close...
  10. combo

    Printer.Print to PDF

    Using Office VBA code makes sense in case of automating one of Office applications. They use internal configurable converters for generating pdfs. Excel, Word and PowerPoint use document's ExportAsFixedFormat method (PowerPoint has two additional variants), Access DoCmd.OutputTo method...
  11. combo

    Power Query Split by Table Group by Rows into a new set of group by tables

    You can add subindex to each 'Face List Table' tables, expand, split subindex to GroupID, group again using GroupID (if grouping is necessary. An example with two tables: 1. 'query1': x1 100 (unique values) 20 ... 2. 'query2' (related table): x2 (related to 'x1'0 y 100 val1 100...
  12. combo

    Problem with Excel pasting into filtered cells

    It's just an Excel feature, hidden rows have still data and can be referenced in other cells. It seems that Excel copies visible cells. There are possibilities (copy & paste in filtered range): single cell copied, single cell selected to paste: trivial, single cell copied, range selected...
  13. combo

    SQL data types in CREATE/ALTER TABLE queries

    Thanks, it seems that there is a total mess here. I tested some types, my observations: Access will not save query with not recognized field type, a direct answer to my post: simple BIGINT and DATETIME2 work! TINYINT is not recognized, BYTE works, TEXT and LONGTEXT create text and long text...
  14. combo

    SQL data types in CREATE/ALTER TABLE queries

    I found a list a list of Access SQL data types from MS here. However, this article refers to Access 2013. In the meantime Access introduced large number and extended datetime fields handling. Is it possible to generate new types fields with access query, if so - what type names should be used?
  15. combo

    Calculate amt due

    With a layout as in the workbook, IMHO the only way is to manually arrange output. If it is possible to rearrange data, I would use: column example/remarks CustID C004200 Name Cusomer #4 SettlementID #1, #2, ... Type Settlement, collection Amount use negative in one of types (for...
See more

Part and Inventory Search

Back
Top