Formula help, CELL Function help needed.

  • Thread starter Thread starter Kenny McCormick
  • Start date Start date
K

Kenny McCormick

I am working on a project which requires me to compare data, then
subtract the value of that data to come up with a difference.

Example: I need help writing out a formula which will first, see if
any of the lot numbers in cell [C]"Batched" match any lot numbers in
cell [A]"Counted". note.(Lot numbers are the .XXXXXX-93) If there are
cells that indeed are Matches in colums D and A I then need to
subtract the "Remaining" from the
[D]*Weighed from those two cells. The lot number then needs to be fed
in colum [E] and the difference of the amount in colum [F].

Exibit A: An example of what I need to accoplish.

[A] [C] [D] [E] [F]
[1] Counted Remaining Batched Weighed Matched # DIFF.
[2].335215-01 1 .335215-01 7 .335215-01 6
[3].400039-07 73.12 .54049-01 48.350498 No Match n/a
[4].400415-01 3.44 .53707-02 2.9249 No Match n/a

Any help would be greatly appreciated!

KENNy
 
Hi Kenny,

Based on your sample data in E2 enter this formula:

=IF(C2=A2,A2,"No Match")

In F2 enter this formula:

=IF(C2=A2,D2-B2,"N/A")

Then you can just copy down as needed. This seems a little
too easy. Are there possible duplicates? If not then the
above formulas will do what you want. If there are
duplicates post back with more detail as to where the
duplicates would be and we'll try something else.

Biff
 
Thanks,
That did help me see what matches i have in the two sets of
colums but I am still lacking the second part where if there is a
match in the two colums, i need it to take the difference of the two
colums and put it in the difference colum in the worksheet.. (Refer to
example)

Any further help would be greatly appreciated.

kENNy

Biff said:
Hi Kenny,

Based on your sample data in E2 enter this formula:

=IF(C2=A2,A2,"No Match")

In F2 enter this formula:

=IF(C2=A2,D2-B2,"N/A")

Then you can just copy down as needed. This seems a little
too easy. Are there possible duplicates? If not then the
above formulas will do what you want. If there are
duplicates post back with more detail as to where the
duplicates would be and we'll try something else.

Biff



-----Original Message-----
I am working on a project which requires me to compare data, then
subtract the value of that data to come up with a difference.

Example: I need help writing out a formula which will first, see if
any of the lot numbers in cell [C]"Batched" match any lot numbers in
cell [A]"Counted". note.(Lot numbers are the .XXXXXX-93) If there are
cells that indeed are Matches in colums D and A I then need to
subtract the "Remaining" from the
[D]*Weighed from those two cells. The lot number then needs to be fed
in colum [E] and the difference of the amount in colum [F].

Exibit A: An example of what I need to accoplish.

[A] [C] [D] [E] [F]
[1] Counted Remaining Batched Weighed Matched # DIFF.
[2].335215-01 1 .335215-01 7 .335215- 01 6
[3].400039-07 73.12 .54049-01 48.350498 No Match n/a
[4].400415-01 3.44 .53707-02 2.9249 No Match n/a

Any help would be greatly appreciated!

KENNy
.
Click to expand...
Click to expand...
 
Hi Ken,

The second formula I offered does that. Did it not work?
It should have.

This formula tests for a match and if found subtracts
the 'remaining' from the 'weighed'. If there is no match
it returns N/A.

Biff
-----Original Message-----
Thanks,
That did help me see what matches i have in the two sets of
colums but I am still lacking the second part where if there is a
match in the two colums, i need it to take the difference of the two
colums and put it in the difference colum in the worksheet.. (Refer to
example)

Any further help would be greatly appreciated.

kENNy

"Biff" <[email protected]> wrote in message
Hi Kenny,

Based on your sample data in E2 enter this formula:

=IF(C2=A2,A2,"No Match")

In F2 enter this formula:

=IF(C2=A2,D2-B2,"N/A")

Then you can just copy down as needed. This seems a little
too easy. Are there possible duplicates? If not then the
above formulas will do what you want. If there are
duplicates post back with more detail as to where the
duplicates would be and we'll try something else.

Biff



-----Original Message-----
I am working on a project which requires me to compare data, then
subtract the value of that data to come up with a difference.

Example: I need help writing out a formula which will first, see if
any of the lot numbers in cell [C]"Batched" match any
Click to expand...
lot
numbers in
cell [A]"Counted". note.(Lot numbers are the .XXXXXX-
Click to expand...
93)
If there are
cells that indeed are Matches in colums D and A I then need to
subtract the "Remaining" from the
[D]*Weighed from those two cells. The lot number then needs to be fed
in colum [E] and the difference of the amount in colum [F].

Exibit A: An example of what I need to accoplish.

[A] [C] [D] [E] [F]
[1] Counted Remaining Batched Weighed Matched # DIFF.
[2].335215-01 1 .335215-01
Click to expand...
7 .335215-
01 6
[3].400039-07 73.12 .54049-01
Click to expand...
48.350498 No
Match n/a
[4].400415-01 3.44 .53707-02
Click to expand...
2.9249 No
Match n/a
Any help would be greatly appreciated!

KENNy
.
Click to expand...
Click to expand...

.
Click to expand...
 
Hi Ken,

I sent a reply yesterday but I don't see it at CDO. It is
in the archives however.

The second formula I offered does that. Did it not work?
It should have.

This tests to see if there a match and if so subtracts
the 'remaining' from the 'weighed'. If no match then it
returns N/A.

Biff
-----Original Message-----
Thanks,
That did help me see what matches i have in the two sets of
colums but I am still lacking the second part where if there is a
match in the two colums, i need it to take the difference of the two
colums and put it in the difference colum in the worksheet.. (Refer to
example)

Any further help would be greatly appreciated.

kENNy

"Biff" <[email protected]> wrote in message
Hi Kenny,

Based on your sample data in E2 enter this formula:

=IF(C2=A2,A2,"No Match")

In F2 enter this formula:

=IF(C2=A2,D2-B2,"N/A")

Then you can just copy down as needed. This seems a little
too easy. Are there possible duplicates? If not then the
above formulas will do what you want. If there are
duplicates post back with more detail as to where the
duplicates would be and we'll try something else.

Biff



-----Original Message-----
I am working on a project which requires me to compare data, then
subtract the value of that data to come up with a difference.

Example: I need help writing out a formula which will first, see if
any of the lot numbers in cell [C]"Batched" match any
Click to expand...
lot
numbers in
cell [A]"Counted". note.(Lot numbers are the .XXXXXX-
Click to expand...
93)
If there are
cells that indeed are Matches in colums D and A I then need to
subtract the "Remaining" from the
[D]*Weighed from those two cells. The lot number then needs to be fed
in colum [E] and the difference of the amount in colum [F].

Exibit A: An example of what I need to accoplish.

[A] [C] [D] [E] [F]
[1] Counted Remaining Batched Weighed Matched # DIFF.
[2].335215-01 1 .335215-01
Click to expand...
7 .335215-
01 6
[3].400039-07 73.12 .54049-01
Click to expand...
48.350498 No
Match n/a
[4].400415-01 3.44 .53707-02
Click to expand...
2.9249 No
Match n/a
Any help would be greatly appreciated!

KENNy
.
Click to expand...
Click to expand...

.
Click to expand...
 
Back
Top