VLOOKUP, MATCH, Not sure which

DataNut

New Member
Joined
Nov 15, 2017
Messages
6
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
AFG
1CategorybpmAlternate Category(ies)
2Beats - Electronica115 
3Action126
4Christmas135
5Corporate122Corporate, Fun & Happy
6Fun & Happy122Corporate, Fun & Happy
Alternate Categories
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top