Excel Compare Numbers

[DoovidToonet]

So as a part of my job, I need to compare a dataset against a list we have in our system to be sure that everything's correct. This works good, however parts of the dataset end up looking like this:
 

 

My current way of checking is that I copy/paste the data into an Excel spreadsheet, which would use the following formula to check the invoices:

=OR(D2=J2,J2=0)

I want to develop a formula that would ignore anything after a "-" and compare the numbers before, is this possible? I cannot just compare the dollar amounts, as multiple numbers can have the same amount. My current way to do this is by just replacing everything after the "-" with blank space using find/replace, however if the dataset is ever different than normal it can take me more time to verify, which makes me lag behind.

[Bombastinator]

15 minutes ago, DoovidToonet said:

So as a part of my job, I need to compare a dataset against a list we have in our system to be sure that everything's correct. This works good, however parts of the dataset end up looking like this:
 

 

My current way of checking is that I copy/paste the data into an Excel spreadsheet, which would use the following formula to check the invoices:

=OR(D2=J2,J2=0)

I want to develop a formula that would ignore anything after a "-" and compare the numbers before, is this possible? I cannot just compare the dollar amounts, as multiple numbers can have the same amount. My current way to do this is by just replacing everything after the "-" with blank space using find/replace, however if the dataset is ever different than normal it can take me more time to verify, which makes me lag behind.

If that’s a database and they’re the same app you shouldn’t even need that.

[DoovidToonet]

16 minutes ago, Bombastinator said:

If that’s a database and they’re the same app you shouldn’t even need that.

This is what my job requires me to do, I need the numbers to line up for allocation purposes. The numbers are pulled from a proprietary app that doesn't like to match things up properly, and I've been told it'll take like 5-8 years to fix, so exporting to Excel and comparing there is the best way to do this.

[mariushm]

You can use simple basic excel functions  to split the string

 

For example assume

 

A  |   B   |  C  |  D 

x-y|      

 

A1 is your text, above it's "x-y"

In B column, use this formula to put where the "-" is in text minus one, or length of string otherwise :

=IFERROR(FIND("-",A1)-1,LEN(A1))

In C column, you can now have everything before the first "-" 

=LEFT(A1,B1)

In D column, you can now have whatever is after the - , if anything (you check if B has the same value as length of A,and if so output an empty text) :

=IF(LEN(A1)<>B1,  MID(A1,B1+2,LEN(A1)-B1)   ,"")

 

You can put these somewhere all the way to the right, and just add a column to the left ex to copy the title there

AA : =A1

AB : =IFERROR(FIND("-",AA1)-1,LEN(AA1))

AC : =LEFT(AA1,AB1)

AD : =IF(LEN(AA1)<>AB1,  MID(AA1,AB1+2,LEN(AA1)-AB1)   ,"")

 

Then just double click the bottom right corners of each cell (or drag from bottom right corner), to have the formulas go down all the way down to the last row.

 

 

 

 

[PDifolco]

44 minutes ago, DoovidToonet said:

So as a part of my job, I need to compare a dataset against a list we have in our system to be sure that everything's correct. This works good, however parts of the dataset end up looking like this:
 

 

My current way of checking is that I copy/paste the data into an Excel spreadsheet, which would use the following formula to check the invoices:

=OR(D2=J2,J2=0)

I want to develop a formula that would ignore anything after a "-" and compare the numbers before, is this possible? I cannot just compare the dollar amounts, as multiple numbers can have the same amount. My current way to do this is by just replacing everything after the "-" with blank space using find/replace, however if the dataset is ever different than normal it can take me more time to verify, which makes me lag behind.

This seems to work to get the left part before the -

EDIT - formula wasn't correct and didn't work if there was no "-", damn excel !

 

[DoovidToonet]

43 minutes ago, PDifolco said:

This formula worked well to extract the text before the hyphen, however when I go to compare the extracted text to the normal text, it throws a "False"

Below is the formula I am using to compare:

=OR(J170=O170,J170=0)

In theory, this should check to see if the two numbers are equal and throw a "True" if so, and if there is no value listed it will throw "True" by default.

 

[Bombastinator]

1 hour ago, DoovidToonet said:

This is what my job requires me to do, I need the numbers to line up for allocation purposes. The numbers are pulled from a proprietary app that doesn't like to match things up properly, and I've been told it'll take like 5-8 years to fix, so exporting to Excel and comparing there is the best way to do this.

This should be a simple function in excel, not anything complicated.  You can even have it display only the ones that are out of balance or merely flag them or something.

[PDifolco]

2 hours ago, DoovidToonet said:

This formula worked well to extract the text before the hyphen, however when I go to compare the extracted text to the normal text, it throws a "False"

Below is the formula I am using to compare:

=OR(J170=O170,J170=0)

In theory, this should check to see if the two numbers are equal and throw a "True" if so, and if there is no value listed it will throw "True" by default.

 

I suppose that's because the processed cell is text and the other is number format 

You should compare numbervalue(textcell) to the number