Compare value for multiple fields (100+)

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

Tips and Tricks

Macros

Batch Macro

Accepted answers

flying008

Hi, @mer

The Multi-Row Formula is :

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

录制_2025_03_21_10_47_57_556.gif

录制_2025_03_21_10_19_21_589.gif

RecordID

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

598765

Smith

Match

Cindy

Match

2000-7-1

Match

2024-12-1

Match

35000

40000

Review

USD

EUR

Review

569859

Brown

Match

Jennifer

Match

1999-5-8

Match

2023-11-5

2023-11-4

Review

70000

Match

USD

Match

679682

Smith

Match

Andrew

Match

1975-5-3

1975-5-4

Review

2024-10-6

Match

500000

Match

USD

Match

59682

Williams

Match

Karen

Match

1982-12-25

1982-12-24

Review

2024-9-8

Match

150000

Match

EUR

Match

******

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

KGT

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.

Screenshot 2025-03-24 112528.png

Comparing sets of fields.yxmd

All comments

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.

录制_2025_03_21_09_31_04_938.gif

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.

Sample output.xlsx

Sample input.xlsx

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

Hi, @mer

The Multi-Row Formula is :

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

录制_2025_03_21_10_47_57_556.gif

录制_2025_03_21_10_19_21_589.gif

RecordID

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

598765

Smith

Match

Cindy

Match

2000-7-1

Match

2024-12-1

Match

35000

40000

Review

USD

EUR

Review

569859

Brown

Match

Jennifer

Match

1999-5-8

Match

2023-11-5

2023-11-4

Review

70000

Match

USD

Match

679682

Smith

Match

Andrew

Match

1975-5-3

1975-5-4

Review

2024-10-6

Match

500000

Match

USD

Match

59682

Williams

Match

Karen

Match

1982-12-25

1982-12-24

Review

2024-9-8

Match

150000

Match

EUR

Match

******

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

KGT

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.

Screenshot 2025-03-24 112528.png

Comparing sets of fields.yxmd

mer

Thank you all for your help!!