I have a list of journal entries on one side and I have a list accounts associated with each entry on the other. Is there a way to use the join tool to combine the accounts of each entry? Ex: List 1 JE00011100: RevenueJE00011200: CashJE00014000: Accounts ReceivableJE0005 XXJE0005XX List 2 JE0001JE0002JE0003 Desired End Result JE0001 1100: Revenue, 1200: Cash, 4000: Accounts Receivable

How to join data and have multiple results?

I have a list of journal entries on one side and I have a list accounts associated with each entry on the other. Is there a way to use the join tool to combine the accounts of each entry?

Ex:

List 1

JE0001	1100: Revenue
JE0001	1200: Cash
JE0001	4000: Accounts Receivable
JE0005	XX
JE0005	XX

List 2

JE0001

JE0002

JE0003

Desired End Result

JE0001

1100: Revenue, 1200: Cash, 4000: Accounts Receivable

Help

Accepted answers

JohnJPS

Understood, this can be done by adding a generic field label and using that as the Field Header in the Cross-Tab. Please see the attached for the actual workflow.

joinMultiConcat.yxmd

All comments

JohnJPS

Hi @Inactive User,

Do the join as usual, on the first column. The middle output will have three rows, all for JE001. Then do a Cross Tab to concatenate the multiple rows into a single row. (See attachment for this done on your sample data).

Hope that helps!

John

joinMultiConcat.yxmd

unknown

Thanks for the reply!

I used your advice but the crosstab output something along the lines of the following...

JE Number	JE002	JE003	JE004	JE005
JE002	Revenue
JE003		Revenue
JE004			Revenue, Accounts Receivable
JE005				Accounts Receivable

Is there a way to only have one column looking something like...

JE Number	Accounts
JE001	Revenue
JE002	Revenue
JE003	Revenue, Accounts Receivable
JE004	Accounts Receivable

echuong

You can also achieve this by just using the summarize tool to streamline things. Just do a group by for the JE number and a concatenate for the description. You can change the default seperator to a comma with space (from a comma) for visual clarity.

New Workflow1.yxmd

JohnJPS

@echuong is absolutely spot on - recommend adding his solution to "Accepted."

- John

joinMultiConcat.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703