Lookup in an Array
Steve
an array and then return the information in a cell that is in a column
which is the same row that the found item occurs?....see below
FIND CUST SPEC TC1 TC2 TC3 TC4 TC5 TC6
row 1 CU227 AAA 24409 CU281 CU282 CU283 CU284 CU285 CU286
row 2 CU024 DDD 24444 CA952
row 3 CU284 WWW 24435 CU024
row 4 CA977 RRR 24467 CU281 CU282 CU283 CU284 CU285 CU286
row 5 CU242 VVV 24488 CA952 CA954 CA977
row 6 CU248 YYY 24463 CU210
row 7 CU254 OOO 24498 CU227 CU289
row 8 CU241 MMM 24434 CU241 CU242 CU244 CU248
row 9 CA952 PPP 24427 CU254
For example: If I do a find on the item "CU248" (listed in row 6
under the "FIND" column) from the information listed under the columns
labeled "TC1" through "TC6", I need to be able to return the "CUST"
and the "SPEC" that appear in the same row where "CU248" is found. In
this case the find on "CU248" should result in "MMM" for the "CUST"
and "24434" for the "SPEC".
Please help,
Steve
Max
Assuming the data range given is in A2:I10
Name this range as say: MyTable
In a new sheet, assume you have in col A,
A2 downwards, the items CU248, etc put in:
B2: =VLOOKUP(TRIM($A2),MyTable,COLUMN(),FALSE)
Copy across B2:C2,
then down as many rows as there items in col A
cols B & C will return the CUST & SPEC from MyTable
for the items in col A
Steve <shoc...@fc-us.com> wrote in message
news:7d0c0fbf.03102...@posting.google.com...
Max
Leo Heuser
What happens, when the item to find exists in
more than one row? (e.g. CU284)
--
Best Regards
Leo Heuser
Excel MVP
Followup to newsgroup only please.
"Steve" <shoc...@fc-us.com> skrev i en meddelelse
news:7d0c0fbf.03102...@posting.google.com...
Steve
Leo,
Preferably...I would like it to give me what is in the "CUST" and the
"SPEC" that appear in the same row where the item is found for each
instance. However, if that is not possible...I'll take the first
instance that it shows up in the table. Sorry for not making that
distinction in my original posting.
Thanks,
Steve
Alan Beban
http://home.pacbell.net/beban are available to your workbook, and
assuming your data is in A1:J10:
=OFFSET(INDIRECT(ArrayMatch("CU284",$E$2:$J$10,"A",4)),0,-(ColumnVector(ArrayMatch("CU284",$E$2:$J$10),2))-1-(1-COLUMN(A1)))
array entered into a column long enough to accommodate the output (in
this case 9 rows), and filled right one column, will return the list of
customers and specs and #N/A in the extra cells.
Alan Beban
Leo Heuser
Not exactly a beauty but the following formulae
seems to do the job.
Assuming headings (Cust, Spec, TC1 through 6) in B1:i1
and data in B2:i50.
Heading "Cust" in L2 and heading "Spec" in M2.
L1 is used for entering the item to look up.
I have also assumed, that all data in B2:B50 (Cust) is distinct.
In L3 enter this arrayformula:
=INDEX($B$2:$B$50,SMALL((COUNTIF(OFFSET($D$2:$I$2,
ROW($A$2:$A$50)-ROW($A$2),0,1),$L$1))*(ROW($A$2:$A$50)-
ROW($A$2)+1),ROW()-ROW($L$3)+1+FREQUENCY((COUNTIF(
OFFSET($D$2:$I$2,ROW($A$2:$A$50)-ROW($A$2),0,1),$L$1))*
(ROW($A$2:$A$50)-ROW($A$2)+1),0)))
The formula must be entered with <Shift><Ctrl><Enter> also if
edited later. If done correctly, Excel will display the formula in
the formula bar enclosed in curly brackets { } Don't enter these
brackets yourself.
In M3 enter this formula:
=INDEX($B$2:$C$50,MATCH(L3,$B$2:$B$50,0),2)
Select L3:M3 and copy them down with the fill handle (the little
square in the lower right corner of the selection) as far as
needed (the maximum number of times a distinct item can exist
in the TC1 - TC6 range).
Cells not returning an answer will contain the #NUM! error.
Alan Beban
> I have also assumed, that all data in B2:B50 (Cust) is distinct.
For what it's worth, the formula I previously posted, which depends on
the downloadable add-in, does not have this restriction. I wonder if
Leo's formula can be modified to eliminate it.
Alan Beban
Harlan Grove
>Leo Heuser wrote:
>>I have also assumed, that all data in B2:B50 (Cust) is distinct.
>
>For what it's worth, the formula I previously posted, which depends on
>the downloadable add-in, does not have this restriction. I wonder if
>Leo's formula can be modified to eliminate it.
Either modify Leo's formula or just mention that it has a restriction that your
formula doesn't. Spare us rhetorical questions!
As it happens, it's possible to use a different approach involving only built-in
functions. If the OP's data were in A1:I10 ('FIND' in A1), the customer code
sought in either a range or string expression named CC, and the topmost matching
CUST and SPEC col results returned in cells N1 and O1, respectively, enter the
followin array formula in N1:O1.
=INDEX($B$2:$C$10,SMALL((MMULT(--($D$2:$I$10=CC),
TRANSPOSE(COLUMN($D$2:$I$10)))>0)*(ROW($D$2:$I$10)-CELL("Row",$D$2:$I$10)+1)
+(MMULT(--($D$2:$I$10=CC),TRANSPOSE(COLUMN($D$2:$I$10)))=0)*65537,
ROW()-ROW($N$1)+1),{1,2})
Select N1:O1 and fill down until it gives #REF! errors. Multiple instances of CC
in the same row (so in different TC# columns) are treated as a single match, but
instances of CC in different rows (whatever TC# column(s)) are treated as
different multiple matches.
--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.
Leo Heuser
In L3 arrayenter (<Shift><Ctrl><Enter>)
=INDEX(B$2:B$50,SMALL((COUNTIF(OFFSET($D$2:$I$2,
ROW($A$2:$A$50)-ROW($A$2),0,1),$L$1))*(ROW($A$2:$A$50)-
ROW($A$2)+1),ROW()-ROW($L$3)+1+FREQUENCY((COUNTIF(
OFFSET($D$2:$I$2,ROW($A$2:$A$50)-ROW($A$2),0,1),$L$1))*
(ROW($A$2:$A$50)-ROW($A$2)+1),0)))
Copy L3 to M3 with the fill handle.
Copy L3:M3 down with the fill handle.
The above formula is the one, I provided for my first answer, the
only difference being, that B is relative in B$2:B$50
Leo Heuser
"Alan Beban" <alannos...@pacbell.net> skrev i en meddelelse
news:3F9FF669...@pacbell.net...
Alan Beban
wasn't a rhetorical question.
I try to keep a mini-library of built-in formulas that obviate the need
for the array functions add-in; this is a helpful addition.
Alan Beban
Leo Heuser wrote:
> To eliminate the restriction:
>
> In L3 arrayenter (<Shift><Ctrl><Enter>)
>
> =INDEX(B$2:B$50,SMALL((COUNTIF(OFFSET($D$2:$I$2,ROW($A$2:$A$50)-ROW($A$2),0,1),$L$1))*(ROW($A$2:$A$50)-ROW($A$2)+1),ROW()-ROW($L$3)+1+FREQUENCY((COUNTIF(OFFSET($D$2:$I$2,ROW($A$2:$A$50)-ROW($A$2),0,1),$L$1))*> (ROW($A$2:$A$50)-ROW($A$2)+1),0)))
Steve
Sorry, for not being able to get to the internet yesterday after I
left work to clear up any of the questions that were popping up on my
posting. Many thanks to everyone for the generous reccomendations. I
will try some of those out today...
I'm a bit of novice when it comes to anything more than a simple
"If..." formula. Let me try once more to make sure everyone knows
exactly what I was after...here goes
Can somebody tell me how to look up a list of items in a column from
an array and then return the information in a cell that is in a column
which is the same row that the found item occurs?....see below
FROM:
FIND CUST SPEC TC1 TC2 TC3 TC4 TC5 TC6
row 1 CU227 AAA 24409 CU281 CU282 CU283 CU284 CU285 CU286
row 2 CU024 VVV 24444 CA952
row 3 CU284 WWW 24435 CU024
row 4 CA977 AAA 24467 CU281 CU282 CU283 CU284 CU285 CU286
row 5 CU242 VVV 24488 CA952 CA954 CA977
row 6 CU248 YYY 24463 CU210
row 7 CU254 OOO 24498 CU227 CU289
row 8 CU241 MMM 24434 CU241 CU242 CU244 CU248
row 9 CA952 PPP 24427 CU254
For example: If I do a find on the item "CU284" (listed in row 3
under the "FIND" column) from the information listed under the columns
labeled "TC1" through "TC6", I need to be able to return the "CUST"
and the "SPEC" that appear in the same row where "CU284" is found for
all instances. In this case the find on "CU284" should result in
"AAA" for the "CUST" and "24409" and "24467" for the "SPEC".
Note: Items listed under the "CUST" column are not unique, where as
those listed under the "SPEC" column will be unigue.
The way I would like to see the result for this table to turn out
would be something like as follows, (perhaps on a separate worksheet):
FIND CUST SPEC
row 1 CA952 VVV 24444
row 2 CA952 VVV 24488
row 3 CA954 VVV 24488
row 4 CA977 VVV 24488
row 5 CU024 WWW 24435
row 6 CU210 YYY 24463
row 7 CU227 OOO 24498
row 8 CU241 MMM 24434
row 9 CU242 MMM 24434
row 10 CU244 MMM 24434
row 11 CU248 MMM 24434
row 12 CU254 PPP 24427
row 13 CU281 AAA 24409
row 14 CU281 AAA 24467
row 15 CU282 AAA 24409
row 16 CU282 AAA 24467
row 17 CU283 AAA 24409
row 18 CU283 AAA 24467
" " " "
I tried providing only a small sample of what I have. In reality, I
have roughly 1,250 rows of data like what is shown in the original
format above with approximately 30 columns of TC's. I was hoping
there might be some sort of macro that could do this for me. I was
able to do something with a suggestion someone sent from a similar
problem I had using the "INDIRECT" function and 55,000 rows. Really
slow...
Kind Regards,
Steve
Alan Beban
instance of a CU number in any row, either Leo Heuser's second formula,
Harlan Grove's formula or mine, using the functions from the
downloadable file, will work for you. If there is more than one such
instance per row, Harlan Grove's formula will work, ignoring each second
(third, fourth, etc.) occurrence, i.e., returning CUST and SPEC from
that row once; my formula will return each such occurrence separately;
and Leo Heuser's formula will throw up, returning quirky results.
Alan Beban
Steve
Thanks again for all of your help... I'll try the one you've
reccomended this evening...
Kind Regards,
Steve
Steve
Harlan,
I'm trying to use your built-in solution, however I must be doing
something wrong. I did everything you had written and ended with the
Control+Shift+Enter and still only came up with the "#NAME?" error.
Any ideas where I might be doing something wrong? Please advise...
Regards,
Steve
Harlan Grove
>I'm trying to use your built-in solution, however I must be doing
>something wrong. I did everything you had written and ended with the
>Control+Shift+Enter and still only came up with the "#NAME?" error.
>Any ideas where I might be doing something wrong? Please advise...
>Harlan Grove<hrl...@aol.com> wrote...
>>=INDEX($B$2:$C$10,SMALL((MMULT(--($D$2:$I$10=CC),
>>TRANSPOSE(COLUMN($D$2:$I$10)))>0)*(ROW($D$2:$I$10)-CELL("Row",$D$2:$I$10)+1)
>>+(MMULT(--($D$2:$I$10=CC),TRANSPOSE(COLUMN($D$2:$I$10)))=0)*65537,
>>ROW()-ROW($N$1)+1),{1,2})
Did you name the customer code you're searching for CC? If not, and you're
entering it in, say, cell X99, replace the CC instances in the formula above
with $X$99. Failure to define the name CC or #NAME? errors in the data range are
the only ways my formula would return #NAME?.
Steve
Alan,
Me again... I tried to use your downloadable array functions, however
I'm not quite clear on how to use them or which one I should be using.
Do I paste this function in one of the columns and copy it down?
When in ask for a range for the array what group of cells do I select.
The formula only gives me the "#NAME? error when I paste it in my
worksheet, even after I do the Control+Shift+Enter thing....still
learning...Thanks again for your assistance.
Regards,
Steve
Leo Heuser
in all ranges (CUST, SPEC and TC1-TC6), returning CUST
and SPEC for a row only once in case of duplicates in that row:
=INDEX(B$2:B$50,SMALL(ROUNDUP(COUNTIF(OFFSET($D$2:$I$2,
ROW($A$2:$A$50)-ROW($A$2),0,1),$L$1)/COLUMNS($D$2:$I$2),0)*
(ROW($A$2:$A$50)-ROW($A$2)+1),ROW()-ROW($L$3)+1+
FREQUENCY((COUNTIF(OFFSET($D$2:$I$2,ROW($A$2:$A$50)-
ROW($A$2),0,1),$L$1))*(ROW($A$2:$A$50)-ROW($A$2)+1),0)))
Still to be array-entered.
Leo Heuser
"Alan Beban" <alannos...@pacbell.net> skrev i en meddelelse
news:3FA13499...@pacbell.net...
>>>=INDEX(B$2:B$50,SMALL((COUNTIF(OFFSET($D$2:$I$2,ROW($A$2:$A$50)-ROW($A$2)
Steve
Leo,
So far yours was the only one I've managed to make work. Not that I'm
doubting the work that the others have done, it's just me being a
novice at Excel and having to muddle my way through things. However,
if you look at the posting I made back to Alan which was the 13th in
the string, I tried to better explain what I was after. Basically, I
needed each unique entity listed a row be re-routed in a column with
its corresponding "CUST" and "SPEC" beside it, i.e. transposing all of
the data into one single column. Any ideas on how to do this with
your formula would be greatly appreciated.
Thanks,
Steve
Alan Beban
your relevant workbook, in the VBE Editor, select Tools|References and
check that item in the list of libraries avaliable.
Alan Beban
Alan Beban
>Basically, I
> needed each unique entity listed a row be re-routed in a column with
> its corresponding "CUST" and "SPEC" beside it, i.e. transposing all of
> the data into one single column.
Unfortunately, I find the above incomprehensible.
Alan Beban
Steve
> "Leo Heuser" <leo.h...@adslhome.dk> wrote in message news:<OlpJV13n...@TK2MSFTNGP10.phx.gbl>...
Leo,
Correction to my last sentence of that last posting, "Basically, I
needed each unique entity listed in a row to be re-routed into a
single column of "TC#" with their corresponding "CUST" and "SPEC"
beside them, i.e. transposing all of the data into a total of three
columns, "TC#", "CUST" and "SPEC". Any ideas on how to do this with
anon...@discussions.microsoft.com
Spare us rhetorical questions!
That's classic Harlan folks. As rude and obnoxious as the
day is long.
Comes up with a mish-mash of trash that doesn't work then
he cuts and runs.
>-----Original Message-----
>"Steve" wrote...
>>I'm trying to use your built-in solution, however I must
be doing
>>something wrong. I did everything you had written and
ended with the
>>Control+Shift+Enter and still only came up with
the "#NAME?" error.
>>Any ideas where I might be doing something wrong?
Please advise...
>...
>>Harlan Grove<hrl...@aol.com> wrote...
>>>=INDEX($B$2:$C$10,SMALL((MMULT(--($D$2:$I$10=CC),
>>>TRANSPOSE(COLUMN($D$2:$I$10)))>0)*(ROW($D$2:$I$10)-CELL
("Row",$D$2:$I$10)+1)
>>>+(MMULT(--($D$2:$I$10=CC),TRANSPOSE(COLUMN($D$2:$I$10)))
=0)*65537,
>>>ROW()-ROW($N$1)+1),{1,2})
>
>Did you name the customer code you're searching for CC?
If not, and you're
>entering it in, say, cell X99, replace the CC instances
in the formula above
>with $X$99. Failure to define the name CC or #NAME?
errors in the data range are
>the only ways my formula would return #NAME?.
>
>--
>Never attach files.
>Snip unnecessary quoted text.
>Never multipost (though crossposting is usually OK).
>Don't change subject lines because it corrupts Google
newsgroup archives.
>.
>
Harlan Grove
>Harlan Grove<hrl...@aol.com> wrote...
>Spare us rhetorical questions!
>
>That's classic Harlan folks. As rude and obnoxious as the
>day is long.
No. Days are too short.
>Comes up with a mish-mash of trash that doesn't work then
>he cuts and runs.
Uh, moron, did you happen to read my follow-up to the OP?
http://groups.google.com/groups?selm=MEiob.25966%24cJ5.4230%40www.newsranger.com
Or did you miss it because you don't even know how to use CDO?
Since the OP hasn't responded, either the follow-up addressed the OP's
problem (which seems to have been due to not seeing the following statement
from my earlier response
http://groups.google.com/groups?selm=rIVnb.25799%24cJ5.4047%40www.newsranger.com
"the customer code sought in either a range or string expression named CC").
Then again, you never have let facts intrude upon your peculiar dementia, so
why start now?
Leo Heuser
With the new information, I realize, that it's quite a bit
of data to rearrange. Up to about 70000 instances of
my array formulae and with the size of the formula, I
don't think, that's the way to go. It will probably slow
Excel down to a crawl. Instead I have built a macro
for you to use, when rearranging the data. It's
extremely fast because data is transferred to an array,
processed in the array and put back in the workbook
at the end. With the amount of data you have, it will
probably do the job in seconds!
Edit the lines
SourceSheet = "Sheet1"
SourceCells = "A2:AG1300" 'Cust + Spec + all TcColumns. No headings.
DestSheet = "Sheet2"
DestCell = "A2" 'A1:C1 is used for headings
to reflect the actual setup of your data.
There's no column for FIND in SourceCells. CUST *must*
be in the first column, and SPEC *must* be in the second,
all other columns contain TC-data.
Bon voyage, and let me know how it worked on your
dataset :-)
--
Best Regards
Leo Heuser
Excel MVP
Followup to newsgroup only please.
Sub RearrangeData()
'Leo Heuser, 31-10-2003
Dim Counter As Long
Dim DataRange As Range
Dim DataRangeValue As Variant
Dim DestSheet As String
Dim DestCell As String
Dim ElementsInTcRange As Long
Dim GetValue As Long
Dim Headings As Variant
Dim lColumn As Long
Dim lRow As Long
Dim NumberOfHeadings As Long
Dim ResultArray As Variant
Dim SourceCells As String
Dim SourceSheet As String
Dim TCRange As Range
SourceSheet = "Sheet1"
SourceCells = "A2:AG1300" 'Cust + Spec + all TcColumns. No headings.
DestSheet = "Sheet2"
DestCell = "A2" 'A1:C1 is used for headings
Headings = Array("FIND", "CUST", "SPEC")
Application.ScreenUpdating = False
Set DataRange = Sheets(SourceSheet).Range(SourceCells)
DataRangeValue = DataRange.Value
Set TCRange = DataRange.Columns(3). _
Resize(DataRange.Rows.Count, DataRange.Columns.Count - 2)
ElementsInTcRange = Application.WorksheetFunction.CountA(TCRange)
NumberOfHeadings = UBound(Headings) - LBound(Headings) + 1
ReDim ResultArray(1 To ElementsInTcRange, 1 To 3)
For lRow = LBound(DataRangeValue, 1) To UBound(DataRangeValue, 1)
For lColumn = 3 To UBound(DataRangeValue, 2)
If Not IsEmpty(DataRangeValue(lRow, lColumn)) Then
GetValue = GetValue + 1
ResultArray(GetValue, 1) = DataRangeValue(lRow, lColumn)
ResultArray(GetValue, 2) = DataRangeValue(lRow, 1)
ResultArray(GetValue, 3) = DataRangeValue(lRow, 2)
End If
Next lColumn
Next lRow
With Worksheets(DestSheet)
.Activate
With .Range(DestCell)
With .Offset(-1, 0).Resize(1, NumberOfHeadings)
.Value = Headings
.Font.Bold = True
End With
.Resize(GetValue, 3).Value = ResultArray
.Sort _
key1:=Range(DestCell), _
header:=xlYes, _
order1:=xlAscending
End With
End With
Application.ScreenUpdating = True
End Sub
"Steve" <shoc...@fc-us.com> skrev i en meddelelse
news:7d0c0fbf.03103...@posting.google.com...
anon...@discussions.microsoft.com
>
Harlan Grove
><LOL>
Acronym for 'lots of luck', an expression of hopelessness, as in the chances
that you'll ever contribute anything worthwhile in this newsgroup or in the
wider world.
Indeed, LOL.
Steve
Leo,
I tried the macro just on my example that I had used, but must have
done something wrong because when I tried to run it nothing happened
to the destination sheet.
Here is what it looked like, maybe you can spot what was wrong with
it:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/1/2003 by Steve
SourceSheet = "Sheet1"
SourceCells = "A2:H10" 'Cust + Spec + all TcColumns. No headings.
DestSheet = "Sheet2"
DestCell = A2 'A1:C1 is used for headings
'
End Sub
Regards,
Steve
Steve
Leo,
You are totally awesome...this is exactly what I was looking for...
It worked wonderfully.
Thanks again,
Steve
Leo Heuser
Thanks for the feedback! It's appreciated.
LeoH
"Steve" <shoc...@fc-us.com> skrev i en meddelelse
news:7d0c0fbf.03110...@posting.google.com...
Steve
> "Leo Heuser" <leo.h...@adslhome.dk> wrote in message news:<uGyexCGo...@tk2msftngp13.phx.gbl>...
Leo,
You can scratch this posting that I had started, I thought I had
stopped it in time. It was just after this when I had realized that
there was a lot more to the macro in the posting that you had put
together that was part of a continuation to that screen. Again, it
worked beautifully.
Thanks again,
Steve
Steve
Harlan,
Sorry about not getting back to you sooner...busy with kids and
halloween..
Anyway, I think I'm almost there with your formula. I wanted to see
if I could get everyone's solutions to work one way or the other. I
went ahead and named the range "A2:A10" with "CC" as you mentioned,
but now I get the following results in "N01:O13"
MMM MMM
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#REF! #REF!
#NUM! #NUM!
#NUM! #NUM!
#NUM! #NUM!
#NUM! #NUM!
Obviously I'm still screwing it up somewhere...Any ideas?
Also, sorry about the negative outside interference, I don't believe
all that is necessary unless they have something contstructive to add,
maybe that's why they remain anonymous
Regards,
Steve
Steve
Alan,
Sorry, for not getting back sooner, busy weekend here... Well I did
manage to get your add-in file into my workbook and try out the
formula. Here is what I got as a result:
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
AAA 24467
I ended up with the same results for all 9 rows, which ended up being
the "SPEC" for the second instance that "CU284" (in the formula)
showed up in the array (i.e. row 1 has "SPEC" 24409 and row 4 has
"SPEC" 24467). I never got any of the #N/A results that you had
mentioned. I must still have something not quite right in my
worksheet. Any ideas, because I really would like to see how this one
works when I've done it right?
Here is the formula I used for the first column:
=OFFSET(INDIRECT(ArrayMatch("CU284",$C$2:$H$10,"A",4)),0,-(ColumnVector(ArrayMatch("CU284",$C$2:$H$10),2))-1-(1-COLUMN(A1)))
FOR This Data:
AAA 24409 CU281 CU282 CU283 CU284 CU285 CU286
VVV 24444 CA952
WWW 24435 CU024
AAA 24467 CU281 CU282 CU283 CU284 CU285 CU286
VVV 24488 CA952 CA954 CA977
YYY 24463 CU210
OOO 24498 CU227 CU289
MMM 24434 CU241 CU242 CU244 CU248
PPP 24427 CU254
Regards,
Steve
Alan Beban
> FOR This Data:
>
> AAA 24409 CU281 CU282 CU283 CU284 CU285 CU286
> VVV 24444 CA952
> WWW 24435 CU024
> AAA 24467 CU281 CU282 CU283 CU284 CU285 CU286
> VVV 24488 CA952 CA954 CA977
> YYY 24463 CU210
> OOO 24498 CU227 CU289
> MMM 24434 CU241 CU242 CU244 CU248
> PPP 24427 CU254
In what cells do you have that data?
Alan Beban
Steve
Alan,
The data is in columns A through H and rows 1 through 9. Unless I put
the column labels back in which would then make it rows 2 through 10.
Thus, if I were to add a "FIND" column under A, then everything would
shift to the right, being columns B through I. Below is exactly what
I ended up with when I was able to start seeing some results from the
modified formula I sent in my last posting...
Thanks,
Steve
Alan Beban
I originally indicated way back when, *array enter it* into the first
column, then fill across one more column.
Steve
Alan,
I must still be doing something wrong, because I still get the same
results, except now the "CU284" in your formula only gives me "#REF!",
but if I change it to one of the "TC" numbers in the other rows I get
something similar to what I had said I got in my last posting, (see
below). Perhaps, I'm not doing the "*array enter it*" thing properly.
Am I missing something?
Please advise,
Steve
By changing "CU284" in your formula to "CA977", I got:
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488
VVV 24488