Compare value for multiple fields (100+)

[mer]

I need help comparing value for multiple fields. I have over 100 fields that need to be reviewed. I tried creating a formula however, it will take quite some time to add a formula for each comparison (i.e IF [LAST NAME] = [LAST NAME FH] THEN "MATCH" ELSE "REVIEW" ENDIF) since I have over 100 fields. The field that needs to be compared contain a suffix of FH. I need a new validation field added for each comparison. 

Below are examples of my field names.

LAST NAME

LAST NAME FH

FIRST NAME

FIRST NAME FH

BIRTH DATE

BIRTH DATE FH

TERMINATION DATE

TERMINATION DATE FH

[caltang]

I would suggest to pivot it and batch it if needed. But can you give some sample data and sample output to better help with the request?

[KGT]

Yep, pivot/transpose would be my go to here. If you have 100 million rows, then you would want a more efficient method, but transposes are going to be easiest. Transpose all your "Non-FH" fields, and in parallel pivot all your "FH" fields, Use a formula to create a field that removes the FH, and then join on your key fields and [Name]. Then compare.

A little used tool, but does exactly this, is the arrange tool. You can add a column and select all the "Non FH" fields, then add another column and select all the "FH" fields. Select all others as key fields, or add a RecordID and select it as Key. You will then have them next to each other, and can compare.

The other option is Dynamic Formula from the CReW Macros, that will enable you to programmatically create all the formulas, but is a little more setup than the above.

[flying008]

Hi, @mer 

FYI.

Input
LAST NAME	LAST NAME FH	FIRST NAME	FIRST NAME FH	BIRTH DATE	BIRTH DATE FH	TERMINATION DATE	TERMINATION DATE FH
1	1	2	0	3	3	4	4
0	1	2	2	4	4	5	5
Output
LAST NAME	LAST NAME FH	LAST NAME_Compare	FIRST NAME	FIRST NAME FH	FIRST NAME_Compare	BIRTH DATE	BIRTH DATE FH	BIRTH DATE_Compare	TERMINATION DATE	TERMINATION DATE FH	TERMINATION DATE_Compare
1	1	Match	2	0	Review	3	3	Match	4	4	Match
0	1	Review	2	2	Match	4	4	Match	5	5	Match

LAST NAME_Compare	FIRST NAME_Compare	BIRTH DATE_Compare	TERMINATION DATE_Compare
Match	Review	Match	Match
Review	Match	Match	Match

[mer]

Hello. Thanks for your help. I have around 30k rows and over 100 columns. Below is sample data.

[mer]

@flying008 Thank you! This is what I'm looking for. Are you able to share the workflow so I can review the configuration? 

[flying008 · Accepted solution]

Hi, @mer 

The Multi-Row Formula is :

IIF([RowCount] = 2, IIF([Row-2:Value] = [Row-1:Value], 'Match', 'Review'), [Value])

RecordID

ID

Last Name

Last Name FH

Last Name Validation

First Name

First Name FH

First Name Validation

Birth Date

Birth Date FH

Birth Date Validation

Termination Date

Termination Date FH

Termination Date Validation

Annual Pay

Annual Pay FH

Annual Pay Validation

Annual Pay Currency

Annual Pay Currency FH

Annual Pay Currency Validation

1

598765

Smith

Smith

Match

Cindy

Cindy

Match

2000-7-1

2000-7-1

Match

2024-12-1

2024-12-1

Match

35000

40000

Review

USD

EUR

Review

2

569859

Brown

Brown

Match

Jennifer

Jennifer

Match

1999-5-8

1999-5-8

Match

2023-11-5

2023-11-4

Review

70000

70000

Match

USD

USD

Match

3

679682

Smith

Smith

Match

Andrew

Andrew

Match

1975-5-3

1975-5-4

Review

2024-10-6

2024-10-6

Match

500000

500000

Match

USD

USD

Match

4

59682

Williams

Williams

Match

Karen

Karen

Match

1982-12-25

1982-12-24

Review

2024-9-8

2024-9-8

Match

150000

150000

Match

EUR

EUR

Match

******

If can help you get your want, please mark it as a solution and give like for more share.

[KGT · Accepted solution]

I thought I would give the Arrange tool a try because I don't often find a great use case for it. The selection of fields is not dynamic in it like it is in Transpose, and the transpose gives more control over creating extra fields, like something representing the field name.

So, I would still go with the bottom option in this screenshot, but the arrange tool could give you a quick summary of how many matches per ID.

[mer]

Thank you all for your help!!