Hi, Essentially, I'm looking to build out a macro that will search for incongruences between the first two columns of two data sets, along with the values of "x" columns, cycling through until all of the columns have been analyzed. (Cycle 1: Columns 1+2+3-6, Cycle 2: Columns 1+2+7-10, etc.) It seems to me that an iterative macro should be able to assist with this process. Admittedly, I'm not entirely sure if this is the best way to solve this problem, so any guidance would be greatly appreciated. Sample Data: Set 1- Column 1Column 2Column 3Column 4Column 5Column 6Column 7Column 8Column 9Column 10af3a4a5a6a7a8a9a10abg3b4b5b6b7b8b9b10bch3c4c5c6c7c8c9c10cdi3d4d5d6d7d8d9d10dej3e4e5e6e7e8e9e10e Set 2- Column 1Column 2Column 3Column 4Column 5Column 6Column 7Column 8Column 9Column 10af3a4a5a6a7a8a9g10abg3b4b5b6b7b8b9b10bch3c4f5c6c7c8c9c10cd i3d4d5d6d7d8d9d10de j3e4e5e6e7e8e9e10e Output - Column 1Column 2Column 3Column 4Column 5Column 6ch3c4c5c6cch3c4f5c6c Column 1Column 2Column 7Column 8Column 9Column 10af7a8a9a10aaf7a8a9g10a Thanks for your help. *EDIT: Additional Info* Columns 1+2 will need to remain static, as within my actual data set they are key information. Apart from that, the first column of each set (e.g. Column 3 when reviewing 1+2+3-6) is the next key piece of data, with the following columns being metadata for that set. For these reasons, ideally my output will look similar to how I've written it above.

Assistance with Iterative Macros

Hi,

Essentially, I'm looking to build out a macro that will search for incongruences between the first two columns of two data sets, along with the values of "x" columns, cycling through until all of the columns have been analyzed. (Cycle 1: Columns 1+2+3-6, Cycle 2: Columns 1+2+7-10, etc.) It seems to me that an iterative macro should be able to assist with this process. Admittedly, I'm not entirely sure if this is the best way to solve this problem, so any guidance would be greatly appreciated.

Sample Data:

Set 1-

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10
a	f	3a	4a	5a	6a	7a	8a	9a	10a
b	g	3b	4b	5b	6b	7b	8b	9b	10b
c	h	3c	4c	5c	6c	7c	8c	9c	10c
d	i	3d	4d	5d	6d	7d	8d	9d	10d
e	j	3e	4e	5e	6e	7e	8e	9e	10e

Set 2-

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10
a	f	3a	4a	5a	6a	7a	8a	9g	10a
b	g	3b	4b	5b	6b	7b	8b	9b	10b
c	h	3c	4f	5c	6c	7c	8c	9c	10c
d	i	3d	4d	5d	6d	7d	8d	9d	10d
e	j	3e	4e	5e	6e	7e	8e	9e	10e

Output -

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6
c	h	3c	4c	5c	6c
c	h	3c	4f	5c	6c

Column 1	Column 2	Column 7	Column 8	Column 9	Column 10
a	f	7a	8a	9a	10a
a	f	7a	8a	9g	10a

Thanks for your help.

*EDIT: Additional Info*

Columns 1+2 will need to remain static, as within my actual data set they are key information. Apart from that, the first column of each set (e.g. Column 3 when reviewing 1+2+3-6) is the next key piece of data, with the following columns being metadata for that set. For these reasons, ideally my output will look similar to how I've written it above.

Macros

Iterative Macro

Accepted answers

SPetrie

Assuming you only have the 10 columns and its always 4 and 4 for the comparisons, I tweaked @phottovy's workflow to generate the output I believe you are looking for. Their workflow was simpler than what I was going to kludge together 🙂

take 2.PNG

Assistance with Iterative Macros2.yxmd

All comments

SPetrie

Some of this may depend on how the data will be going forward. Will both sets always have the same number of rows? Same number of columns? Etc.

Assuming those stay the same, I dont think a macro is necessary. I was able to get basically the same result you are looking for, if not formatted the way you are wanting. It should get you on your way though and we can always tweak it as necessary.

SetCompare.yxmd

NoahWaite

Thanks for your response.

phottovy

I agree with @SPetrie that you probably don't need a macro. I put together the attached workflow that is very similar before realizing the first output is columns 1-6 and second being 1,2, 7-10. Can you explain how you identify which columns to keep in your output?

Assistance with Iterative Macros.yxmd

SPetrie

take 2.PNG

Assistance with Iterative Macros2.yxmd

NoahWaite

This was exactly what I needed. Thank you! Thanks to you as well, @phottovy.

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157