Complex Lookup
 

I have to following inventory data:

Pool Part # Final Discrepancies Actions

FG11 286692-001 -1
FG13 286692-001 1 Move 1 to FG11

I need a formula that automatically gives me the comment "Move 1 to FG11"
considering 1 as the number of parts to move in order to correct both
inventories. Please note that there are hundreds of diferent part #'s under
FG11 and FG13. What I need is for Excel to find, everytime there is an
overcount of one part, if there is the same part # somewhere else with a
short count and suggest me to move the part there. The -1 means I'm short one
piece and the 1 I'm over one piece. Sorting doesn't work when handling
hundreds of parts. It's better if I get that comment automatically then sort
that column and work over it.

Complex Lookup
 

Maybe a formula like this:

=INDIRECT("D" & MATCH(1,C1:C10,0))

HTH,
Paul


--

"Byron720" wrote in message
...
I have to following inventory data:

Pool Part # Final Discrepancies Actions

FG11 286692-001 -1
FG13 286692-001 1 Move 1 to FG11

I need a formula that automatically gives me the comment "Move 1 to FG11"
considering 1 as the number of parts to move in order to correct both
inventories. Please note that there are hundreds of diferent part #'s
under
FG11 and FG13. What I need is for Excel to find, everytime there is an
overcount of one part, if there is the same part # somewhere else with a
short count and suggest me to move the part there. The -1 means I'm short
one
piece and the 1 I'm over one piece. Sorting doesn't work when handling
hundreds of parts. It's better if I get that comment automatically then
sort
that column and work over it.

Complex Lookup
 

Not that helpful

"PCLIVE" wrote:

Maybe a formula like this:

=INDIRECT("D" & MATCH(1,C1:C10,0))

HTH,
Paul


--

"Byron720" wrote in message
...
I have to following inventory data:

Pool Part # Final Discrepancies Actions

FG11 286692-001 -1
FG13 286692-001 1 Move 1 to FG11

I need a formula that automatically gives me the comment "Move 1 to FG11"
considering 1 as the number of parts to move in order to correct both
inventories. Please note that there are hundreds of diferent part #'s
under
FG11 and FG13. What I need is for Excel to find, everytime there is an
overcount of one part, if there is the same part # somewhere else with a
short count and suggest me to move the part there. The -1 means I'm short
one
piece and the 1 I'm over one piece. Sorting doesn't work when handling
hundreds of parts. It's better if I get that comment automatically then
sort
that column and work over it.

Complex Lookup
 

Another play to try ...

Assuming source table in cols A to C*, data from row2 down
*ie Pool, Part #, Final Discrepancies

Put in D2:
=IF(C2="","",COUNTIF(C$2:C2,C2))

Put in E2, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=IF(D2="","",IF(ISNUMBER(MATCH(-C2&"_"&D2&"_"&B2,C$2:C2&"_"&D$2:D2&"_"&B$2:B2,0)), "Move
"&C2&" to
"&INDEX(A$2:A2,MATCH(-C2&"_"&D2&"_"&B2,C$2:C2&"_"&D$2:D2&"_"&B$2:B2,0)), ""))

Select D2:E2, fill down as far as required. Hide away col D. Col E should
return the required "action" indications (resembling what you're after)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Complex Lookup
 

Sorry, pl dismiss the earlier ..

Here's a revised, more robust play ..

Illustrated in this sample construct:
http://www.savefile.com/files/1003536
Complex Lookup - Inventory Matching.xls

Assuming source table in cols A to C*, data from row2 down
*ie Pool, Part #, Final Discrepancies

In D1: =IF(OR(B2="",C2=""),"",B2&"_"&C2)
In E1: =IF(D2="","",COUNTIF(D$2:D2,D2))

In F1, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=IF(E2="","",
IF(ISNUMBER(MATCH(-C2&"_"&E2&"_"&B2,C$2:C2&"_"&E$2:E2&"_"&B$2:B2,0)) ,
"Move "&C2&" to "&
INDEX(A$2:A2,MATCH(-C2&"_"&E2&"_"&B2,C$2:C2&"_"&E$2:E2&"_"&B$2:B2,0)), ""))

Select D1:F1, fill down as far as required. Hide away cols D & E.
Col F will return the required "action" indications (resembling what you're
after)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Byron720" wrote in message
...
I have to following inventory data:

Pool Part # Final Discrepancies Actions

FG11 286692-001 -1
FG13 286692-001 1 Move 1 to FG11

I need a formula that automatically gives me the comment "Move 1 to FG11"
considering 1 as the number of parts to move in order to correct both
inventories. Please note that there are hundreds of diferent part #'s under
FG11 and FG13. What I need is for Excel to find, everytime there is an
overcount of one part, if there is the same part # somewhere else with a
short count and suggest me to move the part there. The -1 means I'm short one
piece and the 1 I'm over one piece. Sorting doesn't work when handling
hundreds of parts. It's better if I get that comment automatically then sort
that column and work over it.

Complex Lookup
 

You are an absolute Excel Monster (in a good way). Thank you soooo much.

"Max" wrote:

Sorry, pl dismiss the earlier ..

Here's a revised, more robust play ..

Illustrated in this sample construct:
http://www.savefile.com/files/1003536
Complex Lookup - Inventory Matching.xls

Assuming source table in cols A to C*, data from row2 down
*ie Pool, Part #, Final Discrepancies

In D1: =IF(OR(B2="",C2=""),"",B2&"_"&C2)
In E1: =IF(D2="","",COUNTIF(D$2:D2,D2))

In F1, array-entered (press CTRL+SHIFT+ENTER to confirm the formula):
=IF(E2="","",
IF(ISNUMBER(MATCH(-C2&"_"&E2&"_"&B2,C$2:C2&"_"&E$2:E2&"_"&B$2:B2,0)) ,
"Move "&C2&" to "&
INDEX(A$2:A2,MATCH(-C2&"_"&E2&"_"&B2,C$2:C2&"_"&E$2:E2&"_"&B$2:B2,0)), ""))

Select D1:F1, fill down as far as required. Hide away cols D & E.
Col F will return the required "action" indications (resembling what you're
after)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Byron720" wrote in message
...
I have to following inventory data:

Pool Part # Final Discrepancies Actions

FG11 286692-001 -1
FG13 286692-001 1 Move 1 to FG11

I need a formula that automatically gives me the comment "Move 1 to FG11"
considering 1 as the number of parts to move in order to correct both
inventories. Please note that there are hundreds of diferent part #'s under
FG11 and FG13. What I need is for Excel to find, everytime there is an
overcount of one part, if there is the same part # somewhere else with a
short count and suggest me to move the part there. The -1 means I'm short one
piece and the 1 I'm over one piece. Sorting doesn't work when handling
hundreds of parts. It's better if I get that comment automatically then sort
that column and work over it.

Complex Lookup
 

welcome, glad it helped.

There were a couple of typos in the cell refs earlier

All of these should be referring to row2,
instead of row1, eg:

"In D1, In E1, In F1, D1:F1"

should have read:
"In D2, In E2, In F2, D2:F2 "
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Byron720" wrote in message
...
You are an absolute Excel Monster (in a good way). Thank you soooo much.