trying an alternative to a major complex serious of
columns.
I was reading that in formulas you can have 2 column entryies
return int the same cell for exaple
=VLOOKUP(G5,Sheet1!$A$1:$J$50000,{3,4},FALSE)
In theory the would display the 3rd and forth column in one cell
but I cannot get it to work.
is some one giving me some bad info, or could
you have several columns in onecell

No, I think that is bad info. To get two results returned to the same cell,
you'd need to concatenate two VLOOKUPs together. Like this:

=VLOOKUP(G5,Sheet1!$A$1:$J$50000,3,FALSE) &
VLOOKUP(G5,Sheet1!$A$1:$J$50000,4,FALSE)

HTH
Elkar


"Dylan @ UAFC" wrote:

trying an alternative to a major complex serious of
columns.
I was reading that in formulas you can have 2 column entryies
return int the same cell for exaple
=VLOOKUP(G5,Sheet1!$A$1:$J$50000,{3,4},FALSE)
In theory the would display the 3rd and forth column in one cell
but I cannot get it to work.
is some one giving me some bad info, or could
you have several columns in onecell

I understand what you are saying the other would be
just to easy.
The reason for the equastion is I can not have the #n/a
so I have been using =if(isna the duplication the vookup
formulas, then going on 13 coolumns out, super slow way to
do it.
I think however each column of data in the array is not signidigant
and there are form of sorting were data consolidaiton could happen.
Say I could bread it up were if I need to popluation 12 column over
I could considate taht data in 4 set of were 3 return = in one cell.
Do you think this would be any faster or am I jsut spinning my
wheels????????????
I gues I coudl considat the data in the array =a1&" "&a2 and
shorten the columen list down, but that would be the last resortn
any siggestion

"Elkar" wrote:

No, I think that is bad info. To get two results returned to the same cell,
you'd need to concatenate two VLOOKUPs together. Like this:

=VLOOKUP(G5,Sheet1!$A$1:$J$50000,3,FALSE) &
VLOOKUP(G5,Sheet1!$A$1:$J$50000,4,FALSE)

HTH
Elkar


"Dylan @ UAFC" wrote:

trying an alternative to a major complex serious of
columns.
I was reading that in formulas you can have 2 column entryies
return int the same cell for exaple
=VLOOKUP(G5,Sheet1!$A$1:$J$50000,{3,4},FALSE)
In theory the would display the 3rd and forth column in one cell
but I cannot get it to work.
is some one giving me some bad info, or could
you have several columns in onecell

No. You can't use that construction.

You might be thinking of something like this.

Select B1:E1

Type =VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,{2,3,4,5},FALSE ) into B1 and

CTRL + SHIFT + ENTER to enter as an array formula.

You could combine two lookups in one cell with one formula.

=VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,3,FALSE) & " " &
VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,4,FALSE)


Gord Dibben MS Excel MVP

On Tue, 16 Dec 2008 17:28:00 -0800, Dylan @ UAFC
wrote:

trying an alternative to a major complex serious of
columns.
I was reading that in formulas you can have 2 column entryies
return int the same cell for exaple
=VLOOKUP(G5,Sheet1!$A$1:$J$50000,{3,4},FALSE)
In theory the would display the 3rd and forth column in one cell
but I cannot get it to work.
is some one giving me some bad info, or could
you have several columns in onecell

Thank short cut on the filing the arrays out is great.
Do you have any suggestion for speed that would run better that
if(isna('vlookup forumula","vlookup formula")
When I add this to take away the #n/a's it
kills the speed

"Gord Dibben" wrote:

No. You can't use that construction.

You might be thinking of something like this.

Select B1:E1

Type =VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,{2,3,4,5},FALSE ) into B1 and

CTRL + SHIFT + ENTER to enter as an array formula.

You could combine two lookups in one cell with one formula.

=VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,3,FALSE) & " " &
VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,4,FALSE)


Gord Dibben MS Excel MVP

On Tue, 16 Dec 2008 17:28:00 -0800, Dylan @ UAFC
wrote:

trying an alternative to a major complex serious of
columns.
I was reading that in formulas you can have 2 column entryies
return int the same cell for exaple
=VLOOKUP(G5,Sheet1!$A$1:$J$50000,{3,4},FALSE)
In theory the would display the 3rd and forth column in one cell
but I cannot get it to work.
is some one giving me some bad info, or could
you have several columns in onecell

I do not understand
the {1,2,3,4}
will this take col ABCD in table array
and put them in the same cell
next
if I typed
{1, 2, 3, 4} would this put a space in the data
in the single cell
plesse advsie

"Gord Dibben" wrote:

No. You can't use that construction.

You might be thinking of something like this.

Select B1:E1

Type =VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,{2,3,4,5},FALSE ) into B1 and

CTRL + SHIFT + ENTER to enter as an array formula.

You could combine two lookups in one cell with one formula.

=VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,3,FALSE) & " " &
VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,4,FALSE)


Gord Dibben MS Excel MVP

On Tue, 16 Dec 2008 17:28:00 -0800, Dylan @ UAFC
wrote:

trying an alternative to a major complex serious of
columns.
I was reading that in formulas you can have 2 column entryies
return int the same cell for exaple
=VLOOKUP(G5,Sheet1!$A$1:$J$50000,{3,4},FALSE)
In theory the would display the 3rd and forth column in one cell
but I cannot get it to work.
is some one giving me some bad info, or could
you have several columns in onecell

By your use of relative and absolute references in your formula, I'm
assuming that you are copying your formula down a number of rows as well as
across numerous columns.
Since your lookup range is rather large (50,000 rows), the following
approach might save you some efficiency.

Let's perform a single search and use the results of that search as the
criteria for returning the entire row of data from the array.

Start with something like this in say H5 of Sheet2:

=IF(ISNA(MATCH(G5,Sheet1!A$1:A$50000,0)),"",MATCH( G5,Sheet1!A$1:A$50000,0))

If there is a match in the datalist A1 to J50000, this will return the row
number ... OR ... a blank cell if no match was found.

Copy this formula down as far as needed.

Now, in the formulas in the next columns over, we use the results of this
single search to return either data or empty cells.

In cell I5 enter:
=IF($H5="","",INDEX(Sheet1!$A$1:$J$50000,$H5,COLUM NS($A:B)))

Copy this formula across to Q5,
Then, select I5 to Q5, and copy that 9 cell selection down as far as needed.

This approach should improve the speed of your query.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Dylan @ UAFC" wrote in message
...
Thank short cut on the filing the arrays out is great.
Do you have any suggestion for speed that would run better that
if(isna('vlookup forumula","vlookup formula")
When I add this to take away the #n/a's it
kills the speed

"Gord Dibben" wrote:

No. You can't use that construction.

You might be thinking of something like this.

Select B1:E1

Type =VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,{2,3,4,5},FALSE ) into B1 and

CTRL + SHIFT + ENTER to enter as an array formula.

You could combine two lookups in one cell with one formula.

=VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,3,FALSE) & " " &
VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,4,FALSE)


Gord Dibben MS Excel MVP

On Tue, 16 Dec 2008 17:28:00 -0800, Dylan @ UAFC
wrote:

trying an alternative to a major complex serious of
columns.
I was reading that in formulas you can have 2 column entryies
return int the same cell for exaple
=VLOOKUP(G5,Sheet1!$A$1:$J$50000,{3,4},FALSE)
In theory the would display the 3rd and forth column in one cell
but I cannot get it to work.
is some one giving me some bad info, or could
you have several columns in onecell

I like the approach I will give it a shot and
see the performance results
maybe you can ehlp with another speed issue.
This spreedsheet data is basic driven off a unique
phone # that has several detial that encompass that
unquie value, however in the main data page there can
be duplicated phone entry.
This is purpose and intergity of the poject.
I am using the formula
=IF(COUNTIF($C$1:$C$25000,b1)1,FALSE,TRUE)
The performnace fo the function is terrrible. I was not
haveing to speed prob. with the vlookup, but when this
was added it killed the speed. From about a 2 second population
time of the cells to about 18 to 25 secondes with check for duplicated
formula
any sugestion there

"RagDyeR" wrote:

By your use of relative and absolute references in your formula, I'm
assuming that you are copying your formula down a number of rows as well as
across numerous columns.
Since your lookup range is rather large (50,000 rows), the following
approach might save you some efficiency.

Let's perform a single search and use the results of that search as the
criteria for returning the entire row of data from the array.

Start with something like this in say H5 of Sheet2:

=IF(ISNA(MATCH(G5,Sheet1!A$1:A$50000,0)),"",MATCH( G5,Sheet1!A$1:A$50000,0))

If there is a match in the datalist A1 to J50000, this will return the row
number ... OR ... a blank cell if no match was found.

Copy this formula down as far as needed.

Now, in the formulas in the next columns over, we use the results of this
single search to return either data or empty cells.

In cell I5 enter:
=IF($H5="","",INDEX(Sheet1!$A$1:$J$50000,$H5,COLUM NS($A:B)))

Copy this formula across to Q5,
Then, select I5 to Q5, and copy that 9 cell selection down as far as needed.

This approach should improve the speed of your query.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Dylan @ UAFC" wrote in message
...
Thank short cut on the filing the arrays out is great.
Do you have any suggestion for speed that would run better that
if(isna('vlookup forumula","vlookup formula")
When I add this to take away the #n/a's it
kills the speed

"Gord Dibben" wrote:

No. You can't use that construction.

You might be thinking of something like this.

Select B1:E1

Type =VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,{2,3,4,5},FALSE ) into B1 and

CTRL + SHIFT + ENTER to enter as an array formula.

You could combine two lookups in one cell with one formula.

=VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,3,FALSE) & " " &
VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,4,FALSE)


Gord Dibben MS Excel MVP

On Tue, 16 Dec 2008 17:28:00 -0800, Dylan @ UAFC
wrote:

trying an alternative to a major complex serious of
columns.
I was reading that in formulas you can have 2 column entryies
return int the same cell for exaple
=VLOOKUP(G5,Sheet1!$A$1:$J$50000,{3,4},FALSE)
In theory the would display the 3rd and forth column in one cell
but I cannot get it to work.
is some one giving me some bad info, or could
you have several columns in onecell

The formula posted doesnot put everything in one cell.

It puts results in the 4 cells B1:E1 that you pre-selected before
array-entering the formula.

It is just a shortcut to increase the lookup column index number across 4
columns.


Gord

On Tue, 16 Dec 2008 19:58:01 -0800, Dylan @ UAFC
wrote:

I do not understand
the {1,2,3,4}
will this take col ABCD in table array
and put them in the same cell
next
if I typed
{1, 2, 3, 4} would this put a space in the data
in the single cell
plesse advsie

"Gord Dibben" wrote:

No. You can't use that construction.

You might be thinking of something like this.

Select B1:E1

Type =VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,{2,3,4,5},FALSE ) into B1 and

CTRL + SHIFT + ENTER to enter as an array formula.

You could combine two lookups in one cell with one formula.

=VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,3,FALSE) & " " &
VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,4,FALSE)


Gord Dibben MS Excel MVP

On Tue, 16 Dec 2008 17:28:00 -0800, Dylan @ UAFC
wrote:

trying an alternative to a major complex serious of
columns.
I was reading that in formulas you can have 2 column entryies
return int the same cell for exaple
=VLOOKUP(G5,Sheet1!$A$1:$J$50000,{3,4},FALSE)
In theory the would display the 3rd and forth column in one cell
but I cannot get it to work.
is some one giving me some bad info, or could
you have several columns in onecell

AFAIK, that Countif() formula is relatively fast, for what you're looking to
accomplish.

I copied it to 45,000 cells on 2 XP machines,
one with XL02, and the other using XL2K.

Both took about 6 - 8 seconds.

Just now, on a Win98 machine with XL97, it took well over a minute.

So, I guess you can say it's resource intensive, really dependent on the
resources of the individual machine being utilized, since the Win98 machine
only has 384 MB of Ram.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dylan @ UAFC" wrote in message
...
I like the approach I will give it a shot and
see the performance results
maybe you can ehlp with another speed issue.
This spreedsheet data is basic driven off a unique
phone # that has several detial that encompass that
unquie value, however in the main data page there can
be duplicated phone entry.
This is purpose and intergity of the poject.
I am using the formula
=IF(COUNTIF($C$1:$C$25000,b1)1,FALSE,TRUE)
The performnace fo the function is terrrible. I was not
haveing to speed prob. with the vlookup, but when this
was added it killed the speed. From about a 2 second population
time of the cells to about 18 to 25 secondes with check for duplicated
formula
any sugestion there

"RagDyeR" wrote:

By your use of relative and absolute references in your formula, I'm
assuming that you are copying your formula down a number of rows as well
as
across numerous columns.
Since your lookup range is rather large (50,000 rows), the following
approach might save you some efficiency.

Let's perform a single search and use the results of that search as the
criteria for returning the entire row of data from the array.

Start with something like this in say H5 of Sheet2:


=IF(ISNA(MATCH(G5,Sheet1!A$1:A$50000,0)),"",MATCH( G5,Sheet1!A$1:A$50000,0))

If there is a match in the datalist A1 to J50000, this will return the
row
number ... OR ... a blank cell if no match was found.

Copy this formula down as far as needed.

Now, in the formulas in the next columns over, we use the results of
this
single search to return either data or empty cells.

In cell I5 enter:
=IF($H5="","",INDEX(Sheet1!$A$1:$J$50000,$H5,COLUM NS($A:B)))

Copy this formula across to Q5,
Then, select I5 to Q5, and copy that 9 cell selection down as far as
needed.

This approach should improve the speed of your query.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Dylan @ UAFC" wrote in message
...
Thank short cut on the filing the arrays out is great.
Do you have any suggestion for speed that would run better that
if(isna('vlookup forumula","vlookup formula")
When I add this to take away the #n/a's it
kills the speed

"Gord Dibben" wrote:

No. You can't use that construction.

You might be thinking of something like this.

Select B1:E1

Type =VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,{2,3,4,5},FALSE ) into B1 and

CTRL + SHIFT + ENTER to enter as an array formula.

You could combine two lookups in one cell with one formula.

=VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,3,FALSE) & " " &
VLOOKUP(A1,'Sheet1'!$A$1:$J$50000,4,FALSE)


Gord Dibben MS Excel MVP

On Tue, 16 Dec 2008 17:28:00 -0800, Dylan @ UAFC
wrote:

trying an alternative to a major complex serious of
columns.
I was reading that in formulas you can have 2 column entryies
return int the same cell for exaple
=VLOOKUP(G5,Sheet1!$A$1:$J$50000,{3,4},FALSE)
In theory the would display the 3rd and forth column in one cell
but I cannot get it to work.
is some one giving me some bad info, or could
you have several columns in onecell