Does anyone know this???

[nickkim88]

Excel GODs please help!

Table A and Table B has the same information.

Are there anyway you can fill in column "G" and "I" with formula?

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	Table A						Table B
2	Item Number	Total	Warehouse A	Warehouse B	Warehouse C		Warehouse	Item Number	Qty
3	ABC1	6	1	2	3		Warehouse A	ABC1	1
4	ABC2	24	7	8	9		Warehouse A	ABC2	7
5	ABC3	42	13	14	15		Warehouse A	ABC3	13
6	ABC4	60	19	20	21		Warehouse A	ABC4	19
7	ABC5	6	1	2	3		Warehouse A	ABC5	1
8	ABC6	24	7	8	9		Warehouse A	ABC6	7
9	ABC7	42	13	14	15		Warehouse A	ABC7	13
10	ABC8	60	19	20	21		Warehouse A	ABC8	19
11	ABC9	6	1	2	3		Warehouse A	ABC9	1
12	ABC10	24	7	8	9		Warehouse A	ABC10	7
13							Warehouse B	ABC1	2
14							Warehouse B	ABC2	8
15							Warehouse B	ABC3	14
16							Warehouse B	ABC4	20
17							Warehouse B	ABC5	2
18							Warehouse B	ABC6	8
19							Warehouse B	ABC7	14
20							Warehouse B	ABC8	20
21							Warehouse B	ABC9	2
22							Warehouse B	ABC10	8
23							Warehouse C	ABC1	3
24							Warehouse C	ABC2	9
25							Warehouse C	ABC3	15
26							Warehouse C	ABC4	21
27							Warehouse C	ABC5	3
28							Warehouse C	ABC6	9
29							Warehouse C	ABC7	15
30							Warehouse C	ABC8	21
31							Warehouse C	ABC9	3
32							Warehouse C	ABC10	9
33
34
Sheet6

Cell Formulas
Range	Formula
G3:I32	G3	=LET(I,A3:A12,h,C2:E2,w,C3:E12,r,ROWS(I),c,COLUMNS(w),s,SEQUENCE(r*c,,0),o_1,INDEX(h,INT(s/r)+1),o_2,INDEX(I,MOD(s,r)+1),o_3,INDEX(w,MOD(s,r)+1,INT(s/r)+1),CHOOSE({1,2,3},o_1,o_2,o_3))
B3:B12	B3	=SUM(C3:E3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G:I	Expression	=G1048576<>""	text	NO

 

[DanteAmor]

Another option:

=LET(a,A3:A7,c,C3:C7,d,D3:D7,e,E3:E7,f,COUNTA(a),VSTACK(HSTACK(TEXTSPLIT(REPT(C2&",",f),,",",1),a,c),HSTACK(TEXTSPLIT(REPT(D2&",",f),,",",1),a,d),HSTACK(TEXTSPLIT(REPT(E2&",",f),,",",1),a,e)))

 

[Fluff]

Another option

=HSTACK(TOCOL(IF(SEQUENCE(ROWS(A3:A12)),C2:E2),,1),TOCOL(IF(SEQUENCE(,COLUMNS(C2:E2)),A3:A12),,1),TOCOL(C3:E12,1,1))