Data sorting with multiple criteria

Question

I have data like this. I have shown 20 items only, but in actual they are more than 4000. Each item may fall in one or multiple groups.

I want a data by which Group1,2,3,4,5,6 are kept in one cell through data validation-List. So if I select Group1, I should get list of items related to Group1 as below (as answer)

Group1
Item1
Item4
Item6
Item8
Item12
Item15
Item20

Similarly, if I change to Group2, the asnwers should be accordingly. Number of Groups can be increased also with time.

ITEM	Group1	Group2	Group3	Group4	Group5	Group6
Item1	Item1		Item1	Item1
Item2		Item2	Item2			Item2
Item3		Item3		Item3	Item3
Item4	Item4	Item4				Item4
Item5		Item5	Item5		Item5
Item6	Item6			Item6	Item6	Item6
Item7			Item7	Item7		Item7
Item8	Item8	Item8
Item9			Item9	Item9	Item9
Item10		Item10			Item10	Item10
Item11			Item11
Item12	Item12	Item12	Item12
Item13		Item13		Item13	Item13
Item14					Item14	Item14
Item15	Item15		Item15	Item15		Item15
Item16		Item16			Item16	Item16
Item17		Item17		Item17	Item17
Item18					Item18	Item18
Item19			Item19	Item19		Item19
Item20	Item20	Item20		Item20

Will be thankful if some soltution for this in Excel 2019

Answer 1

Hi,

See the printscreen above and try this formula:

=FILTER(INDEX(B2:G21,,MATCH(I2,B1:G1,0)),INDEX(B2:G21,,MATCH(I2,B1:G1,0))<>"")

If you do not use a Microsoft 365 then use the following formula.

=IFERROR(INDEX(INDEX($B$2:$G$21,,MATCH($I$2,$B$1:$G$1,0)),AGGREGATE(15,6,(ROW($B$2:$G$21)-1)/(INDEX($B$2:$G$21,,MATCH($I$2,$B$1:$G$1,0))<>""),ROW(K1))),"")

Hope this helps.

Answer 2

Hi,

Enter this formula in cell M2 and drag down

=IFERROR(INDEX(INDIRECT($I$2),SMALL(IF(INDIRECT($I$2)<>0,ROW($A$1:$A$20)),ROW(1:1)),1),"")

Hope this helps.

Answer 3

Hi,

In the first table of your original post, you have shown only one Group. Please show 3 groups - I'd like to see your data structure/layout. Share data in a format that can be pasted in an MS Excel file.

Answer 4

Sir. the only solution needed is when I select a group, I should get corresponding items in first column of new sheet without gaps.

So through data validation I change the group, the items should change accordingly related to new selected geoup

It's like we have copied a column of desired group, paste in new sheet and then remove blanks.

Instead of repeating the name of items in all columns, it is possible for me to have source data with first column of item names fixed and in other columns mark with 0 or 1 as per group.

Anyway solution is OK to me. Hope it is clear

Answer 5

Hi,

Your expected result is not clear. While you say that you want to create a validation list, your desired result is a table. Please clarify.