Join two columns with one primary key including null values

[syt]

Hi

I have one table read from Excel:

Summary	Included Summaries
A	B
A	C
A	D
B	E

and another table:

Summary	Included IDs
A	1
B	2
B	3

How can I join these two tables like the following

Summary	Included Summaries	Included IDs
A	B	1
	C
	D
B	E	2
		3

I have tried join and multiply join, but they don't work. Their output is like:

Summary	Included Summaries	Included IDs
A	B	1
A	C	1
A	D	1
B	E	2
B	E	3

[the_jake_tool]

Hey @syt 

I'm sure there are many ways to do this! I tend to think in Macros! Therefore, although its not the most simple, I've built you a macro that might help your issue.

I've seen a lot of things like this with formatting for importing into third party softwares.

Let me know if it helps or if you need any clarification...also please like and accept to confirm knowledge sharing if it helps you in the right direction 🙂

Sincerely,

J

[Thableaus]

Hi @syt 

Not the easiest solution, probably there are better than mine, but here we go

- Do a regular join

- Add a Record ID to the dataset

- Transpose the colums grouping by RecordID

- Sort your dataset in a descending way starting with the column "Value". This will be useful to wipe out the repeated values using the Multi Row Formula Tool. 

- Use the Multi Row Formula Tool with the following condition:

IF [Row+1:Value] = [Value] THEN ""
ELSE [Value] ENDIF

That's the best way I found to get rid of these repeated values that come after.

- Use the CrossTab Tool to put your columns back the way they were.

- Use the Select Tool to order the columns. Rename them if you find necessary.

Workflow attached.

Cheers,

[the_jake_tool]

@Thableaus 

I think this is a great challenge - maybe even weekly challenge material for intermediate level participants!

Wouldn't you agree?

[Thableaus]

I completely agree with you @the_jake_tool!

 That's a great suggestion. I'd love to see different solutions for this problem - which is something I can see many Excel users looking for.

[the_jake_tool]

I've worked with a lot of teams who have to get their data into weird tabular format to be exported into excel in order to be used as an import into a third party software e.g., Thomson Reuters, etc. Soon they will see the light 🙂

J

[syt]

Hi @Thableaus 

Thank you for your reply. But I still have one question. If I have the input data like that

Summary	Included Summaries
A	B
A	C
A	D
B	E

and

Summary	Included IDs
A	1
A	4
B	2
B	3

The output is 

Summary	Included Summaries	Included IDs
A	B	1
	C
	D
		4
B	E	2
		3

I don't know why there are so many empty fields which is not needed. Can you please take a look at this? Thank you so much.

Workflow attached

Best Regards