VLOOKUP, MATCH, Not sure which

[DataNut]

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...

[TABLE="class: grid, width: 500"]
<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>[TR]
[TD="width: 142"]Category
[/TD]
[TD="width: 398"]Group Notes[/TD]
[TD="width: 246"]Group Title[/TD]
[TD="width: 324"]Sub-Group Notes[/TD]
[TD="width: 214"]Sub-Group Sub-Ttiles[/TD]
[TD="width: 46"]bpm[/TD]
[TD="width: 201"]Alternate Category(ies)[/TD]
[/TR]
[TR]
[TD]Beats - Electronica[/TD]
[TD]Focus on synthesizers & drum machines[/TD]
[TD]Melodic Breakfast[/TD]
[TD]Breakbeats w-optimistic feel[/TD]
[TD] In Motion[/TD]
[TD]115[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Action[/TD]
[TD]High energy music for action productions[/TD]
[TD]Power Factory[/TD]
[TD]Hard hitting powerful tracks[/TD]
[TD] Nine Lives[/TD]
[TD]126[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Christmas[/TD]
[TD]Christmas[/TD]
[TD]Christmas vol 1[/TD]
[TD][/TD]
[TD]12 Days of Christmas[/TD]
[TD]135[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Corporate[/TD]
[TD]Inspirational for business[/TD]
[TD]Quirky Fun[/TD]
[TD]Friendly fun & a bit silly[/TD]
[TD]6 Hour Day[/TD]
[TD]122[/TD]
[TD]Corporate, Fun & Happy
[/TD]
[/TR]
[TR]
[TD]Fun & Happy[/TD]
[TD]Happy, fun, kid show/advertising oriented[/TD]
[TD]Quirky Fun[/TD]
[TD]Friendly fun & a bit silly[/TD]
[TD]6 Hour Day[/TD]
[TD]122[/TD]
[TD]Corporate, Fun & Happy
[/TD]
[/TR]
</tbody>[/TABLE]

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]

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.

Book1
	A	F	G
1	Category	bpm	Alternate Category(ies)
2	Beats - Electronica	115
3	Action	126
4	Christmas	135
5	Corporate	122	Corporate, Fun & Happy
6	Fun & Happy	122	Corporate, Fun & Happy
Alternate Categories

Cell Formulas
Range	Formula
G2	{=IF(COUNTIF(F$2:F$6,F2)=1,"",TEXTJOIN(", ",TRUE,IF(F$2:F$6=F2,A$2:A$6,"")))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DataNut]

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]

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.

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