Hello Everyone,
I have 2 sets of information: "Custom" and "Base". What I need is i need to join them and look for every "Row" that is listed in the "Custom" set and bring the corresponding value in the "Base" set, along with the corresponding accounts under each. See below
Custom
Row | Description | Custom Dataspec |
76750 | A. FOREIGN DIVIDENDS | INPUT |
8207065 1 STATE ADJ FOR SEC 965 INCOME W | ||
6044 -1 FOREIGN CORPORATIONS-OTHER E | ||
6045 -1 FOREIGN CORPS.-SUBPART F(CONTROL E | ||
8ID2406 1 FOREIGN DIVIDENDS (WATER'S EDGE) E | ||
85218 | TOTAL INCOME UNDER 965(A) | INPUT |
8207065 1 STATE ADJ FOR SEC 965 INCOME W | ||
86160 | ROYALTY INCOME | INPUT |
Base
Row | Description | Base Dataspec |
76750 | FOREIGN DIVIDENDS | INPUT |
6043090 -1 FOREIGN CORPS.-100% SEC 245B W | ||
6043090 -1 FOREIGN CORPS.-100% SEC 245B E | ||
6043190 -1 FOR CORPS - 100% SEC 245 SUB TO W | ||
6043190 -1 FOR CORPS - 100% SEC 245 SUB TO E | ||
6043RT1 -1 FRGN CORP-100% SEC 245B-REPORT T W | ||
6043RT1 -1 FRGN CORP-100% SEC 245B-REPORT T E | ||
6042050 -1 FOREIGN CORPORATIONS - 65% W | ||
6042050 -1 FOREIGN CORPORATIONS - 65% E | ||
6042150 -1 FOR CORPS - 65% SUB TO 832 W | ||
6042150 -1 FOR CORPS - 65% SUB TO 832 E | ||
6042RT2 -1 MORE THAN 20% OWNED CFC-REPORT T W | ||
6042RT2 -1 MORE THAN 20% OWNED CFC-REPORT T E | ||
6042051 -1 FOREIGN CORPORATIONS - 50% W | ||
6042051 -1 FOREIGN CORPORATIONS - 50% E | ||
6042151 -1 FOR CORPS - 50% - SUB TO 832 W | ||
6042151 -1 FOR CORPS - 50% - SUB TO 832 E | ||
6042RT1 -1 LESS THAN 20% OWNED CFC-REPORT T W | ||
6042RT1 -1 LESS THAN 20% OWNED CFC-REPORT T E | ||
6045 -1 FOREIGN CORPS.-SUBPART F(CONTROL W | ||
6044 -1 FOREIGN CORPORATIONS-OTHER W | ||
6044 -1 FOREIGN CORPORATIONS-OTHER E | ||
85218 | ADJ TO TCJA ADDITIONS TO FTI | INPUT |
8207065 1 STATE ADJ FOR SEC 965 INCOME W | ||
8207025 1 SEC 163(J) C/F INT EXP STATE ADJ W | ||
8207068 1 ADJ TO TCJA ADDITIONS TO FTI W | ||
86160 | ROYALTY INCOME | INPUT |
7304 1 ROYALTY INCOME E | ||
7315172 -1 ROYALTY INCOME EXCL-E W |
The end result I need is listing each row and show all the corresponding values under the "Custom Dataspec" and "Base Dataspec". The problem is that, as you can see, sometimes, the "Base Dataspec" contains more values than the "Custom Dataspec" and i Dont get the full list of each. The end result i need is:
Row | Description | Custom Dataspec | Base Dataspec |
76750 | A. FOREIGN DIVIDENDS | INPUT | INPUT |
8207065 1 STATE ADJ FOR SEC 965 INCOME W | 6043090 -1 FOREIGN CORPS.-100% SEC 245B W | ||
6044 -1 FOREIGN CORPORATIONS-OTHER E | 6043090 -1 FOREIGN CORPS.-100% SEC 245B E | ||
6045 -1 FOREIGN CORPS.-SUBPART F(CONTROL E | 6043190 -1 FOR CORPS - 100% SEC 245 SUB TO W | ||
8ID2406 1 FOREIGN DIVIDENDS (WATER'S EDGE) E | 6043190 -1 FOR CORPS - 100% SEC 245 SUB TO E | ||
6043RT1 -1 FRGN CORP-100% SEC 245B-REPORT T W | |||
6043RT1 -1 FRGN CORP-100% SEC 245B-REPORT T E | |||
6042050 -1 FOREIGN CORPORATIONS - 65% W | |||
6042050 -1 FOREIGN CORPORATIONS - 65% E | |||
6042150 -1 FOR CORPS - 65% SUB TO 832 W | |||
6042150 -1 FOR CORPS - 65% SUB TO 832 E | |||
6042RT2 -1 MORE THAN 20% OWNED CFC-REPORT T W | |||
6042RT2 -1 MORE THAN 20% OWNED CFC-REPORT T E | |||
6042051 -1 FOREIGN CORPORATIONS - 50% W | |||
6042051 -1 FOREIGN CORPORATIONS - 50% E | |||
6042151 -1 FOR CORPS - 50% - SUB TO 832 W | |||
6042151 -1 FOR CORPS - 50% - SUB TO 832 E | |||
6042RT1 -1 LESS THAN 20% OWNED CFC-REPORT T W | |||
6042RT1 -1 LESS THAN 20% OWNED CFC-REPORT T E | |||
6045 -1 FOREIGN CORPS.-SUBPART F(CONTROL W | |||
6044 -1 FOREIGN CORPORATIONS-OTHER W | |||
6044 -1 FOREIGN CORPORATIONS-OTHER E | |||
85218 | TOTAL INCOME UNDER 965(A) | INPUT | INPUT |
8207065 1 STATE ADJ FOR SEC 965 INCOME W | 8207065 1 STATE ADJ FOR SEC 965 INCOME W | ||
8207025 1 SEC 163(J) C/F INT EXP STATE ADJ W | |||
8207068 1 ADJ TO TCJA ADDITIONS TO FTI W | |||
86160 | ROYALTY INCOME | INPUT | INPUT |
7304 1 ROYALTY INCOME E | |||
7315172 -1 ROYALTY INCOME EXCL-E W |
Please note that these are part of a larger list
Any ideas?
Solved! Go to Solution.
Here's one solution that leverages the multi-row formula and the tile tools. Some extra steps at the end to remove the duplicate [Row] field, but if that isn't required it's a much shorter workflow. Let me know if this helps.
jm
Hi @malcorr
I built a batch macro to perform this operation. It could be streamlined further. Hope it helps
Hello,
Thanks for your reply, I'm not able to open the second file since it says I dont have the latest version.
I can open the first one, but given the fact that the inputs are saved in your desktop i cannot run it. Could you please send them seprately?
Thanks!
@malcorr- I've added the excel data input file now. See if you can access
Thank You,
Te excel Worksheet you added is the first input in your workflow? In that case, what do i do with the second function, the Dynamic Input??
thanks!
Thanks!
How do you configure the Macro??
The configuration for the dynamic input is below. In the Input Data Source Template field, you'll want to point to the excel file and choose one of the sheets. Also be sure to select "Full Path" in the Output File Name as Field section of the Options.
Then you would copy one of the values into the Filter tool so that the workflow splits into the 2 datasources that you're trying to join.
I tried and configured exactly as indicated, but seems im not getting the same result as you
Seems like the filter is not working properly. I have this configuration. On the "False" Side of the filter, Im getting the "Base" numbers, but I'm not getting anything on the "True" Side of the filter