Adding Rows When Criteria is met/Match

[NicoleHou]

I am trying to insert key fields where a criteria/field matches:

Source #1

A	B	C	D	E
			Year To Date	Year To Date
			August 2022	August 2022
Account		Account Description	C123P4 - XY Corp.	C345P6 - XYZ Investment LLC
44123	TOTAL	Acct 1	12345.66	22345.66
44124	TOTAL	Acct 2	2345.68	32345.68
44125	TOTAL	Acct 3	564.88	1564.88

F

Year To Date

August 2022

C445P9 - BB LP

32345.66

332345.68

31564.88

Source # 2

ID	Co	Area	Area Number	Check
C123P4 - XY Corp.	123P4	Colby	P4	x
C345P6 - XYZ Investment LLC	345P6	Moore	P6	y
C445P9	445P9	Ralph	P9	x

Desired Outcome 

			x	y
			123P4	345P6
			P4	P6
			Colby	Moore
			Year To Date	Year To Date
			August 2022	August 2022
Account		Account Description	C123P4 - XY Corp.	C345P6 - XYZ Investment LLC
44123	TOTAL	Acct 1	12345.66	22345.66
44124	TOTAL	Acct 2	2345.68	32345.68
44125	TOTAL	Acct 3	564.88	1564.88

x

445P9

P9

Ralph

Year To Date

August 2022

C445P9- BB LP

32345.66

332345.68

31564.88

Any guidance is appreciated 

Thank you

[michelle_mathews]

@NicoleHou , can you elaborate on the logic and criteria used for matching the two datasets?

[NicoleHou]

My apologies, I left out information on accident.  Need to match based on row 4 in Source #1 (Columns D,E,F) and the ID column from Source #2

Source #1

A	B	C	D	E	F
			Year To Date	Year To Date	Year To Date
			August 2022	August 2022	August 2022
Account		Account Description	C123P4 - XY Corp.	C345P6 - XYZ Investment LLC	C445P9 - BB LP
44123	TOTAL	Acct 1	12345.66	22345.66	32345.66
44124	TOTAL	Acct 2	2345.68	32345.68	332345.68
44125	TOTAL	Acct 3	564.88	1564.88	31564.88

Source #2 

ID	Co	Area	Area Number	Check
C123P4 - XY Corp.	123P4	Colby	P4	x
C345P6 - XYZ Investment LLC	345P6	Moore	P6	y
C445P9 - BB LP	445P9	Ralph	P9	x

Desired Output

			x	y	x
			123P4	345P6	445P9
			P4	P6	P9
			Colby	Moore	Ralph
			Year To Date	Year To Date	Year To Date
			August 2022	August 2022	August 2022
Account		Account Description	C123P4 - XY Corp.	C345P6 - XYZ Investment LLC	C445P9 - BB LP
44123	TOTAL	Acct 1	12345.66	22345.66	32345.66
44124	TOTAL	Acct 2	2345.68	32345.68	332345.68
44125	TOTAL	Acct 3	564.88	1564.88	31564.88

[Cicada_lies_heavy · Accepted solution]

Hi!

I approached it by transposing the second part of the header, joining it with Source #2, and retransposing the result. (I wished Alteryx had a one-tool transpose option....)

See the attached workflow for the intricacies (such as renaming the fields before retransposing in order to ensure the desired order of the header)

[Emmanuel_G · Accepted solution]

@NicoleHou 

One way to achieve to this result.

[NicoleHou]

Thank you!

[NicoleHou]

Thank you, this worked as well!

[NicoleHou]

@Emmanuel_G  thank you, this worked with fewer steps, but how do I get the "make columns" tool?

Thanks!

Edited to say, I found this in the Laboratory menu! Thanks again!

[Emmanuel_G]

@NicoleHou  Good to know ! 🙂

And yes, it is in the Laboratory menu ! Hope you liked this tool.

[NicoleHou]

@Cicada_lies_heavy   Thank you very much, the detail in your response helped me maintain all of the information correctly, thank you very much!