Advance filter question

[QuickLearner]

My earlier post was obviously too wordy or difficult.
Perhaps an example of what I am trying to achieve may help.

Have a look at the attached workbook. It has two worksheets:
Item history, which has an items history and the priority of quotes
that the current information takes preference on.
Current Data is the worksheet I would like to achieve by filtering,
looping through cells???? This row represents the most prefered data
for a particular item.

Ideally, by selecting this item the user will be presented with a
variety of options for viewing the items information. (filter by
supplier, or by quotes etc)

Any ideas of how I can achieve this?


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4896 |
+-------------------------------------------------------------------+

 

[Tom Ogilvy]

I am not following all of what you are doing or your rules, but this should
get you started:

Sub ABC()
Set sh1 = Worksheets("Item History")
Set sh2 = Worksheets("Current Data")
sh1.AutoFilterMode = False

With sh1
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 2).End(xlUp)) _
.Resize(, 12)
End With
ans = InputBox("Enter Item Number")
If ans = "" Then Exit Sub
If Application.Count(rng.Columns(2), ans) = 0 Then
MsgBox "Not found"
Exit Sub
End If
rng.AutoFilter Field:=2, Criteria1:=ans
Set rng1 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 12)
Set rng2 = rng1.Columns(12).SpecialCells(xlVisible)
For Each cell In rng2
cell.EntireRow.Select
MsgBox cell.Offset(0, -2).Value & _
" has a Preference rating of " & cell.Value
Next



End Sub

 

[QuickLearner]

Tom,
Many thanks for that reply, it has got me thinking!
I can now see how you are looping and reading the value for each
Priority.
I can also carry out a similar routine for each date.

My main problem here is trying to get my head around building a single
row from the data in the item history sheet.

Each cell in the row must hold the most recent data (Max Date), but if
there is older data with a higher priority, then that should go into
the cell.

So ending up with just one row of information with the latest
information for the item, but priority takes presedence over date.

this is also a simplification because my spreadsheet should end up with
about 3000 rows of unique items.

 

[Tom Ogilvy]

Just add some code/logic to that loop of the priorities: (again, this
probably isn't complete, but should again stimulate your thinking)

Sub ABC()
Set sh1 = Worksheets("Item History")
Set sh2 = Worksheets("Current Data")
sh1.AutoFilterMode = False

With sh1
Set rng = .Range(.Cells(2, 1), .Cells(Rows.Count, 2).End(xlUp)) _
.Resize(, 12)
End With
ans = InputBox("Enter Item Number")
If ans = "" Then Exit Sub
If Application.Count(rng.Columns(2), ans) = 0 Then
MsgBox "Not found"
Exit Sub
End If
rng.AutoFilter Field:=2, Criteria1:=ans
Set rng1 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 12)
Set rng2 = rng1.Columns(12).SpecialCells(xlVisible)
maxdate = 0
maxDateRow = 0
highPriRow = 0
highPri = 11 ' assume a pri of 1 is the highest and 10 is the lowest
With sh1
For Each cell In rng2
' check if the row is a provider
If .Cells(cell.Row, 4) = "" Then
' it is a provide
If cell.Value < highPri Then
highPri = cell.Value
highPriRow = cell.Row
End If
If .Cells(cell.Row, 1) > maxdate Then
maxdate = .Cells(cell.Row, 1)
maxDateRow = cell.Row
End If
End If
Next
If .Cells(maxDateRow, 12) = highPri Then
Set rngTocopy = .Cells(maxDateRow, 6).Resize(1, 4)
Else
Set rngTocopy = .Cells(highPriRow, 6).Resize(1, 4)
End If
rngTocopy.Copy Destination:=sh2.Cells(2, 5)
End With

End Sub

 

[QuickLearner]

Tom,
You sir, are a steely eyed missile-man!!
Still a bit to do as it is only populating the row with the repair
information but not the pricing!

Shall do my best to adapt this to what I need.

 

[QuickLearner]

sorry to be ignorant
but could you please explain what the following code is doing:

Set rng1 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 12)
Set rng2 = rng1.Columns(12).SpecialCells(xlVisible)

 

[Tom Ogilvy]

rng is a reference to your database starting in row 2
rng1 is a reference to your database starting in row 3 - data only - no
headers
rng2 is a refence to the visible cells in column L - starting in row 3

Set rng1 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 12)
Set rng2 = rng1.Columns(12).SpecialCells(xlVisible)

Yes, i didn't put in any pricing because I wasn't sure what the criteria
was/is to pick the pricing row.

 

[QuickLearner]

Many thanks again Tom,
The criteria for the pricing columns was the same for the repairs. i.e.
the prices from a priority quote OR The latest information from the next
priority down if its missing from the top priority quote.

I dont think I gave an example in the worksheet, but what I mean is:
If a price is given on a top priority quote, but no lead time is given,
take the price from the top quote but the lead time from the next latest
quote.

Hope that makes sense!

Cheers.