Batch/Iterative Macros: Finding Sums

[rheepa]

Hi All,

I have a number of portfolio, gender, and dealer triplets. Within each set, I need to check whether any cartype's cost or additive combination of cartypes is equal to the "car" cost.

For example, for portfolio A, gender MALE, and dealer nyc, the nissan and car cost are the same. I want to flag the nissan record with a 1 and a 0 for the honda and toyota records.

Similarly, for portfolio A, gender FEMALE, and dealer la, the honda and toyota sum is equal to the "car" cost. I want to flag the honda and toyota records with a 1 and a 0 for the nissan.

There can be any number of cartypes. I attached the start of a batch macro but I am getting really confused on how to check for this. I'm thinking there should be a nested iterative macro but I'm not so sure. Can someone please help?

Thank you!!!

portfolio gender cartype cost dealer
A FEMALE honda -4125.44 nyc
A MALE honda -3304.56 nyc
A FEMALE car -13203.65 nyc
A FEMALE toyota -3652.18 nyc
A MALE nissan -11315.6 nyc
A MALE car -11315.6 nyc
A FEMALE nissan -5426.04 nyc
A MALE toyota -2631.91 nyc
A FEMALE honda -10000 la
A MALE honda -3291.16 la
A FEMALE car -13344.39 la
A FEMALE toyota -3344.39 la
A MALE nissan -5333.96 la
A MALE car -11202.48 la
A FEMALE nissan -5455.71 la
A MALE toyota -2577.36 la

[Qiu]

This is difficult...🤔

[Christina_H · Accepted solution]

Think I've got there in the end.  The iterative macro creates all combination groups of cars, which can then be compared to the "car" row.  Fingers crossed it works for more than just this data...

[rheepa]

Wow! Brilliant solution! Thank you so much! Truly amazing. Thank you!