Getting same value with different options from drop-down list. For Google Sheets

[YaziR]

I'm currently working on a project where I need to create a drop-down list which should automatically populate the tables below. I'll straight away jump to the example for better understanding.
So the Sheet looks like this.
So the data is like

I think the table is self-explanatory. I have the data for each fruit that goes to which plate. So if I choose plate 5 from the dropdown list, I should get Apple, Orange, Pineapple, Mango and Berries in the table below.
Please help me with how to achieve this. Thank you in advance.

 

[kweaver]

Welcome to Mr. Excel.

Please clarify how you want the results presented. With 365 it's rather easy.
With 2019 you might consider:

temporary.xlsm
	A	B	C	D	E
1	Apple	Plate 1, Plate 3, Plate 5	Apple
2	Orange	Plate 1, Plate 2, Plate 4, Plate 5	Orange		Plate 5
3	Pineapple	Plate 5	Pineapple
4	Mango	Plate 1, Plate 4, Plate 5	Mango
5	Berries	Plate 5	Berries
6	Banana	Plate 1, Plate 2, Plate 3, Plate 4
Sheet5

Cell Formulas
Range	Formula
C1:C6	C1	=IF(ISNUMBER(SEARCH($E$2,B1)),A1,"")

 

[kweaver]

Another option is to use VBA and this routine with a helper column::

Function aconcat(a As Variant, Optional sep As String = " ") As String
' Harlan Grove, Mar 2002
    Dim y As Variant


    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If


    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

temporary.xlsm
	A	B	C	D	E
1	Apple	Plate 1, Plate 3, Plate 5		#VALUE!
2	Orange	Plate 1, Plate 2, Plate 4, Plate 5	Orange	19	Plate 4
3	Pineapple	Plate 5		#VALUE!
4	Mango	Plate 1, Plate 4, Plate 5	Mango	10
5	Berries	Plate 5		#VALUE!
6	Banana	Plate 1, Plate 2, Plate 3, Plate 4	Banana	28
7
8		Orange Mango Banana
Sheet5

Cell Formulas
Range	Formula
C1:C6	C1	=IF(ISNUMBER(SEARCH($E$2,B1)),A1,"")
D1:D6	D1	=SEARCH($E$2,B1)
B8	B8	=TRIM(MID(aconcat(IF(ISNUMBER(D1:D6)," "&A1:A6,"")),1,999))

 

[igold]

Another option would be to maintain your data more properly as shown below. You will then be in to slice and dice your data much more easily. Also you could easily use a Pivot Table to accomplish what you want.

Book1
	A	B	C	D	E
1	Basket	Plate		Plate	Plate 2
2	Apple	Plate 1
3	Orange	Plate 1		Banana
4	Mango	Plate 1		Orange
5	Banana	Plate 1
6	Orange	Plate 2
7	Banana	Plate 2
8	Apple	Plate 3
9	Banana	Plate 3
10	Orange	Plate 4
11	Mango	Plate 4
12	Banana	Plate 4
13	Apple	Plate 5
14	Orange	Plate 5
15	Pineapple	Plate 5
16	Mango	Plate 5
17	Berries	Plate 5
Sheet2

 

[igold]

As a follow-up, if you see/understand why storing your data as I suggested is a better alterative (and you are able to), you can use code like this to split your data out as shown. Your data would have to be in the exact format as shown in your OP. That is to say that the data that is contained in the Plates column contain a comma and a space between each item contained in each cell.

Please test on a copy of your data as this code will overwrite to your data and it will be essentially unrecoverable.

Sub SplitPlates()
    Dim pLates, arr
    Dim i As Long, ct As Long, r As Long
    Application.ScreenUpdating = False
    r = 2
    arr = Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
    For i = 1 To UBound(arr)
        pLates = Split(arr(i, 2), "," & " ")
        ct = UBound(pLates)
        Range("A" & r).Resize(UBound(pLates) + 1) = arr(i, 1)
        Range("B" & r).Resize(UBound(pLates) + 1) = Application.Transpose(pLates)
        r = r + ct + 1
    Next
    Application.ScreenUpdating = True
End Sub

 

[YaziR]

Thank you all for the replies. I was able to achieve the preferred result using search function. Thank you

@kweaver.​

 

[YaziR]

Welcome to Mr. Excel.

Please clarify how you want the results presented. With 365 it's rather easy.
With 2019 you might consider:

temporary.xlsm
	A	B	C	D	E
1	Apple	Plate 1, Plate 3, Plate 5	Apple
2	Orange	Plate 1, Plate 2, Plate 4, Plate 5	Orange		Plate 5
3	Pineapple	Plate 5	Pineapple
4	Mango	Plate 1, Plate 4, Plate 5	Mango
5	Berries	Plate 5	Berries
6	Banana	Plate 1, Plate 2, Plate 3, Plate 4
Sheet5

Cell Formulas
Range	Formula
C1:C6	C1	=IF(ISNUMBER(SEARCH($E$2,B1)),A1,"")

Hai. Apologies first. I tried to get the work done with the formula you sent me. It does the job. But I've not given you the full idea of what I'm trying to accomplish with the sheet. I thought of doing things myself with the idea you gave (So that I can learn in the process). However., It made things complicated. So I'll present the whole sheet to you.

As you can see, I have a drop-down at A1. The idea is., if I select a sector from the drop-down list., the data below should get filtered. (Eg: If I select sector 1 in the dropdown, then only row 3, 5, 10, 11, 13 and 17 should be visible for me to edit.)
The data is massive. Around 750 persons with 35 sectors. Each person may be assigned with one sector or multiple sectors. Color changes are done by conditional formatting. And the problem is., It has to be done without macros or VBAs using google sheets.

Link for Example sheet: Report Sheet

Thank you.

 

[igold]

It would have been nice to have this information in your OP. People here freely donate their time. If you could not use macros, that should have been stated. Good luck.

 

[Fluff]

using google sheets.

That is something that you should have clearly stated in your original post (preferably in the title) & you should also post such questions in the General Discussion & Other Applications section of the board. I have done this for you this time.

 

[YaziR]

I apologise again to you all for not giving clear information. I learned many new things from the macros and VBA posted here. Please don't think it was wasted. I'm curious to know how this can be solved. Please help me with this.
Thank you.