Populate a column based on values in two other columns

[RodneyC]

I have a list of items in $A$2:$A$15 In column $B$2:$B$15 there is a for the corresponding value in A. The value in column B allows me to define what “group” the item in column A belongs to.

In column E, I want a list of items in each group. From the screen shot below, you can see the idea. I’ve color coded this simply for ease of explanation, no coloring is needed when finished. I’m thinking I may need separate codes for each group. The values in column B are likely to change over time... if I change B2 to 3 instead of 1 as it currently is, it would automatically be moved out of Group 1 into Group 3 in column E

I'm needing something in E2:E5 that looks for items in column B with the value “1” and if it’s there, populate the cells with the appropriate value in column A. I would need a similar formula would be needed for Group 2 and so on.

Thinking ahead, it’s not important what order these are displayed in, I’m assuming it would do it based on the order these are displayed on column A.

Thank you in advance

 

[Fluff]

Can you please post the actual data using the XL2BB add-in, rather than just an image.
Otherwise you are relying on members to spend there time re-creating what you already have.

 

[RodneyC]

Cool tool! Here it is. There are no formulas currently. No conditional formatting or data validation at this point.

Colors.xlsx
	A	B	C	D	E
1	COLOR	GROUP			Group 1
2	Green	1			Green
3	Blue	1			Blue
4	Yellow	2			Red
5	Orange	2			Purple
6	Tangerine	2
7	Red	1			Group 2
8	Black	3			Yellow
9	Magenta	2			Orange
10	Brown	2			Tangerine
11	White	3			Magenta
12	Silver	3			Brown
13	Purple	1
14	Lime	3			Group 3
15	Banana	3			Black
16					White
17					Silver
18					Lime
19					Banana
MR Excel

 

[Fluff]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B	C	D	E
1	COLOR	GROUP
2	Green	1			Green
3	Blue	1			Blue
4	Yellow	2			Red
5	Orange	2			Purple
6	Tangerine	2
7	Red	1			Yellow
8	Black	3			Orange
9	Magenta	2			Tangerine
10	Brown	2			Magenta
11	White	3			Brown
12	Silver	3
13	Purple	1			Black
14	Lime	3			White
15	Banana	3			Silver
16					Lime
17					Banana
18
Data

Cell Formulas
Range	Formula
E2:E17	E2	=LET(u,UNIQUE(B2:B15),r,ROWS(u),s,SEQUENCE(ROWS(B2:B15)+r-1,,0),i,INDEX(u,MOD(s,r-1)+1),INDEX(SORT(IFERROR(INDEX(A2:B15,s+1,{1,2}),CHOOSE({1,2},"",i)),2),,1))
Dynamic array formulas.

 

[RodneyC]

Copy/pasted

=LET(u,UNIQUE(B2:B15),r,ROWS(u),s,SEQUENCE(ROWS(B2:B15)+r-1,,0),i,INDEX(u,MOD(s,r-1)+1),INDEX(SORT(IFERROR(INDEX(A2:B15,s+1,{1,2}),CHOOSE({1,2},"",i)),2),,1))

verbatim into E2 and I get the Excel pop-up that says "there's a problem with the formula"

 

[Fluff]

Try copying the mini-sheet into a blank sheet using the copy icon

 

[RodneyC]

When I do that, it populates the columns A and B but for column E it only populates E2 and populates it with what appears to be a text version of the formula. I copy/pasted "Paste special" as "formula" and it's still showing up as text.

 

[Fluff]

Do you normally use commas as the separator in formulae?

 

[RodneyC]

I don't have a "normal" I usually refer to something similar I've done before and copy/paste then modify for what I'm trying to do. If it's something new, I reach out here.

 

[Fluff]

don't have a "normal"

Yes you do. If you type =SUM( into a cell, look at the tool tip, what does it show separating the arguments?