VLOOKUP, MATCH, Not sure which

DataNut

New Member
i All,

Have come to a wall I'd love to de-brick...Not sure which function or how to solve this problem.

I've a single sheet that contains the following...

Category
Group NotesGroup TitleSub-Group NotesSub-Group Sub-TtilesbpmAlternate Category(ies)
Beats - ElectronicaFocus on synthesizers & drum machinesMelodic BreakfastBreakbeats w-optimistic feel In Motion115
ActionHigh energy music for action productionsPower FactoryHard hitting powerful tracks Nine Lives126
ChristmasChristmasChristmas vol 112 Days of Christmas135
CorporateInspirational for businessQuirky FunFriendly fun & a bit silly6 Hour Day122Corporate, Fun & Happy
Fun & HappyHappy, fun, kid show/advertising orientedQuirky FunFriendly fun & a bit silly6 Hour Day122Corporate, Fun & Happy

<colgroup><col style="mso-width-source:userset;mso-width-alt:3776;width:106pt" width="142"> <col style="mso-width-source:userset;mso-width-alt:10624;width:299pt" width="398"> <col style="mso-width-source:userset;mso-width-alt:6549;width:184pt" width="246"> <col style="mso-width-source:userset;mso-width-alt:8640;width:243pt" width="324"> <col style="mso-width-source:userset;mso-width-alt:5717;width:161pt" width="214"> <col style="mso-width-source:userset;mso-width-alt:1216;width:34pt" width="46"> <col style="mso-width-source:userset;mso-width-alt:5354;width:151pt" width="201"> </colgroup><tbody>
</tbody>

I want to write a formula that I can install in the column titled "Alternate Category(ies)" so that it returns the data displayed in the column in bold faced print.

The current sheet is nearly 1000 lines long. In order to find these two matches I simply sorted the sheet to stack the information, not elegant, nor practical.

Thank you most kindly for the assist.

Respectfully,

DataNut
 

Peter_SSs

MrExcel MVP, Moderator
Re: VLOOKUP, MATCH, Not sure which and need help...

You haven't said what it is about those two lines that leads to them having a value in that last column. ;)
I am assuming that it is the fact that they have the same value in the bpm column. If that is not it then please give further clarification.

This suggestion requires that you are using a version of Excel that supports the TEXTJOIN function. Typically that is a subscription to Office 365.

This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Category</td><td style="text-align: right;;">bpm</td><td style=";">Alternate Category(ies)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Beats - Electronica</td><td style="text-align: right;;">115</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Action</td><td style="text-align: right;;">126</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Christmas</td><td style="text-align: right;;">135</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Corporate</td><td style="text-align: right;;">122</td><td style=";">Corporate, Fun & Happy</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Fun & Happy</td><td style="text-align: right;;">122</td><td style=";">Corporate, Fun & Happy</td></tr></tbody></table><p style="width:16em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Alternate Categories</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">{=IF(<font color="Blue">COUNTIF(<font color="Red">F$2:F$6,F2</font>)=1,"",TEXTJOIN(<font color="Red">", ",TRUE,IF(<font color="Green">F$2:F$6=F2,A$2:A$6,""</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

DataNut

New Member
Re: VLOOKUP, MATCH, Not sure which and need help...

Greetings Peter,

I am most grateful for your reply, the assist and apologize for the lack of clarity in the original post.

Clarification:

The song titles (col E:Sub-Group-Sub-Titles) exist in multiple categories (col A: Category).

I need to be able to populate Alternate Category(ies) (col G) - with the names of the Category (col A) each time the title appears in the catalog. Hence the reason rows 5 & 6 read, "Corporate", "Fun & Happy": these are the Categories in which the song title 6 Hour Day, appears.

Col F: bpm - is not a discreet value as it is can be shared by multiple titles, so utilizing it as the deciding factor will not work. Only Col E: Sub-Group-Sub-Titles) can the discreet search criteria.

I hope that makes the request clearer.

I installed the formula you provided and when it came to row 5, it returned #NAME?, up to that point it left the cell blank, which in the end is (not something I thought of), but in fact ideal.


Again, most appreciative for the assist.

Lastly, hoping you're dry. I thought I saw something in the news recently regarding some rather intense rains (perhaps an understatement) in your part of the world.

Respectfully

DataNut
 

Peter_SSs

MrExcel MVP, Moderator
Re: VLOOKUP, MATCH, Not sure which and need help...

Thanks for the added clarification & yes, weather is not too wet here, thanks.

The adjustment to my formula relating to the different column use would be, still confirmed with Ctrl+Shift+Enter
=IF(COUNTIF(E$2:E$6,E2)=1,"",TEXTJOIN(", ",TRUE,IF(E$2:E$6=E2,A$2:A$6,"")))

However, the #NAME? error indicates that you do not have a version of Excel per my previous post
This suggestion requires that you are using a version of Excel that supports the TEXTJOIN function. Typically that is a subscription to Office 365.
In that case, I think you will need a macro. Here is one that I think does what you require.
Test in a copy of your workbook.
Code:
Sub Alternate_Categories()
  Dim a As Variant
  Dim d As Object
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  With Range("A2:G" & Range("A" & Rows.Count).End(xlUp).Row)
    a = .Value
    For i = 1 To UBound(a)
      d(a(i, 5)) = d(a(i, 5)) & "|||" & a(i, 1)
    Next i
    For i = 1 To UBound(a)
      If InStr(4, d(a(i, 5)), "|||") > 0 Then a(i, 7) = Replace(Mid(d(a(i, 5)), 4), "|||", ", ")
    Next i
    .Value = a
  End With
End Sub
 
Last edited:

Some videos you may like

This Week's Hot Topics

Top