Using the MATCH command

[Tenspeed39355]

Good morning guys. This might not be the command I need to use so this is what I need to do. I have some fund symbols in column F and some fund symbols in column A. I need the command to fund the symbol in column a and then give me the cell number it is in. If the symbol in column F3 is ABC I want to look over in column A, find ABC and give me the cell location in column a the symbol is in. Thanks for your time with this mess.
Max

[Bob Phillips]

=ADDRESS(MATCH(F3,A:A,0),1)

[Charlize]

Probably a little bit overbloated but anyway ...
Sub locate_Cell_adress()
Dim rng As Range, result As Range
Dim firstaddress As String, vmessage As String
Dim lrow As Long
lrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
Set rng = Worksheets(1).Range("A1:A" & lrow)
vmessage = "Fund '" & ActiveCell.Value & "' was ..." & vbCrLf
With rng
Set result = .Find(ActiveCell.Value, LookIn:=xlValues)
If Not result Is Nothing Then
firstaddress = result.Address
Do
vmessage = vmessage & "- Located at : " & result.Address
Set result = .FindNext(result)
Loop While Not result Is Nothing And result.Address <> firstaddress
End If
End With
MsgBox vmessage, vbInformation, "Requested info ..."
End SubCharlize

[Tenspeed39355]

I have one more project to work on. This is going to be hard for me to explain but here goes.
In column A I have numbers from 1 to 600
In column B I have stock symbols from 1 to 600
In column C I have data for the symbols in column B
In column D I have more stock symbols from 1 to 600
In column E I have numbers from 1 to 600
In column F I have the data for the symbols in column D
The data in column C is the result of long term
The data in column F is for short term.
My question is there a way that I can put a number next to the symbols in column B from column D.
Now that I have you confused how about the following
The end result is the following.
Lets say the fund xxx in column B is in first place and in 10th place in column D. I want to be able to add the two places together for a sum of both. This will tell me the fund xxx has the lowest sum showing that it is the best fund to buy. Thanks for helping with this mess.
How do I get myself in this mess. LOL
Max:banghead:

[mdmackillop]

Hi Max,
Can you post a sample spreadsheet?

[Aussiebear]

I've developed a simple spreadsheet loosely based on Tenspeed's requirements, and according to Contextures simplified lesson on Match and Index, I tried to write a formula to sum the results of a combined Index Match.

While it seems to work for the very first cell, when I dragged it down the column it had been very erratic in the answers supplied. 3 of the 10 are correct.

[Aussiebear]

Found a couple of errors in the formula. ( with Bob's help)

[geekgirlau]

... or

=SUMPRODUCT(($B$2:$B$11=B2)*$A$2:$A$11)+SUMPRODUCT(($D$2:$D$11=B2)*$E$2:$E$ 11)

[Bob Phillips]

There is no need for SP in single conditions

=SUMIF($B$2:$B$11,B2,$A$2:$A$11)+SUMIF($D$2:$D$11,B2,$E$2:$E$11)

[Aussiebear]

Hmmm.... seems my idea got thrown out on the rubbish heap.

:wot

Looks like this means a long a meaningful conversation with a beer in each hand.

[Bob Phillips]

I don't think so. There is no need to do summing when there is a single item in a list, as there should be in a stock code list, INDEX/MATCH (or VLOOKUP depending upon the data structure) is the correct way to go IMO. I was just pointiung oiut to gg that SP is total overkill on a single condition.

And if the OP ever comes back, your suggestion gives him a solution, or a lead to explain why it doesn't fit.

[Aussiebear]

(Hic..) I would have replied earlier but it meant I had to put the beers down.

:beerchug:

And as any good Aussie knows..... that's taboo

[Charlize]

Another overbloated way : Will sort the array on number and stockname ...
Option Base 0
Public Sub Best_Value_Quote()
Dim rng As Range, startrng As Range, result As Range
Dim vQuotes() As String
Dim cell As Range
Dim vsum As Long
Dim firstaddress As String, vmessage As String
Dim lrow As Long
Dim vno As Long

Dim ws As Worksheet

lrow = Worksheets(1).Range("B" & Rows.Count).End(xlUp).Row
Set rng = Worksheets(1).Range("D2:D" & lrow)
Set startrng = Worksheets(1).Range("B2:B" & lrow)
ReDim Preserve vQuotes(lrow - 1, 2)

vmessage = "Funds scores : (pick the best) ..." & vbCrLf
vno = 0

For Each cell In startrng
vsum = cell.Offset(0, -1).Value
With rng
Set result = .Find(cell.Value, LookIn:=xlValues)
If Not result Is Nothing Then
firstaddress = result.Address
Do
vsum = vsum + result.Offset(0, 1).Value
Set result = .FindNext(result)
vQuotes(vno, 1) = vsum
vQuotes(vno, 2) = cell.Value
Loop While Not result Is Nothing And result.Address <> firstaddress
End If
End With
vsum = 0
vno = vno + 1
Next cell
'*** put values of array in new sheet for sorting and refill array
' with sorted list
Application.ScreenUpdating = False
Worksheets.Add after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = "TempArray"
Set ws = Worksheets("TempArray")
vno = 1
For Each cell In startrng
ws.Cells(vno, 1).Value = vQuotes(vno - 1, 1)
ws.Cells(vno, 2).Value = vQuotes(vno - 1, 2)
vno = vno + 1
Next cell
'*** Sort the list based on no + stockname
Range("A1:B" & lrow - 1).Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range( _
"B1"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
'*** Refill array with sorted list
vno = 0
For Each cell In startrng
vQuotes(vno, 1) = ws.Cells(vno + 1, 1).Value
vQuotes(vno, 2) = ws.Cells(vno + 1, 2).Value
vno = vno + 1
Next cell
'*** Remove the extra worksheet TempArray
Application.DisplayAlerts = False
ws.Delete
Worksheets(1).Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'*** create the message to be displayed with sorted list
vno = 0
For Each cell In startrng
vmessage = vmessage & "- " & vQuotes(vno, 2) & " : " & vQuotes(vno, 1) & vbCrLf
vno = vno + 1
Next cell
MsgBox vmessage, vbInformation
End SubCharlize