How to get the column names into my formula

[wonka1234]

Hi,

This is my output:

Team	City	Deposits	Right_Team	Right_City	Right_Deposits	Result
Raptors	Montreal	5000000	Raptors	Montreal	5000000	Matched
Canadians	Montreal	100000				Data 1 Unmatched in Data 2
Leafs	Toronto	200000				Data 1 Unmatched in Data 2
Raptors	Toronto	100000				Data 1 Unmatched in Data 2
			Raptors	Toronto	10	Data 2 Unmatched in Data 1
			Leafs	Toronto	2300000	Data 2 Unmatched in Data 1
			Canadians	Montreal	1500000	Data 2 Unmatched in Data 1

As you can see it compares like columns.

However in my rsult formula, is it possible to switch it from "Data 1 Unmatched in Data 2" to something like

Row 2 result would be - Data 1 unmatched in Data 2: Team, City, Deposits.  

COuld this be done?

[apathetichell]

convert to string. add a record id. transpose - use recordid as a key field. use a formula tool to extract if [Name] contains "Right" or not - call it "source" (or something). group by recordid/source/concatenate [Value] with no delimiter. join on recordid. compare if the two concatenated values are equal. if they are  "Matched" if one source is empty - then you can build your logic etc... join this new field back to your original data on recordid.

[apathetichell]

If you want to add where specifically the [Name] field doesn't match you'd do the same kind of transpose. You'd remove the "Right_" suffix from the data2 fieldnames. Then you'd summarize group by record id/name and count distinct values in value. filter where the distinct value is not 1 and then concatenate the name field by record id - this would tell you which fields in each records don't match.

[wonka1234]

@apathetichell  anychance you can recreate the workflow?

[nagakavyasri]

Use 'Multi-Field Formula' tool to get Column names

[wonka1234]

@nagakavyasri thanks, the only issue is the the columns loook to be hardcoded here -

IF Contains([Result], "unmatched") THEN [Result]+":"+[Team]+","+[City]+","+[Deposits] ELSE [Result] ENDIF

[nagakavyasri · Accepted solution]

Updated workflow, let me know if this helps: