Search row for name in column, populate name in new column

[schneitodd]

I have a data file that has a column for each item that was "tagged." The tags are topics or locations, but are sorted alphabetically, and additional tags could be added in the future (so the column numbers might change).
I would like to have Excel scan the row for the noted locations (locations represented by yellow columns) and place the name of the location in a new column I've added (highlighted in blue). Every row should only have 1 location (yellow column) populated. I can generate a discrete list of locations if I need to compare each cell to a list.
I lack VBA skills and typically try to rely on nested functions, but I'm open on the approach and willing to learn.

 

[Peter_SSs]

You will generally get faster/better suggestions if you give us sample data in a form that we can copy.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a “Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this in D2 and copied down does what you want. A formula cannot see the colour of the cells it is referencing so I have used the fact that in your image all the yellow cells have a space as the 2nd character in the heading & the others do not. That may not work if you add many more columns & need to go beyond Z, but we can probably work out another way if otherwise this is what you want.

=IFERROR(INDEX(E2:ZZ2,AGGREGATE(15,6,(COLUMN(E2:ZZ2)-COLUMN(E2)+1)/((E2:ZZ2<>"")*(MID(E$1:ZZ$1,2,1)=" ")),1)),"No Location")

 

[Zot]

My understanding is that you want to fill in Location column D with location data in yellow column. For each row, there is only one location to be found no matter how many yellow columns there are.

If you are into VBA, here is the code:

Sub FindLoc()

Dim iRow As Long, eRow As Long, iCol As Long, eCol As Long
Dim cellLoc As Range, cellCol As Range, rngLoc As Range, rngTag As Range
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("SheetName")          ' Replace with sheet name where table resides

Application.ScreenUpdating = False

' Set initial Row & Column
iRow = 2
iCol = 5                                                   ' This is for colum E in alphabet

' Find last Row and Column
eRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
eCol = Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column

' Define range of Location
Set rngLoc = ws.Range("D" & iRow, "D" & eRow)

' Loop through all rows in rngLoc
For Each cellLoc In rngLoc
    ' Define range of Tag each time the row changed
    Set rngTag = ws.Range(Cells(cellLoc.Row, iCol), Cells(cellLoc.Row, eCol))
    For Each cellCol In rngTag
        If cellCol.Interior.Color = vbYellow Then
            If Not Len(cellCol) = 0 Then
                cellLoc = cellCol
            End If
        End If
    Next cellCol
Next cellLoc

Application.ScreenUpdating = True

End Sub

 

[Peter_SSs]

If a vba approach is desired, this would be mine.

Sub Find_Loc()
  Dim rLoc As Range
  Dim Cols As Long, Rw As Long
  
  Cols = Cells(1, Columns.Count).End(xlToLeft).Column - 4
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = vbYellow
  For Rw = 2 To Range("A" & Rows.Count).End(xlUp).Row
    Set rLoc = Cells(Rw, 5).Resize(, Cols).Find(What:="?*", LookIn:=xlValues, SearchFormat:=True)
    If Not rLoc Is Nothing Then Cells(Rw, 4).Value = rLoc.Value
  Next Rw
  Application.FindFormat.Clear
End Sub

 

[Zot]

If a vba approach is desired, this would be mine.

Sub Find_Loc()
  Dim rLoc As Range
  Dim Cols As Long, Rw As Long
 
  Cols = Cells(1, Columns.Count).End(xlToLeft).Column - 4
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = vbYellow
  For Rw = 2 To Range("A" & Rows.Count).End(xlUp).Row
    Set rLoc = Cells(Rw, 5).Resize(, Cols).Find(What:="?*", LookIn:=xlValues, SearchFormat:=True)
    If Not rLoc Is Nothing Then Cells(Rw, 4).Value = rLoc.Value
  Next Rw
  Application.FindFormat.Clear
End Sub

The difference between pro and amateur ?
Simpler and faster code

 

[schneitodd]

In an attempt to explain my need and de-identify the data, I inadvertently misrepresented my data. The Locations on the input file are not highlighted yellow (I added that to show how they are mixed in with the other tags). Also the location names are variety of words/phrases. I changed the names to fictional names that more closely resemble the actual name lengths.
Since the location Column is added to the data file, I moved it to the first column.

Here is a more representative example of the data:

Data file example2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	Location	Kind	Answer	Phase	Burger Hut	Change	comm	Communication	Corwin/Flex Team	Doctor Jones	Fred BT (Bait and Tackle)	Grace Memory House	Grand Merchandise Center	Growers County Method House	Interpersonal Relationships	Leadership	Manfred Homes	Mary General Store	Ono Gifts Galore	Oreo Lamp Group	O’Charley Foods	Resources	Riverview Fish Fry	staf	Staffing	Time	Westside Meat Market	Work Life Integration	Workfload	Workflow	Workload
2		Staff	Free Text Comment 1							Doctor Jones																					Workload
3		Staff	Free Text Comment 2																				Riverview Fish Fry								Workload
4		Staff	Free Text Comment 3													Leadership	Manfred Homes											Work Life Integration
5		Staff	Free Text Comment 4										Grand Merchandise Center			Leadership									Staffing					Workflow
6		Staff	Free Text Comment 5													Leadership							Riverview Fish Fry		Staffing						Workload
7		Staff	Free Text Comment 6			Change		Communication					Grand Merchandise Center
8		Staff	Free Text Comment 7							Doctor Jones															Staffing
9		Staff	Free Text Comment 8															Mary General Store								Time					Workload
10		Staff	Free Text Comment 9										Grand Merchandise Center												Staffing
11		Staff	Free Text Comment 10			Change												Mary General Store
12		Staff	Free Text Comment 11																				Riverview Fish Fry					Work Life Integration
13		Staff	Free Text Comment 12		Burger Hut																										Workload
14		Staff	Free Text Comment 13														Manfred Homes											Work Life Integration
15		Staff	Free Text Comment 14					Communication								Leadership							Riverview Fish Fry
16		Staff	Free Text Comment 15										Grand Merchandise Center												Staffing
17		Staff	Free Text Comment 16																			Resources	Riverview Fish Fry		Staffing
18		Staff	Free Text Comment 17					Communication															Riverview Fish Fry
19		Staff	Free Text Comment 18													Leadership							Riverview Fish Fry
20		Staff	Free Text Comment 19				comm									Leadership							Riverview Fish Fry
21		Staff	Free Text Comment 20							Doctor Jones						Leadership
22		Staff	Free Text Comment 21					Communication					Grand Merchandise Center												Staffing						Workload
23		Staff	Free Text Comment 22					Communication											Ono Gifts Galore												Workload
24		Staff	Free Text Comment 23							Doctor Jones															Staffing
25		Staff	Free Text Comment 24											Growers County Method House											Staffing						Workload
26		Staff	Free Text Comment 24										Grand Merchandise Center												Staffing						Workload
27		Staff	Free Text Comment 25												Interpersonal Relationships	Leadership	Manfred Homes														Workload
28		Staff	Free Text Comment 26							Doctor Jones					Interpersonal Relationships										Staffing
29		Staff	Free Text Comment 27														Manfred Homes								Staffing
30		Staff	Free Text Comment 28					Communication	Corwin/Flex Team
31		Staff	Free Text Comment 29															Mary General Store				Resources									Workload
32		Staff	Free Text Comment 30																				Riverview Fish Fry					Work Life Integration
33		Staff	Free Text Comment 31							Doctor Jones																					Workload
34		Staff	Free Text Comment 32																		O’Charley Foods				Staffing
35		Staff	Free Text Comment 33							Doctors Hospital						Leadership
36		Staff	Free Text Comment 34																				Riverview Fish Fry								Workload
37		Staff	Free Text Comment 35													Leadership							Riverview Fish Fry		Staffing
38		Staff	Free Text Comment 36																				Riverview Fish Fry
39		Staff	Free Text Comment 37			Change																	Riverview Fish Fry
40		Staff	Free Text Comment 38																				Riverview Fish Fry								Workload
41		Staff	Free Text Comment 39																				Riverview Fish Fry		Staffing						Workload
42		Staff	Free Text Comment 40					Communication															Riverview Fish Fry		Staffing						Workload
43		Staff	Free Text Comment 41									Grace Memory House													Staffing
44		Staff	Free Text Comment 42							Doctor Jones																		Work Life Integration
45		Staff	Free Text Comment 43						Corwin/Flex Team																			Work Life Integration
46		Staff	Free Text Comment 44					Communication		Doctor Jones						Leadership
47		Staff	Free Text Comment 45																				Riverview Fish Fry		Staffing
48		Staff	Free Text Comment 46																								Westside Meat Market
49		Staff	Free Text Comment 46																	Oreo Lamp Group
50		Staff	Free Text Comment 47								Fred BT (Bait and Tackle)
Data

Here is the list of locations from another tab:

Data file example2.xlsx
	A
1	Burger Hut
2	Corwin/Flex Team
3	Doctor Jones
4	Fred BT (Bait and Tackle)
5	Grace Memory House
6	Grand Merchandise Center
7	Growers County Method House
8	Manfred Homes
9	Mary General Store
10	Ono Gifts Galore
11	Oreo Lamp Group
12	O’Charley Foods
13	Riverview Fish Fry
14	Westside Meat Market
Locations

@Peter_SSs - Thanks for the tips. I've updated my profile. The file will use Excel 2016, but I can't add add-ins at work, so the pics above are from Excel 2013. I've attempted to use XL2BB to paste above.

 

[Peter_SSs]

I've updated my profile.

Thanks for that, and for the XL2BB data.

However, when providing sample data it is also a good idea to include the expected results. As it is, I am not sure if you want the heading from the relevant column returned or the value from the data row.
I think one of these in A2 and copied down should be close to what you want.

=INDEX(E$1:AZ$1,MATCH(1,(E2:AZ2<>"")*ISNUMBER(MATCH(E$1:AZ$1,Locations!A$1:A$20,0)),0))

=INDEX(E2:AZ2,MATCH(1,(E2:AZ2<>"")*ISNUMBER(MATCH(E$1:AZ$1,Locations!A$1:A$20,0)),0))

 

[schneitodd]

Perfect. Thank you!
At first I was receiving the #VALUE! error, but later realized this needs to be treated as an array. After using CTRL+SHIFT+ENTER, it worked as needed.

 

[Peter_SSs]

Perfect. Thank you!

You're welcome. Thanks for the follow-up.

At first I was receiving the #VALUE! error, but later realized this needs to be treated as an array. After using CTRL+SHIFT+ENTER, it worked as needed.

Sorry, I often forget that now since with Excel 365, which I am using, you do not need to do the C+S+E any more.