Complicated replacement values in list issue

[CompleteNewb]

Okay, I've got a pretty complex issue here, and was hoping someone had the
time to help or advise me. It's posisble that I might be trying to solve
the problem in the wrong way, as well as not knowing quite how to solve it
using my own ideas.

We ship car part orders, but often have the ability to replace items on an
order with other items based on certain criteria. For instance, maybe the
orderd part is out of stock, but the replacement part is the exact same
color and size, or might be different color but is the same price, etc.

So for every part we have a list of possible replacements, ranked in order
(some replacements are better than others), and the reason they are a
possible replacement. For instance:

Original Part Replacement1PartNum
Replacement1Rank Replacement1Reason
OE54 TF32
1 SameSpecs
OE54 OM23 2
SameColor
OE54 OE16
3 SamePrice

So above, you see that any order that contains part # OE54, if we are out of
that part, we could potentially replace it with parts TF32, OM23, or OE16.
We'd use TF32 first, unless we were out of it, and in that case we'd use
OM23, etc. (the Rank is the preference for replacing with that part).

Now here's another complication. TF32 might be a good replacement for OE54
because it's the same specs, but it might also be a replacement for a
different part number for a different reason. For instance, TF32 might be
the same color as XJ33, but not the same specs. So for a more complete list
of replacement parts (instead of just the replacements for ONE specific
part), we have this:

Original Part Replacement1PartNum
Replacement1Rank Replacement1Reason
OE54 TF32
1 SameSpecs
OE54 OM23 2
SameColor
OE54 OE16
3 SamePrice
XJ33 YU25
1 SameSpecs
XJ33 TF32
2 SameColor
XJ33 KN54
3 SamePrice
TF32 OE54
1 SameSpecs
TF32 XJ33
2 SameColor

So you see the obvious trend that if one part is a replacement for another
part for some reason, it makes sense that they are interchangeable for the
same reason. BUT THIS IS NOT ALWAYS THE CASE. Just so you don't assume
it's a rule. As you can also see, though, different parts are possible
replacements for other parts for completley different reasons. Basically,
the important thing is, we have a list of parts, then a ranked list of
possible replacements, and WHY they are possible replacements. Now here's
why:

We have a different list for the packing person so they have it to refer to
as they box up the order; they know that if OE54 is out, their first choice
is to replace it with TF32, etc. The current list is in Excel, with every
replacement listed in one cell to the right of the original Part Number. So
it looks likes this:

Original Part Replacements
OE54 TF32 (SameSpecs), OM23 (SameColor), OE16
(SamePrice)
XJ33 YU25 (SameSpecs), TF32 (SameColor), KN54
(SamePrice)

So the packager, when he receives an order that has OE54 in it, can look at
this list and see that if the customer wants the same specs if we're out, he
should use TF32. However, if the customer has specified that any
replacement must be the same color and the specs aren't all that important,
then the packager knows he should use OM23 (it's the same color, even though
it's ranked as a 2 instead of a 1). So when the customer has not specified
ANY replacement criteria, the packager will always use what's listed first.
If the customer specifies something (like that the color must match, in my
example), the packager will look at this replacements list and choose OM23
instead, since it's the same color. THE RANK IS NOT ALWAYS BASED ON THE
SAME THING. It's not always the case that SameSpecs will always have a rank
of 1. Sometimes things are ranked as the #1 replacement because it's the
same color, not the same specs, etc. Plus, some of the part numbers have a
lot more than just 3 replacements.

Now, when we run out of something, we want to remove the instance of that
part number from the replacements list, so the packager doesn't have to keep
looking through the replacements list and realizing that we're also out of
replacements. For instance, when we run out of TF32, we don't want TF32
listed as a possible replacement for anything else (since we're out; you
can't replace anything with TF32, because we're ALSO out of TF32).

So what I'm TRYING to do is somehow automate the removal of out-of-stock
items from the possible replacements list. Right now (and don't laugh,
here, please), when we run out of TF32, I have to MANUALLY go through and
search for TF32 in the replacements Excel list the Packager has, and delete
the TF32 (as well as its reason for being a replacement) from the list. So
let's say we have this list starting out:

Original Part Replacements
OE54 TF32 (SameSpecs), OM23 (SameColor), OE16
(SamePrice)
XJ33 YU25 (SameSpecs), TF32 (SameColor), KN54
(SamePrice)

Then we run out of TF32. So we don't want TF32 listed as a possible
replacement anymore. So the new list needs to be like this:

Original Part Replacements
OE54 OM23 (SameColor), OE16 (SamePrice)
XJ33 YU25 (SameSpecs), KN54 (SamePrice)

So can someone help me with the possibilities of doing this? I have tried
using a combination of Access queries that are used to export the new list
for the packagers, but so far getting the replacements to list out, in order
of rank, including the replacement reason, with commas, etc. separating them
in Excel has eluded me. I'm not TOTALLY married to Excel, because if we can
save the hours it takes to manually generate the new list (going through and
deleting every instance of the newly-out-of-stock item in the replacements
list BY HAND) by using something different, I think it's worth it to change
formats. But if it's possible to have the final output in Excel, that's
what everyone currently uses, and you know how much people hate change.

I apologize for the length and complexity of this, but I'm in over my head
here. Any advice, diferent viewpoints, etc. would be very welcome, and I
very much appreciate your time. If someone can help me get through to the
end of this, I am totally happy to buy you lunch at your favorite restaurant
(well, not if Ruth's Chris is your favorite restaurant. But I mean, c'mon,
think about your health here; steaks in sizzling BUTTER?!! You'll be dead
before you get to dessert!!).

Thanks for any help, and thanks also for reading.

PS: Sorry about the Excel/Access cross-post, but I'm a user/experimenter
with both, and thought someone with either viewpoint might have useful
ideas).

The Complete Newb

 

[Tim Williams]

Where is the replacement information maintained, and where is your list of
"in stock" parts kept ?
It should be pretty straightforward to just mark a part as out-of-stock in
the Master list and then have it be excluded from the replacements list.

If you want to send me a sample file with some data and what you're trying
to acheive I'll have a go at it.
tim j will iams at gmail dot com (no spaces)


Tim

 

[OssieMac]

Hi,

I wonder if a simple macro to change the font color of the cells containing
the unavailable replacement parts will suffice.

If the font color is changed to White then they are not visible and they do
not print.

When the parts become available the macro can toggle them back to black.

The following macro has an Input Box to request the part number and it will
change them to White if they are currently Black and vice versa. You will
need to adjust the column to be searched and the number of columns to change.
(Your post broke up the lines but it appeared to me that there are 3 columns
associated with each alternative part.)

I realize that there are probably many more columns to search because you
have many options against a part number but the macro can be enhanced to say
search every 4th column. At this point, I simply want to know if you are
interested in the idea.

I see the big advantage is that the data does not get deleted from the
spreadsheet and that can reduce errors that can be introduced when it is
reinserted.


Sub Make_Visible_InVisible()

Dim rngReplacements As Range
Dim foundcell As Range
Dim firstAddress As String
Dim varTofind
Dim numbCols As Long

'Edit the following line to the
'number of additional columns
'Note: Value 2 will change font in 3 columns
numbCols = 2

'Edit following for your sheet name
'and columns
With Sheets("Sheet1")
Set rngReplacements = .Columns("B:B")
End With

varTofind = InputBox("Search for what part number?")

With rngReplacements
Set foundcell = .Find(What:=varTofind, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not foundcell Is Nothing Then
firstAddress = foundcell.Address

Do
If foundcell.Font.Color = vbWhite Then
Range(foundcell, foundcell.Offset(0, numbCols)) _
.Font.Color = vbBlack
Else
Range(foundcell, foundcell.Offset(0, numbCols)) _
.Font.Color = vbWhite
End If
Set foundcell = .FindNext(foundcell)

Loop While Not foundcell Is Nothing And _
foundcell.Address <> firstAddress
End If
End With
End Sub