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

schneitodd

New Member
Joined
Mar 18, 2003
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
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.

1608230809347.png
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,413
Office Version
  1. 365
Platform
  1. Windows
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.

Excel Formula:
=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

Well-known Member
Joined
Nov 26, 2020
Messages
1,410
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA 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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,413
Office Version
  1. 365
Platform
  1. Windows
If a vba approach is desired, this would be mine.

VBA Code:
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,410
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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 (y)
 

schneitodd

New Member
Joined
Mar 18, 2003
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1LocationKindAnswerPhaseBurger HutChangecommCommunicationCorwin/Flex TeamDoctor JonesFred BT (Bait and Tackle)Grace Memory HouseGrand Merchandise CenterGrowers County Method HouseInterpersonal RelationshipsLeadershipManfred HomesMary General StoreOno Gifts GaloreOreo Lamp GroupO’Charley FoodsResourcesRiverview Fish FrystafStaffingTimeWestside Meat MarketWork Life IntegrationWorkfloadWorkflowWorkload
2StaffFree Text Comment 1Doctor JonesWorkload
3StaffFree Text Comment 2Riverview Fish FryWorkload
4StaffFree Text Comment 3LeadershipManfred HomesWork Life Integration
5StaffFree Text Comment 4Grand Merchandise CenterLeadershipStaffingWorkflow
6StaffFree Text Comment 5LeadershipRiverview Fish FryStaffingWorkload
7StaffFree Text Comment 6ChangeCommunicationGrand Merchandise Center
8StaffFree Text Comment 7Doctor JonesStaffing
9StaffFree Text Comment 8Mary General StoreTimeWorkload
10StaffFree Text Comment 9Grand Merchandise CenterStaffing
11StaffFree Text Comment 10ChangeMary General Store
12StaffFree Text Comment 11Riverview Fish FryWork Life Integration
13StaffFree Text Comment 12Burger HutWorkload
14StaffFree Text Comment 13Manfred HomesWork Life Integration
15StaffFree Text Comment 14CommunicationLeadershipRiverview Fish Fry
16StaffFree Text Comment 15Grand Merchandise CenterStaffing
17StaffFree Text Comment 16ResourcesRiverview Fish FryStaffing
18StaffFree Text Comment 17CommunicationRiverview Fish Fry
19StaffFree Text Comment 18LeadershipRiverview Fish Fry
20StaffFree Text Comment 19commLeadershipRiverview Fish Fry
21StaffFree Text Comment 20Doctor JonesLeadership
22StaffFree Text Comment 21CommunicationGrand Merchandise CenterStaffingWorkload
23StaffFree Text Comment 22CommunicationOno Gifts GaloreWorkload
24StaffFree Text Comment 23Doctor JonesStaffing
25StaffFree Text Comment 24Growers County Method HouseStaffingWorkload
26StaffFree Text Comment 24Grand Merchandise CenterStaffingWorkload
27StaffFree Text Comment 25Interpersonal RelationshipsLeadershipManfred HomesWorkload
28StaffFree Text Comment 26Doctor JonesInterpersonal RelationshipsStaffing
29StaffFree Text Comment 27Manfred HomesStaffing
30StaffFree Text Comment 28CommunicationCorwin/Flex Team
31StaffFree Text Comment 29Mary General StoreResourcesWorkload
32StaffFree Text Comment 30Riverview Fish FryWork Life Integration
33StaffFree Text Comment 31Doctor JonesWorkload
34StaffFree Text Comment 32O’Charley FoodsStaffing
35StaffFree Text Comment 33Doctors HospitalLeadership
36StaffFree Text Comment 34Riverview Fish FryWorkload
37StaffFree Text Comment 35LeadershipRiverview Fish FryStaffing
38StaffFree Text Comment 36Riverview Fish Fry
39StaffFree Text Comment 37ChangeRiverview Fish Fry
40StaffFree Text Comment 38Riverview Fish FryWorkload
41StaffFree Text Comment 39Riverview Fish FryStaffingWorkload
42StaffFree Text Comment 40CommunicationRiverview Fish FryStaffingWorkload
43StaffFree Text Comment 41Grace Memory HouseStaffing
44StaffFree Text Comment 42Doctor JonesWork Life Integration
45StaffFree Text Comment 43Corwin/Flex TeamWork Life Integration
46StaffFree Text Comment 44CommunicationDoctor JonesLeadership
47StaffFree Text Comment 45Riverview Fish FryStaffing
48StaffFree Text Comment 46Westside Meat Market
49StaffFree Text Comment 46Oreo Lamp Group
50StaffFree Text Comment 47Fred BT (Bait and Tackle)
Data


Here is the list of locations from another tab:
Data file example2.xlsx
A
1Burger Hut
2Corwin/Flex Team
3Doctor Jones
4Fred BT (Bait and Tackle)
5Grace Memory House
6Grand Merchandise Center
7Growers County Method House
8Manfred Homes
9Mary General Store
10Ono Gifts Galore
11Oreo Lamp Group
12O’Charley Foods
13Riverview Fish Fry
14Westside 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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,413
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I've updated my profile.
Thanks for that, and for the XL2BB data. (y)

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.
Excel Formula:
=INDEX(E$1:AZ$1,MATCH(1,(E2:AZ2<>"")*ISNUMBER(MATCH(E$1:AZ$1,Locations!A$1:A$20,0)),0))
Excel Formula:
=INDEX(E2:AZ2,MATCH(1,(E2:AZ2<>"")*ISNUMBER(MATCH(E$1:AZ$1,Locations!A$1:A$20,0)),0))
 
Solution

schneitodd

New Member
Joined
Mar 18, 2003
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,413
Office Version
  1. 365
Platform
  1. Windows
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. :cool:
 

Forum statistics

Threads
1,136,275
Messages
5,674,786
Members
419,524
Latest member
helensesc

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
Top