How do I search a row for a specific text and return with the column it is in, as in this example

bilalsana

New Member
Joined
Oct 12, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
I have a sheet for distributing fruits based on the date, making sure they are not repeated in the different halls on the same date, as seen in the picture, I want to see the table below so that I know where that fruit is being served on that day. Importantly the new table would be in Sheet2 (unlike in the example)
tables.jpg
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,796
No problem, but post whole expected result not a part only and source data also
 

bilalsana

New Member
Joined
Oct 12, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

I don't understand, how's the expected result not correct?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,796
source:
Jan-02 and Dinning 2 contain Pear, Apple, Mango
Result
Jan-02 contain Pear for Dinning 2 only
 

bilalsana

New Member
Joined
Oct 12, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Book1.xlsx
B
9Dinning 1
Sheet1
Cell Formulas
RangeFormula
B9B9=TEXTJOIN(", ",TRUE,IF($A$2:$A$4=B$8,IF($B$2:$F$4=$A9,$B$1:$F$1,""),""))


Anyywas, here is my solution but it breaks with comma seperated values
 

bilalsana

New Member
Joined
Oct 12, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Book1.xlsx
ABCDEF
1Dinning 1Dinning 2Dinning 35Dinning 4
2Jan-01ApplePearPeachMango
3Jan-02BananaPear, Apple MangoPeachAppleMango
4Jan-03PearBananaPeachMangoApple
5
6
7
8Jan-01Jan-02Jan-03
9AppleDinning 15Dinning 4
10Banana 
11PearDinning 3
12Peach
13Mango
Sheet1
Cell Formulas
RangeFormula
C9:D9,B9:B11C9=TEXTJOIN(", ",TRUE,IF($A$2:$A$4=C$8,IF($B$2:$F$4=$A9,$B$1:$F$1,""),""))
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,796
create expected result manually
or choose one
sourceResult1
DateDinning 1Dinning 2Dinning 3Dinning 4Fruit01/01/200101/01/200201/01/2003
01/01/2001AppleBananaPear, Peach, PlumMangoAppleDinning 1Dinning 4
01/01/2002BananaPearPeach, Plum, AppleMangoBananaDinning 2Dinning 1Dinning 2
01/01/2003PearBananaPeach, MangoAppleMangoDinning 4Dinning 4
Peach, MangoDinning 3
Peach, Plum, AppleDinning 3
PearDinning 2Dinning 1
Pear, Peach, PlumDinning 3
Result2
Fruit01/01/200101/01/200201/01/2003
AppleDinning 1Dinning 3Dinning 4
BananaDinning 2Dinning 1Dinning 2
MangoDinning 4Dinning 4Dinning 3
PeachDinning 3Dinning 3Dinning 3
PearDinning 3Dinning 2Dinning 1
PlumDinning 3Dinning 3
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,310
Office Version
  1. 365
Platform
  1. Windows
Is this what you are trying to achieve?

bilalsana.xlsm
ABCDEF
1Dinning 1Dinning 2Dinning 3Dinning 4Dinning 5
2Jan-01ApplePearPeachMango
3Jan-02BananaPear, Apple MangoPeachAppleMango
4Jan-03PearBananaPeachMangoApple
5
6
7
8Jan-01Jan-02Jan-03
9AppleDinning 1Dinning 2, Dinning 4Dinning 5
10Banana Dinning 1Dinning 2
11PearDinning 3Dinning 2Dinning 1
12PeachDinning 4Dinning 3Dinning 3
13MangoDinning 5Dinning 2, Dinning 5Dinning 4
Sheet1
Cell Formulas
RangeFormula
B9:D13B9=TEXTJOIN(", ",1,FILTER($B$1:$F$1,ISNUMBER(SEARCH(" "&$A9&" ",INDEX(SUBSTITUTE(" "&$B$2:$F$4&" ",","," "),MATCH(B$8,$A$2:$A$4,0),0))),""))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,406
Messages
5,528,591
Members
409,827
Latest member
Tmcgrew05

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top