generate table based on values from another table

[ajith]

Hello all,
Pls help me to generate the table of orders of a particular item using excel formula based on the data available in another table (A or B). The items in the table generated (C,D,E or F) along with its country should get filled without any empty rows. In the attached example table A and B are data table and C, D, E and F are the tables for the result. The result table should be in such a way that whenever we change the item ordered item the result table gets generated without any interference. Thanks in advance.

Order.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1								C				D
2				orders					Apple				Orange
3		A							January	Orders			January	Orders
4			Country	1st week	2nd week	3rd week			Country	1st week			Country	2nd week
5			US	Apple	Orange	Jack
6			England	Apple	Grapes	Apple
7		January	Mexico	Mango	Apple	Grapes
8			Spain	Apple	Grapes	Plum
9			Japan	Orange	Orange	Mango
10			Norway	Orange	Apple	Grapes
11			Sweden	Apple	Orange	Plum
12								E				F
13		B							Mango				Plum
14			Spain	Apple	Orange	Plum			February	Orders			February	Orders
15			Sweden	Mango	Apple	Orange			Country	1st week			Country	3rd week
16			Mexico	Orange	Apple	Mango
17		February	Norway	Apple	Grapes	Jack
18			Japan	Orange	Apple	Jack
19			England	Mango	Orange	Apple
20			US	Mango	Grapes	Plum
21
22
23
Sheet1

 

[wjhladik]

The solution below uses some unpivot code I had so that I could filter the unpivoted data based on your two inputs of fruit and week. Also, you need to repeat the column headers in table B for this to work.

xxx.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								C				D
2				orders					Apple				Orange
3		A							January	Orders			January	Orders
4			Country	1st week	2nd week	3rd week			Country	1st week			Country	2nd week
5			US	Apple	Orange	Jack			US				US
6			England	Apple	Grapes	Apple			England				Japan
7		January	Mexico	Mango	Apple	Grapes			Spain				Sweden
8			Spain	Apple	Grapes	Plum			Sweden
9			Japan	Orange	Orange	Mango
10			Norway	Orange	Apple	Grapes
11			Sweden	Apple	Orange	Plum
12								E				F
13		B	Country	1st week	2nd week	3rd week			Mango				Plum
14			Spain	Apple	Orange	Plum			February	Orders			February	Orders
15			Sweden	Mango	Apple	Orange			Country	1st week			Country	3rd week
16			Mexico	Orange	Apple	Mango			Sweden				Spain
17		February	Norway	Apple	Grapes	Jack			England				US
18			Japan	Orange	Apple	Jack			US
19			England	Mango	Orange	Apple
20			US	Mango	Grapes	Plum
Sheet2

Cell Formulas
Range	Formula
I5:I8,M5:M7	I5	=LET(range,$C$4:$F$11, fruit,I2, week,J4, r,ROWS(range)-1, c,COLUMNS(range)-1, rowheads,OFFSET(range,1,0,r,1), colheads,OFFSET(range,0,1,1,c), data,OFFSET(range,1,1,r,c), tot,SEQUENCE(r*c), firstcol,INDEX(rowheads,IF(MOD(tot,r)=0,r,MOD(tot,r)),SEQUENCE(,COLUMNS(rowheads))), secondcol,INDEX(colheads,1,ROUNDUP(tot/r,0)), thirdcol,INDEX(data,IF(MOD(tot,r)=0,r,MOD(tot,r)),ROUNDUP(tot/r,0)), ss,SEQUENCE((r*c)+1,,0,1), result,CHOOSE({1,2,3},IF(ss=0,"ROW",INDEX(firstcol,ss,1)),IF(ss=0,"COL",INDEX(secondcol,ss,1)),IF(ss=0,"DATA",INDEX(thirdcol,ss,1))), TAKE(FILTER(result,(CHOOSECOLS(result,3)=fruit)*(CHOOSECOLS(result,2)=week),"None"),,1))
I16:I18,M16:M17	I16	=LET(range,$C$13:$F$20, fruit,I13, week,J15, r,ROWS(range)-1, c,COLUMNS(range)-1, rowheads,OFFSET(range,1,0,r,1), colheads,OFFSET(range,0,1,1,c), data,OFFSET(range,1,1,r,c), tot,SEQUENCE(r*c), firstcol,INDEX(rowheads,IF(MOD(tot,r)=0,r,MOD(tot,r)),SEQUENCE(,COLUMNS(rowheads))), secondcol,INDEX(colheads,1,ROUNDUP(tot/r,0)), thirdcol,INDEX(data,IF(MOD(tot,r)=0,r,MOD(tot,r)),ROUNDUP(tot/r,0)), ss,SEQUENCE((r*c)+1,,0,1), result,CHOOSE({1,2,3},IF(ss=0,"ROW",INDEX(firstcol,ss,1)),IF(ss=0,"COL",INDEX(secondcol,ss,1)),IF(ss=0,"DATA",INDEX(thirdcol,ss,1))), TAKE(FILTER(result,(CHOOSECOLS(result,3)=fruit)*(CHOOSECOLS(result,2)=week),"None"),,1))
Dynamic array formulas.

 

[Fluff]

@wjhladik The OP's profile shows 2016 & so does not have a lot of those functions.

 

[wjhladik]

@wjhladik The OP's profile shows 2016 & so does not have a lot of those functions.

Sorry, didn't notice that. I'm of the mindset that anyone can use excel online (for free basically) and get access to most 365 level formulas. Just depends if their situation warrants it... e.g. business allows and simple upload of data does the trick. Oh well, maybe others can benefit from the embedded unpivot formula.

 

[Fluff]

I'm of the mindset that anyone can use excel online (for free basically)

That may be so, but please post solutions that will work for the OP's version(if listed).