This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have joined two files that each have about 60 columns. I want to be able to create a formula that subtracts file one from file two (think current minus prior year data). I know how to do this using the formula feature, but I don't want to create 60 formulas for each set of columns. Is there a way to create one formula that will subtract each column. I can place the columns next to each other using the select tool so ideally I would have column 2 minus column 1 and column 4 minus column 3 etc with each creating a new column to show the variance (i.e. subtraction result).
You could do this with a Transpose Tool -> Multi-Row Formula, and then CrossTab back into the original shape.
I've attached a screen shot below:
Essentially, I look for columns which contain "right" (not-case-sensitive), as these will be from file2. I then subtract the row above them, I then select only the differences, so I can coerce them into the format you're looking for, create an extra record ID with which to match my differences up with (to avoid a weird diagonal matrix looking dataframe), and join back in with the original data based on RecordID
I can go into more detail if you'd like, let me know if this helps,