Matching criteria to sum numbers

I am trying to get a list of 10 different reference numbers in 6 columns to refer back to a list of 500 different reference numbers in 1 seperate column , can this be done so as highlight the selection in the 1 seperate  column.

Many thanks in advance.

Richard

Mre detail, maybe an example workbook, would help.

Bob

hope the attached will help to see my query easier.

Regards

Richard

Richard

Is ther an error in the file? All the order numbers in column A are 500345162.

If you could clarify that I am sure you can do something with VLOOKUP and INDEX

Is there always a match or do we also need to handle that?

It seems like more qusetions than answers but I should be able to nail it with these answers over the weekend

Hi Nick

The order numbers in column A have been sorted into order but change as you scroll down.

They should match the numbers in the other columns.

Thanks for your help.

Richard

Richard

I am likely being very thick, but I still can't see it. I can see that the Order in A2 matches the number in F2 and therefore with Column F having the heading Preliminaries, that gets returned to E2.

If I then take A3, which has the same Order (500345162). That matches none of the numbers in F3:Q3 and therefore I don't know why you expect it to return Preliminaries in E3.

Sorry this is to'ing and fro'ing so much

Nick
 
A3 is still the same Order (500345162) as A2 and corresponds to column F Preliminaries and would return Preliminaries in E3.
 
For example A603 (500378260) corresponds to J3 and would return in E603 Window Installion.
 
Hoping this makes the problem easier to understand.
 
Many thanks for your help.
 
Richard
 

Richard

That makes it quite a bit more complicated, at least for me. Someone like Bob who does magic with Functions will chime in I'm sure. The solution I have come up with uses a UDF (User Defined Function). (as below)

'------------------Start Code---------------------
Public Function FindHeading(sOrder As String, rLookup As Range) As String
Dim rng As Range

Application.Volatile True

Set rng = rLookup.Find(sOrder)

If Not rng Is Nothing Then
    FindHeading = Range(Cells(1, rng.Column), Cells(1, rng.Column)).Value
    Exit Function
End If

FindHeading = "N/A"

End Function
'-------------------------------------End Code----------------------------------------

To implement this, open the workbook, press F11 and in that window go to 'Insert>Module'. Paste the code here and close the window (You will need to save the workbook and remember you will get a Macro warning when opening (or in Excel 2007 the security bar will show).

Without doubt a industry strength function would need more error checking, but for your use it works at least.

The function takes two parameters, the order number and the range that the numbers can be found in. To use, you just do exactly as you would with any built in function, so for example in E2 you would enter

=FindHeading(A2,$F$2:$Q$6748)

That returns preliminaries (or in fact whatever is in the heading directly above where the order number is found, so if you change that and re-calc, the references will update too.

As it is looking at so much data, there is a lag between making changes and the formula updating

Nick

Thanks very much for your time and help, I will try your function later on today and let you know how I go on.

Hopefully this will crack the problem.

Kindest regards

Richard

Nick

tried out the UDF, it worked brilliantly.

Very clever

Kindest regards

Richard