Assistance with Iterative Macros

[NoahWaite]

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.

[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.

 

[NoahWaite]

Thanks for your response.

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.

[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?

[SPetrie · Accepted solution]

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 🙂

 

[NoahWaite]

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