Member Avatar for squeak24

Hey Everybody.

I have been asked to create a spreadsheet that will create a list of matches, for example in Table 1. (Please see the tables attached).

OK, fair enough, basic table so far. What I want to do is have three columns, that will return the results that match the criteria given. So say for example I have the following values in Table 2

This would return a list of results detailing that Result 1 and Result 2 as having "x's" in those columns.

Can this be done? If so, can you pleae point me in the right direction. I am thinking a Macro may be the way forward.

I know this could be done in Access, but I don't have access to it. I only have Excel.

Any help would be appreciated.

excel_query.pdf (16.95 KB)
Edited by squeak24 because: tidy formats
  • 3 Contributors
  • 3 Replies
  • 254 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by Stuugie

Dani AI

Generated

Assuming the master sheet is an Excel Table (call it Data) with a Name column and several flag columns that contain an x, the goal is to produce three adjacent output columns that list every Name with an x for each flag. noted Access isn’t available, correctly pointed out VBA works, and flagged the PDF was unclear — below are two non-VBA options plus a compact macro so any of those routes can be applied.

For Excel 365 / 2021 (dynamic arrays): put the flag name in the output header (e.g., F1 = "FlagA") and use this in F2 to spill the matches:

=IFERROR(FILTER(Data[Name], Data[FlagA]="x"), "No matches")

For older Excel (no FILTER): use AGGREGATE + INDEX to pull the nth match. Put this in F2 and copy down:

=IFERROR(INDEX(Data[Name], AGGREGATE(15,6,(ROW(Data[Name])-MIN(ROW(Data[Name]))+1)/(Data[FlagA]="x"), ROWS($F$2:F2))), "")

Copy across for each flag column (adjust header/column references to match each flag).

VBA (compact routine that writes each non-Name flag’s matches into columns starting at column F):

Sub BuildLists()
  Dim ws As Worksheet, tbl As ListObject
  Set ws = ActiveSheet
  Set tbl = ws.ListObjects("Data")
  Dim outCol As Long: outCol = ws.Range("F1").Column
  Dim r As Long, c As Long, outRow As Long
  For c = 2 To tbl.ListColumns.Count
    outRow = 2
    For r = 1 To tbl.ListRows.Count
      If LCase(Trim(tbl.DataBodyRange(r, c).Value)) = "x" Then
        ws.Cells(outRow, outCol).Value = tbl.DataBodyRange(r, 1).Value
        outRow = outRow + 1
      End If
    Next r
    outCol = outCol + 1
  Next c
End Sub

Quick checklist/troubleshooting: convert the source to an Excel Table and name it Data; ensure x entries are consistent (use TRIM/LOWER if needed); clear the output area before re-running a macro; if the matching logic is more complex (multiple simultaneous criteria), Power Query can do it without formulas or VBA.

Recommended Answers

All 3 Replies

Member Avatar for squeak24

I hope I have attached the PDF correctly, I can't see it.

Member Avatar for Slavi

Should be no issue at all to do using VBA. Go for macro and ask any questions if you encounter while developing it :)

Member Avatar for Stuugie

I could help but I'm not really sure what your output should be, even after looking at the pdf you uploaded.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.