Search Filter Mapping Matrix - via VBA code

ShuStar

Board Regular
Joined
Sep 9, 2015
Messages
66
Office Version
2010
Platform
Windows
Hi all,

Really need some support in figuring out how to do this. Any help would be greatly appreciated.

Unfortunately I'm having issues with XL2BB, but in the meantime my excel workbook can be found on this google drive link: Excel - Google Drive

Mapping Filter WorkBook


Context:
In the raw data tab there are Names matched against Fruits which people enjoy. Y is for yes. N is for No.
The Keyword Tab is shortlisted words from the Raw Data Tab - this is static and manually inputted.
Matrix tab - based on the ask below, the mapping is to be plotted here.

The ask:
For each fruit in the Keyword tab, I would like for the VBA code to scan this list cell by cell and match it to any names where it is marked Y in the Raw Data tab; then plot this in the Matrix tab.

Notes:
A - In the keywords tab, they words will always be in Column B, however rows are not fixed; in the example there are 7 words in Column B but sometimes this could be 3 or even 10 or 100 - it depends if the columns in raw data tab increase too; if a fixed range needs to be provided then 500 rows would be a safe bet to fix it at.
B - In the Matrix tab, it should only contain the Names which are impacted by any of the key words (ie not all the names or fruits from Raw Data tab should be included)
C - not looking to resolve this by a Vlookup/Index Match - has to be done by VBA code I'm afraid

Any help in figuring out how this should be would be greatly appreciated (I got stuck trying to create the loop etc).

In the link contains my code but I think I have lost myself - basically i was trying to match the keywords and then do a filter somehow.. nevermind, will hopefully get better with more practice.. probably worth ignoring...
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,596
Office Version
365
Platform
Windows
How about
VBA Code:
Sub ShuStar()
   Dim KeyAry As Variant, DataAry As Variant, OutAry As Variant
   Dim r As Long, nr As Long, c As Long
   Dim nc As Variant
   Dim Flg As Boolean
   
   With Sheets("Keywords")
      KeyAry = Application.Transpose(.Range("B3", .Range("B" & Rows.Count).End(xlUp)).Value2)
   End With
   DataAry = Sheets("Raw Data").Range("A2").CurrentRegion.Value2
   ReDim OutAry(1 To UBound(DataAry), 1 To UBound(KeyAry) + 2)
   
   For r = 2 To UBound(DataAry)
      For c = 2 To UBound(DataAry, 2)
         nc = Application.Match(DataAry(1, c), KeyAry, 0)
         If Not IsError(nc) And DataAry(r, c) = "Y" Then
            If Not Flg Then
               nr = nr + 1
               Flg = True
               OutAry(nr, 1) = DataAry(r, 1)
            End If
            OutAry(nr, nc + 2) = DataAry(r, c)
         End If
      Next c
      Flg = False
   Next r
   With Sheets("Matrix")
      .UsedRange.ClearContents
      .Range("A2:b2").Value = Array("Names", "Count")
      .Range("C2").Resize(, UBound(KeyAry)).Value = KeyAry
      .Range("A3").Resize(nr, UBound(OutAry, 2)).Value = OutAry
   End With
End Sub
 

ShuStar

Board Regular
Joined
Sep 9, 2015
Messages
66
Office Version
2010
Platform
Windows
Thank you so much! This is working as expected!

Is there a way to include the Countif into the code? Effectively to count the Y's in the matrix column?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,596
Office Version
365
Platform
Windows
How about
VBA Code:
Sub ShuStar()
   Dim KeyAry As Variant, DataAry As Variant, OutAry As Variant
   Dim r As Long, nr As Long, c As Long, Cnt As Long
   Dim nc As Variant
   Dim Flg As Boolean
   
   With Sheets("Keywords")
      KeyAry = Application.Transpose(.Range("B3", .Range("B" & Rows.Count).End(xlUp)).Value2)
   End With
   DataAry = Sheets("Raw Data").Range("A2").CurrentRegion.Value2
   ReDim OutAry(1 To UBound(DataAry), 1 To UBound(KeyAry) + 2)
   
   For r = 2 To UBound(DataAry)
      For c = 2 To UBound(DataAry, 2)
         nc = Application.Match(DataAry(1, c), KeyAry, 0)
         If Not IsError(nc) And DataAry(r, c) = "Y" Then
            If Not Flg Then
               nr = nr + 1
               Flg = True
               OutAry(nr, 1) = DataAry(r, 1)
            End If
            Cnt = Cnt + 1
            OutAry(nr, nc + 2) = DataAry(r, c)
         End If
      Next c
      If Flg Then OutAry(nr, 2) = Cnt
      Cnt = 0
      Flg = False
   Next r
   With Sheets("Matrix")
      .UsedRange.ClearContents
      .Range("A2:b2").Value = Array("Names", "Count")
      .Range("C2").Resize(, UBound(KeyAry)).Value = KeyAry
      .Range("A3").Resize(nr, UBound(OutAry, 2)).Value = OutAry
   End With
End Sub
 

ShuStar

Board Regular
Joined
Sep 9, 2015
Messages
66
Office Version
2010
Platform
Windows
Many thanks @Fluff, nailed it, it is working as needed :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,596
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback.
 

ShuStar

Board Regular
Joined
Sep 9, 2015
Messages
66
Office Version
2010
Platform
Windows
Hi - sorry to come back to this.

I'm getting a runtime error '1004' - Aplication defined or object-defined error. And upon clicking debug it makes this code row yellow:

.Range("A3").Resize(nr, UBound(OutAry, 2)).Value = OutAry
End With


Do you know why this could be the case or how i can resolve? It works fine in the sample example I gave, but when i take it to my main sheet its not working - however my main sheet is exactly the same as the sample example...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,596
Office Version
365
Platform
Windows
That line shouldn't cause any problems, have you made any changes to the code?
 

ShuStar

Board Regular
Joined
Sep 9, 2015
Messages
66
Office Version
2010
Platform
Windows
Ahh i realise why.

In my Main Worksheet Raw Data Tab - there is a small line of text in A1 - this wasn't in the sample sheet - my bad. When I clear the cell in A1 in my main raw data sheet, the code is now working as the way you provided it.

Thank you.
 

ShuStar

Board Regular
Joined
Sep 9, 2015
Messages
66
Office Version
2010
Platform
Windows
Is there a way to adapt the code so it ignores or skips if there's any text in A1 in the Raw Data Tab without breaking the code?

For extracts I will export, text being in A1 in the raw data tab is 50/50 - this text is dependent on the type of extract exported. There isn't any more text to be placed anywhere else (ie randomly in C1 or D1)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,242
Messages
5,485,607
Members
407,504
Latest member
inexperiencedOne

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top