1

I have data stored in three columns of Excel

Column A: Serial Number Column B: Date Column C: Value (e.g. Cost)

I need to look for the Value (Column C) associated with a particular Serial Number (Column A) AND Date (Column B).

So for example, in the screenshot below, if I want to look for the Value associated with Serial number (T455) and Date (Dec 13, 2010), the value should be 8.

enter image description here

The only method I can come up with would be computationally inefficient, because I would go through ALL the cells each time I look for a value.

Is there a method, for example, that would limit the search area for a given serial number?

For example, if I am looking for a value for Serial Number T455, how can I limit the code to search for the date in Rows (6-13) and find the corresponding value in Column C, rather than searching the whole table?

Sub FindValue() Dim S as String Dim D as Date Dim V as Integer S = T455 D = Dec 13, 2010 for i = 1 to Range("A1").End(xldown).Row If Range("A" & i) = S And Range("B" & i) < Date - 7 And Range("B" & i) < Date + 7 Then ' This way i search a date range rather than a specific date V = Range("C" & i).Value End If End Sub

I thought of While loops, or Lookup functions, but reached a dead end.

Improve this question

2 Answers 2

Reset to default
2

Non-VBA Solution that may be a lot easier and less of a headache.

Column A consists of the formula, for A1 = "=B1&C1"

Cell G1 formula can be seen in formula bar.

enter image description here

UPDATE Here is a VBA solution that will work faster, but there are some notes based on what you wrote that I am unsure of. Also, see some comments to help your code work more like you want it to.

Sub FindValue() Dim S As String, D As Date, V As Integer, rngFound As Range, cel As Range S = "T455" 'needs quotes around the string D = "Dec 13, 2010" 'needs quotes around the date Dim wks As Worksheet Set wks = ActiveSheet With wks 'always better to AutoFilter than Loop when you can! .UsedRange.AutoFilter 1, S .UsedRange.AutoFilter 2, ">" & D - 7, xlAnd, "<" & D + 7 Set rngFound = Intersect(.UsedRange, .Columns(3)).SpecialCells(xlCellTypeVisible) 'the only thing here is if you have a date range _ 'you may return more than one result _ 'in that case, I don't know what you want to do with all the V's If Not rngFound Is Nothing Then For Each cel In rngFound V = cel.Value Next End If .AutoFilterMode = False End With End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I like this idea, but it has some issues depending on the date/serial combo. For instance, if you had "S111" & "1/1/2000", you'll see that this creates the same value as "S110" & "1/1/2000". Other than that, it's a great, simple solution.
Thinking about it a bit more, you could probably avoid this by adding an underscore or similar delimiter not present in the serial/date columns. Also, if you perform the sorts as I mentioned, then you can use some very fast lookup methods without VBA.
@Zairja - how does concatenating S111 & 1/1/2000 and S110 & 1/1/2000 create the same value? If you do this on a spreadsheet, the first results in S11136526 and the 2nd in 'S11036526` (it uses the date serial number)
My mistake, I meant something more akin to S111 & 1/1/2000 and S11 & 10/16/2273 (both end up as S11136526) or S1113 & 11/12/1917, but for the date ranges Alaa is likely using, this shouldn't be an issue.
@Zairja - oh, okay. That is a great point, but as you say, one in which the user may never find himself. It is these cases thought that teach you to be a better programmer / formula writer.
|
1

If you are willing to entertain a non-VBA solution, then you can use this implementation, which basically uses this formula:

=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+ IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "")

There are several VBA methods, which really depend on how comfortable you are with the language and how efficient you want your solution to be. Off the top of my head:

1) filter the list with both criteria, then return the relevant value in whatever row is visible (if any)

2) sort your data by those two columns (first by serial, then date), then perform searches (you'd probably want to call built-in functions like MATCH)

Improve this answer

1 Comment

Thanks a lot Zaiirja. I will put all this into consideration. Thanks for your valuable comments too.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.