vlookup or index and match?
Brian
a worksheet that I would like to bring information that
has the following. Here is sheet one
Account City Address
Carstore Los Angeles 123 anystreet
Carstore Los Angeles 789 avenue
on sheet two, I have just the Account and City. I would
like to know if there is a formula available that would
bring in the addresses into column C and D
Account City Address 1 Address 2
Carstore Los Angeles 123 anystreet 789 avenue
I can use vlookup to bring in the first address into
column C, but how can I get the second address to drop
into column D. Is this possible without modifying the
first sheet? TIA
Harlan Grove
If the table in the first workeheet were named Tbl, then the first three columns
of the table on the second worksheet would be as simple as entering the
following formulas in these cells.
Account
=INDEX(Tbl,1,1)
City
=INDEX(Tbl,1,2)
Address
=INDEX(Tbl,1,3)
Now I need to assume that your table is sorted in ascending order first by
Account and then by City, The address column wouldn't need to be sorted. If the
third =Tbl cell, the one evaluating to the first address of the topmost record
in Tbl, were named X, then the second address for that record would be given by
=IF(COLUMN()-COLUMN(X)<SUMPRODUCT(COUNTIF(OFFSET(X,0,-2),INDEX(Tbl,0,1))
*COUNTIF(OFFSET(X,0,-1),INDEX(Tbl,0,2))),INDEX(Tbl,COLUMN()-COLUMN(X)+1,3),"")
Fill this right as far as needed. Then the second record in the derived table
could be given by
Account
=INDEX(Tbl,SUMPRODUCT(COUNTIF(OFFSET(X,0,-2,ROW()-ROW(X),1),
INDEX(Tbl,0,1))*COUNTIF(OFFSET(X,0,-1,ROW()-ROW(X),1),INDEX(Tbl,0,2)))+1,1)
City
=INDEX(Tbl,SUMPRODUCT(COUNTIF(OFFSET(X,0,-2,ROW()-ROW(X),1),
INDEX(Tbl,0,1))*COUNTIF(OFFSET(X,0,-1,ROW()-ROW(X),1),INDEX(Tbl,0,2)))+1,2)
1st Address
=IF(COLUMN()-COLUMN(X)<SUMPRODUCT(COUNTIF(OFFSET(X,ROW()-ROW(X),-2),
INDEX(Tbl,0,1))*COUNTIF(OFFSET(X,ROW()-ROW(X),-1),INDEX(Tbl,0,2))),
INDEX(Tbl,COLUMN()-COLUMN(X)+1+SUMPRODUCT(COUNTIF(OFFSET(X,0,-2,
ROW()-ROW(X),1),INDEX(Tbl,0,1))*COUNTIF(OFFSET(X,0,-1,ROW()-ROW(X),1),
INDEX(Tbl,0,2))),3),"")
Fill the 1st Address cell right as far as needed. Then copy the derived second
record and paste into the rows below it, down as far as needed.
There's a lot of redundancy in these formulas, so you'd be better off using
additional columns to calculate frequenty used values once. If the derived table
began with topmost Account in the original table in cell C1 on another
worksheet, then enter the following formulas.
A1:
1
B1:
=SUMPRODUCT(COUNTIF(C1,INDEX(Tbl,0,1))*COUNTIF(D1,INDEX(Tbl,0,2)))
C1:
=Index(Tbl,1,1)
D1:
=INDEX(Tbl,1,2)
E1:
=IF(COLUMN()-4<=$B1,INDEX(Tbl,$A1+COLUMN()-5,3),"")
A1:
=A1+B1
Fill E1 right as far as needed, say to Z1. Then copy B1:Z1 and paste into B2:Z2.
Then copy A2:Z2 and paste into columns A to Z from row 3 down through as many
rows as needed.
In both cases, when you have the derived table filled out, copy it and
paste-special as values onto itself. In the second case, delete columns A and B.
If the original table isn't sorted, then if you have Windows Script Host
installed (if you have Internet Explorer 5.0 or higher installed, you have
this), a macro would be the best idea.
'---- begin VBA ----
Sub foobar()
Dim aa As Object, kv() As String, tkv As String, r As Range, tbl As Range
Dim i As Long, j As Long, k As Long, n As Long
Set aa = CreateObject("Scripting.Dictionary")
Set tbl = Range("Tbl")
ReDim kv(1 To tbl.Rows.Count)
k = 0
n = 1
For Each r In tbl.Rows
tkv = r.Cells(1, 1).Value & Chr(127) & r.Cells(1, 2).Value
If aa.Exists(tkv) Then
aa(tkv) = aa(tkv) + 1
aa.Add tkv & Chr(127) & aa(tkv), r.Cells(1, 3)
If aa(tkv) > n Then n = aa(tkv)
Else
k = k + 1
kv(k) = tkv
aa.Add tkv, 1
aa.Add tkv & Chr(127) & 1, r.Cells(1, 3)
End If
Next r
ReDim Preserve kv(1 To k)
Set tbl = ActiveCell.Resize(k, n)
For i = 1 To k
tkv = kv(i)
j = InStr(1, tkv, Chr(127))
tbl.Cells(i, 1).Value = Left(tkv, j - 1)
tbl.Cells(i, 2).Value = Mid(tkv, j + 1)
n = aa(tkv)
For j = 1 To n
tbl.Cells(i, 2 + j).Value = aa(tkv & Chr(127) & j)
Next j
Next i
Erase kv
Set aa = Nothing
End Sub
'---- end VBA ----
--
Public Service Announcements:
1. Don't attach files to postings in this newsgroup.
2. Learn how to snip unnecessary text from quoted material. Indiscriminate
quoting wastes more bandwidth than file attachments.
Peter A
Hi Brian
No you'll have to alter the first sheet. The reference
cell must be unique. The problem seems to be that there
are two stores of the same company. The solution is to
give each one an ID. say CS1, CS2. Then you can use the ID
to call up the data.
regards Peter
Dave Peterson
It uses the almost the same syntax as the =vlookup() function. But it always
uses "false" as the 4th argument--no matter what you type.
Select a range (single column/single row) with enough cells to fill in your data
(any cells not used will appear empty).
Then type in your formula:
=mvlookup(a1,sheet2!$a$1:$c$999,3,false)
(mvlookup = multiple Vlookup)
but hit ctrl-shift-enter instead of enter. If you do it correctly, excel will
wrap curly brackets {} around the formula.
If you use a single cell and you're returning numbers, you can even do this:
=sum(mvlookup(a1,sheet2!$a$1:$c$999,3,false))
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Option Explicit
Function mVlookup(lookupValue, tableArray As Range, colIndexNum As Long, _
Optional NotUsed As Variant) As Variant
Dim initTable As Range
Dim myRowMatch As Variant
Dim myRes() As Variant
Dim initTableCols As Long
Dim i As Long
Dim ubound_myRes As Long
Set initTable = tableArray
initTableCols = initTable.Columns.Count
myRowMatch = Empty
i = 0
Do
myRowMatch = Application.Match(lookupValue, initTable.Columns(1), 0)
If IsError(myRowMatch) Then
Exit Do
Else
i = i + 1
ReDim Preserve myRes(1 To i)
myRes(i) = initTable(1).Offset(myRowMatch - 1, colIndexNum - 1)
If initTable.Rows.Count <= myRowMatch Then
Exit Do
End If
On Error Resume Next
Set initTable = initTable.Offset(myRowMatch, 0) _
.Resize(initTable.Rows.Count - myRowMatch, _
initTableCols)
On Error GoTo 0
If initTable Is Nothing Then
Exit Do
End If
End If
Loop
If i = 0 Then
mVlookup = CVErr(xlErrNA)
Exit Function
End If
If (Application.Caller.Count) = 1 Then
mVlookup = myRes
Else
If (UBound(myRes) - LBound(myRes) + 1 <= Application.Caller.Count) Then
ubound_myRes = UBound(myRes)
ReDim Preserve myRes(1 To Application.Caller.Count)
For i = ubound_myRes + 1 To Application.Caller.Count
myRes(i) = ""
Next i
If Application.Caller.Rows.Count = 1 Then
mVlookup = myRes
Else
mVlookup = Application.Transpose(myRes)
End If
Else
mVlookup = CVErr(xlErrRef)
End If
End If
End Function
--
Dave Peterson
ec3...@msn.com
Max
really great.
All that's needed is to name the range "Tbl", select where
to place the output and then run the macro. Can see a
couple of possible applications for this one...
Btw, just curious, what does 'foobar' stand for? <g>
From: Harlan Grove <hrl...@aol.com>
> If the original table isn't sorted, then if you have
Windows Script Host installed (if you have Internet
Explorer 5.0 or higher installed, you have this), a macro
would be the best idea.
>
>
> '---- begin VBA ----
> Sub foobar() .....
Max
A good keep-aside for "Multiple Vlookup" applications.
Dave Peterson
Max
techniques in Harlan's response <g>. But his is a sub,
unlike yours, a UDF. Both are v.useful! cheers.
>.
>
Alan Beban
> Btw, just curious, what does 'foobar' stand for? <g>
Not sure what Harlan Grove means to denote by it, but there is a
commonly understood acronym, fubar, that stands for F----d Up Beyond All
Recall.
Alan Beban
Harlan Grove
...
>Btw, just curious, what does 'foobar' stand for? <g>
http://www.webopedia.com/TERM/F/foobar.html
Max
Max
From: Harlan Grove <hrl...@aol.com>
>"Max" <demec...@yahoo.com> wrote...
>....
>>Btw, just curious, what does 'foobar' stand for? <g>
>....
Stephen Dunn
=IF(COLUMN(C2)-COLUMN($C2)+1>SUMPRODUCT((Accounts=Acc)*
(Cities=City)),"",INDEX(Addresses,SMALL(IF((Accounts=Acc)*
(Cities=City),ROW(Addresses)-CELL("ROW",Addresses)+1),
COLUMN(C2)-COLUMN($C2)+1)))
Accounts, Cities, and Addresses are absolute references to the columns of
the table on sheet 1.
Type into C2 of sheet2, and hold Ctrl+Shift when you press enter, then copy
across and down as far as necessary.
Steve D.
"Brian" <babs...@earthlink.net> wrote in message
news:031401c31b2a$26908e10$a601...@phx.gbl...
Myrna Larson
"One last note: hackers never use foobar to mean fubar"
but I have real problems believing the former isn't derived from the latter. Do you have any
information re etymology of this term? It isn't in my Historical Dictionary of American Slang.
Harlan Grove
>Yeah, they say
>
> "One last note: hackers never use foobar to mean fubar"
>
>but I have real problems believing the former isn't derived from the latter.
>Do you have any information re etymology of this term? It isn't in my
>Historical Dictionary of American Slang.
Does no one do their own web searches anymore?
http://info.astrian.net/jargon/terms/f/foo.html
Myrna Larson
waste more time on this, and I suspected you had more information than originally provided, the
quickest alternative was to ask you. Sorry if I imposed.
Myrna Larson
On Fri, 16 May 2003 18:47:38 GMT, Harlan Grove<hrl...@aol.com> wrote:
Dave Peterson
(sigh)
Max wrote:
>
> I might have missed it, too, given the wealth of
> techniques in Harlan's response <g>. But his is a sub,
--
Dave Peterson
ec3...@msn.com
Harlan Grove
url in a post I read in another newsgroup.