Convert Data from multiple Rows to one Row with corresponding Value in Columns

[nguyentuson]

Hi everyone !

I'm writting an excel spreadsheet by VBA code for automatic Combinate of Loads in structure analysis job
and i do not know by the way which code for convert data from Multiple Rows in to One Corresponding Row (but data must be paste in corresponding Column in that Row)


Detail descripble like this
I have many Load Patterns in col L (it's sorted dymically by my code and transpoted to N1--->till the last Pattern)
then which corresponding Load Combination Name in Col M, ex 3-LRFD-ComB2_1 i have to lookup from Col A and recognize that it has 6 load participate Load Pattern located in col C (DEAD1, NX_DEAD1, FL-L1, NX_FL-L1, RL1 & NX_RL1)
i must copy Load Factor(1.2 1.2 0.75 0.75 0.5 0.5 ) located in col B and past again to Row 4 which 3-LRFD-ComB2_1 , but Load Factor was pasted to corresponding Load Pattern name in col N,O, Q,R,S,T )
and which cells was not relate to 3-LRFD-ComB2_1, the Load Factor =0.







Many thanks !

 

[Kerryx]

Can you give us some data to work withusing the XL2bb tool , but power query will do the job and i know some of the people on here have previously posted instructions on this, did you try a search on the board

 

[nguyentuson]

i work on procedure about XL2BB tool, but i can not paste the value to the board.

 

[Kerryx]

Select what youo want to copy and just paste in what it copies

 

[nguyentuson]

i've copied, it say everything is ok, but when i click paste to the board , nothing were pasted

 

[nguyentuson]

would you mind if copy my data to your spreadsheet and test it, many appreciate !


Name Factor Patterns DEAD1 NX_DEAD1 NY_DEAD1 FL-L1 NX_FL-L1 RL1 NX_RL1 NY_FL-L1 NY_RL1 RL2
1-LRFD-ComB1_1 1.4 DEAD1 DEAD1 1-LRFD-ComB1_1 1.4 1.4 0 0 0 0 0 0 0 0
1-LRFD-ComB1_1 1.4 NX_DEAD1 NX_DEAD1 2-LRFD-ComB1_2 1.4 0 1.4 0 0 0 0 0 0 0
2-LRFD-ComB1_2 1.4 DEAD1 NY_DEAD1 3-LRFD-ComB2_1 1.2 1.2 0 0.75 0.75 0.5 0.5 0 0 0
2-LRFD-ComB1_2 1.4 NY_DEAD1 FL-L1 4-LRFD-ComB2_2
3-LRFD-ComB2_1 1.2 DEAD1 NX_FL-L1 5-LRFD-ComB2_3
3-LRFD-ComB2_1 1.2 NX_DEAD1 RL1 6-LRFD-ComB2_4
3-LRFD-ComB2_1 0.75 FL-L1 NX_RL1 7-LRFD-ComB2_5
3-LRFD-ComB2_1 0.75 NX_FL-L1 NY_FL-L1 8-LRFD-ComB2_6
3-LRFD-ComB2_1 0.5 RL1 NY_RL1 9-LRFD-ComB2_7
3-LRFD-ComB2_1 0.5 NX_RL1 RL2 10-LRFD-ComB2_8
4-LRFD-ComB2_2 1.2 DEAD1 NX_RL2 11-LRFD-ComB2_9
4-LRFD-ComB2_2 1.2 NY_DEAD1 NY_RL2 12-LRFD-ComB2_10
4-LRFD-ComB2_2 0.75 FL-L1 CRane1 13-LRFD-ComB2_11
4-LRFD-ComB2_2 0.75 NY_FL-L1 NX_CRane1 14-LRFD-ComB2_12
4-LRFD-ComB2_2 0.5 RL1 NY_CRane1 15-LRFD-ComB3a_1
4-LRFD-ComB2_2 0.5 NY_RL1 W1 16-LRFD-ComB3a_2
5-LRFD-ComB2_3 1.2 DEAD1 W2 17-LRFD-ComB3a_3
5-LRFD-ComB2_3 1.2 NX_DEAD1 EQX 18-LRFD-ComB3a_4
5-LRFD-ComB2_3 0.75 FL-L1 19-LRFD-ComB3a_5
5-LRFD-ComB2_3 0.75 NX_FL-L1 20-LRFD-ComB3a_6
5-LRFD-ComB2_3 0.5 RL2 21-LRFD-ComB3a_7

 

[Kerryx]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Factor	Patterns			DEAD1	NX_DEAD1	NY_DEAD1	FL-L1	NX_FL-L1	RL1	NX_RL1	NY_FL-L1	NY_RL1	RL2
2	1-LRFD-ComB1_1	1.4	DEAD1	DEAD1	1-LRFD-ComB1_1	1.4	1.4	0	0	0	0	0	0	0	0
3	1-LRFD-ComB1_1	1.4	NX_DEAD1	NX_DEAD1	2-LRFD-ComB1_2	1.4	0	1.4	0	0	0	0	0	0	0
4	2-LRFD-ComB1_2	1.4	DEAD1	NY_DEAD1	3-LRFD-ComB2_1	1.2	1.2	0	0.75	0.75	0.5	0.5	0	0	0
5	2-LRFD-ComB1_2	1.4	NY_DEAD1	FL-L1	4-LRFD-ComB2_2
6	3-LRFD-ComB2_1	1.2	DEAD1	NX_FL-L1	5-LRFD-ComB2_3
7	3-LRFD-ComB2_1	1.2	NX_DEAD1	RL1	6-LRFD-ComB2_4
8	3-LRFD-ComB2_1	0.75	FL-L1	NX_RL1	7-LRFD-ComB2_5
9	3-LRFD-ComB2_1	0.75	NX_FL-L1	NY_FL-L1	8-LRFD-ComB2_6
10	3-LRFD-ComB2_1	0.5	RL1	NY_RL1	9-LRFD-ComB2_7
11	3-LRFD-ComB2_1	0.5	NX_RL1	RL2	10-LRFD-ComB2_8
12	4-LRFD-ComB2_2	1.2	DEAD1	NX_RL2	11-LRFD-ComB2_9
13	4-LRFD-ComB2_2	1.2	NY_DEAD1	NY_RL2	12-LRFD-ComB2_10
14	4-LRFD-ComB2_2	0.75	FL-L1	CRane1	13-LRFD-ComB2_11
15	4-LRFD-ComB2_2	0.75	NY_FL-L1	NX_CRane1	14-LRFD-ComB2_12
16	4-LRFD-ComB2_2	0.5	RL1	NY_CRane1	15-LRFD-ComB3a_1
17	4-LRFD-ComB2_2	0.5	NY_RL1	W1	16-LRFD-ComB3a_2
18	5-LRFD-ComB2_3	1.2	DEAD1	W2	17-LRFD-ComB3a_3
19	5-LRFD-ComB2_3	1.2	NX_DEAD1	EQX	18-LRFD-ComB3a_4
20	5-LRFD-ComB2_3	0.75	FL-L1		19-LRFD-ComB3a_5
21	5-LRFD-ComB2_3	0.75	NX_FL-L1		20-LRFD-ComB3a_6
22	5-LRFD-ComB2_3	0.5	RL2		21-LRFD-ComB3a_7
Sheet2

 

[Kerryx]

Power Query solution

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Factor	Patterns		Name	DEAD1	NX_DEAD1	NY_DEAD1	FL-L1	NX_FL-L1	RL1	NX_RL1	NY_FL-L1	NY_RL1	RL2
2	1-LRFD-ComB1_1	1.4	DEAD1		1-LRFD-ComB1_1	1.4	1.4	0	0	0	0	0	0	0	0
3	1-LRFD-ComB1_1	1.4	NX_DEAD1		2-LRFD-ComB1_2	1.4	0	1.4	0	0	0	0	0	0	0
4	2-LRFD-ComB1_2	1.4	DEAD1		3-LRFD-ComB2_1	1.2	1.2	0	0.75	0.75	0.5	0.5	0	0	0
5	2-LRFD-ComB1_2	1.4	NY_DEAD1		4-LRFD-ComB2_2	1.2	0	1.2	0.75	0	0.5	0	0.75	0.5	0
6	3-LRFD-ComB2_1	1.2	DEAD1		5-LRFD-ComB2_3	1.2	1.2	0	0.75	0.75	0	0	0	0	0.5
7	3-LRFD-ComB2_1	1.2	NX_DEAD1
8	3-LRFD-ComB2_1	0.75	FL-L1
9	3-LRFD-ComB2_1	0.75	NX_FL-L1
10	3-LRFD-ComB2_1	0.5	RL1
11	3-LRFD-ComB2_1	0.5	NX_RL1
12	4-LRFD-ComB2_2	1.2	DEAD1
13	4-LRFD-ComB2_2	1.2	NY_DEAD1
14	4-LRFD-ComB2_2	0.75	FL-L1
15	4-LRFD-ComB2_2	0.75	NY_FL-L1
16	4-LRFD-ComB2_2	0.5	RL1
17	4-LRFD-ComB2_2	0.5	NY_RL1
18	5-LRFD-ComB2_3	1.2	DEAD1
19	5-LRFD-ComB2_3	1.2	NX_DEAD1
20	5-LRFD-ComB2_3	0.75	FL-L1
21	5-LRFD-ComB2_3	0.75	NX_FL-L1
22	5-LRFD-ComB2_3	0.5	RL2
Sheet2

Here is the code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Factor", type number}, {"Patterns", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Patterns]), "Patterns", "Factor", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"DEAD1", "NX_DEAD1", "NY_DEAD1", "FL-L1", "NX_FL-L1", "RL1", "NX_RL1", "NY_FL-L1", "NY_RL1", "RL2"})
in
    #"Replaced Value"

 

[nguyentuson]

Appreciate with your solution!

Would you mind sharing me your sheet with solution, i do not know how to use your code ( i'm a beginner in VBA , i have just write code by my selft for one week)

 

[Kerryx]

Here are some good links from @alansidman on how to set up and use PQ

How to use Power Query