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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,673
like this?
Value01/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
 

bilalsana

New Member
Joined
Oct 12, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
like this?
Value01/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
Yes exactly. But some cells have multiple fruit listed like: Apple,Pear, Banana
 

bilalsana

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

ADVERTISEMENT

As in the above example, if column B has "Apple,Banana" on Jan-01, I would like it to return Dinning 1 in both B9 and B10
 

bilalsana

New Member
Joined
Oct 12, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
As in the above example, if column B has "Apple,Banana" on Jan-01, I would like it to return Dinning 1 in both B9 and B10
Ofcourse there would be no doubling, as in this example. 1 entry would only come once in a row. Dinning 2 would not have Banana in it, unlike in the example
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,673

ADVERTISEMENT

???
Value01/01/200101/01/200201/01/2003
AppleDinning 1Dinning 4
BananaDinning 2Dinning 1Dinning 2
MangoDinning 4Dinning 4
Peach, AppleDinning 3
Peach, MangoDinning 3
PearDinning 2Dinning 1
Pear, PeachDinning 3
 

bilalsana

New Member
Joined
Oct 12, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Yes. But will it scan fir more than 2 items in the same cell (seperated by commas)?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,673
like this?
Value01/01/200101/01/200201/01/2003
AppleDinning 1Dinning 4
BananaDinning 2Dinning 1Dinning 2
MangoDinning 4Dinning 4
Peach, MangoDinning 3
Peach, Plum, AppleDinning 3
PearDinning 2Dinning 1
Pear, Peach, PlumDinning 3
 

bilalsana

New Member
Joined
Oct 12, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
like this?
Value01/01/200101/01/200201/01/2003
AppleDinning 1Dinning 4
BananaDinning 2Dinning 1Dinning 2
MangoDinning 4Dinning 4
Peach, MangoDinning 3
Peach, Plum, AppleDinning 3
PearDinning 2Dinning 1
Pear, Peach, PlumDinning 3
Screenshot 2020-10-13 011831.jpg

Like this
 

Watch MrExcel Video

Forum statistics

Threads
1,108,538
Messages
5,523,428
Members
409,522
Latest member
szophie

This Week's Hot Topics

Top