Complex Lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)

---
 
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)
 
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 "
 
Back
Top