Compare Two Columns and instert New Column

Hello,

I am a new user and was hoping if I could get some help on a formula I am trying to use. Basically I have 1 excel file, that contains persons and their monthly bills for the last two months. I need to compare the current month's amount versus last month's amount and then insert a column that would tell me if the amounts are the same or not. In excel I would just use the formula =IF(B2=C2,"OK","CHECK").

A mock sample of how the spreadsheet would look like is below. I am hoping to populate the column "Compare Result" with either "OK" if July and August values are the same or "CHECK" if they are not using a formula in Alteryx.

Thank you in advance.

Workflow

Accepted answers

anandshingi

if you will be having only 2 months all the time in the dataset,

use formula tool >> add new column and use formula

if [July] = [August] then "OK" else "Check"
endif

All comments

Elias_Nordlinder

Hello @mcloma

Here is a dynamic approach that would solve your problem.

I decided to do it dynamic if you wanted to check more months than just July and August.

1. I use the transpose tool to convert the values to come below each other.

When they are below each other I can compare values that would be besides each other in the columns.

For example the July and August column (500 and 800 for Gary) will now look like this.

If there would be more columns (Months or Name, these would also work in this solution).

2. MultiRow Formula: This is a great tool for dynamic approaches, for example checking if two rows below each other matches or not.

Here I want to check for example if the value 500 and 800 matches for Gary, I use the formula below:

If the name Gary is the same on this row as the row before, and the value (800) is the same as the row before (500) or

if the name Gary is the same as the row after, and the value (500) is the same as the row after (800) then write "OK",

otherwise write "CHECK".

3. Use the CrossTab tool to get back to the original again.

I see this as the opposite to the Transpose tool.
The pattern Transpose -> Multi Row Formula -> Cross Tab is great for these kind of solution.

4. Select tool to remove unwanted columns.

Result:

//Let me know if this solves your problem 🙂

Regards
Elias

Compare Two Columns.yxmd

anandshingi

if you will be having only 2 months all the time in the dataset,

use formula tool >> add new column and use formula

if [July] = [August] then "OK" else "Check"
endif

mcloma

thanks both for your help, I only need to check 2 months data all the time so ended up using @anandshingi solution and it worked fine.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703