Hi,

I have a huge file with 10,000 records with like +25 rows and some of the
jobs are repeated several times. Using the Job as the reference in vlookup i
am trying to populate the data in a different worksheet, but vlookup just
takes the list row information of a perticular job but i want it to take the
data from all the lines for that job. EX:
Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want the data to populated in this way:
Jim dell
Jim toshiba
Jim lenovo
but its populating like:

Jim dell
Jim dell
Jim dell

Can someone help me on this? i found some formulas but those are for
relatively small data, but how wld i do it with such a huge data?

On Fri, 7 Oct 2005 07:48:07 -0700, "nick"
wrote:

Hi,

I have a huge file with 10,000 records with like +25 rows and some of the
jobs are repeated several times. Using the Job as the reference in vlookup i
am trying to populate the data in a different worksheet, but vlookup just
takes the list row information of a perticular job but i want it to take the
data from all the lines for that job. EX:
Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want the data to populated in this way:
Jim dell
Jim toshiba
Jim lenovo
but its populating like:

Jim dell
Jim dell
Jim dell

Can someone help me on this? i found some formulas but those are for
relatively small data, but how wld i do it with such a huge data?

Vlookup will only return one value.

Do you need a formula at all? Can't you simply Auto Filter the data in
place on the Job number column, and then filter again on the Name
column? Selecting "Jim" will filter out all Jim's equipment.

Depending what you want to do you could also use Advanced Filter to
copy the filtered records somewhere else.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi,

I have like 3000 job #'s...i cant filter each n every job number out. i need
a quicker way to do that. I thought vlookup would help but not when it have
repeated job numbers.

"Richard Buttrey" wrote:

On Fri, 7 Oct 2005 07:48:07 -0700, "nick"
wrote:

Hi,

I have a huge file with 10,000 records with like +25 rows and some of the
jobs are repeated several times. Using the Job as the reference in vlookup i
am trying to populate the data in a different worksheet, but vlookup just
takes the list row information of a perticular job but i want it to take the
data from all the lines for that job. EX:
Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want the data to populated in this way:
Jim dell
Jim toshiba
Jim lenovo
but its populating like:

Jim dell
Jim dell
Jim dell

Can someone help me on this? i found some formulas but those are for
relatively small data, but how wld i do it with such a huge data?

Vlookup will only return one value.

Do you need a formula at all? Can't you simply Auto Filter the data in
place on the Job number column, and then filter again on the Name
column? Selecting "Jim" will filter out all Jim's equipment.

Depending what you want to do you could also use Advanced Filter to
copy the filtered records somewhere else.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi,

Are you trying to extract a multi record subset of the main data set?
If so Vlookup is not your answer, it only returns one value.

My approach to similar tasks like this is to use a simple VBA looping
macro, which a) extracts a unique list of jobs, b) job by job filters
the records out, c) copies them somewhere, either a new sheet or new
workbook, and then d) moves on to the next job.

Obviously this is not an Excel function, but if you want to consider
this, post back and I'll suggest some code.

Rgds



On Fri, 7 Oct 2005 08:59:03 -0700, "nick"
wrote:

Hi,

I have like 3000 job #'s...i cant filter each n every job number out. i need
a quicker way to do that. I thought vlookup would help but not when it have
repeated job numbers.

"Richard Buttrey" wrote:

On Fri, 7 Oct 2005 07:48:07 -0700, "nick"
wrote:

Hi,

I have a huge file with 10,000 records with like +25 rows and some of the
jobs are repeated several times. Using the Job as the reference in vlookup i
am trying to populate the data in a different worksheet, but vlookup just
takes the list row information of a perticular job but i want it to take the
data from all the lines for that job. EX:
Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want the data to populated in this way:
Jim dell
Jim toshiba
Jim lenovo
but its populating like:

Jim dell
Jim dell
Jim dell

Can someone help me on this? i found some formulas but those are for
relatively small data, but how wld i do it with such a huge data?

Vlookup will only return one value.

Do you need a formula at all? Can't you simply Auto Filter the data in
place on the Job number column, and then filter again on the Name
column? Selecting "Jim" will filter out all Jim's equipment.

Depending what you want to do you could also use Advanced Filter to
copy the filtered records somewhere else.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi
Thanks for your offer. i am not that good with VBA, but if you could help me
i would really appreciate it. Basically what i am trying to do is, i have a
huge main file with 10,000 Job numbers with data associated to it. But i have
some selected 3000-4000 jobs that i am trying to populate the data for, from
that huge main file. but the problem is some jobs are repated as i mentioned
before with different data but the same job #. I hope this explained the
process clearly.



"Richard Buttrey" wrote:

Hi,

Are you trying to extract a multi record subset of the main data set?
If so Vlookup is not your answer, it only returns one value.

My approach to similar tasks like this is to use a simple VBA looping
macro, which a) extracts a unique list of jobs, b) job by job filters
the records out, c) copies them somewhere, either a new sheet or new
workbook, and then d) moves on to the next job.

Obviously this is not an Excel function, but if you want to consider
this, post back and I'll suggest some code.

Rgds



On Fri, 7 Oct 2005 08:59:03 -0700, "nick"
wrote:

Hi,

I have like 3000 job #'s...i cant filter each n every job number out. i need
a quicker way to do that. I thought vlookup would help but not when it have
repeated job numbers.

"Richard Buttrey" wrote:

On Fri, 7 Oct 2005 07:48:07 -0700, "nick"
wrote:

Hi,

I have a huge file with 10,000 records with like +25 rows and some of the
jobs are repeated several times. Using the Job as the reference in vlookup i
am trying to populate the data in a different worksheet, but vlookup just
takes the list row information of a perticular job but i want it to take the
data from all the lines for that job. EX:
Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want the data to populated in this way:
Jim dell
Jim toshiba
Jim lenovo
but its populating like:

Jim dell
Jim dell
Jim dell

Can someone help me on this? i found some formulas but those are for
relatively small data, but how wld i do it with such a huge data?

Vlookup will only return one value.

Do you need a formula at all? Can't you simply Auto Filter the data in
place on the Job number column, and then filter again on the Name
column? Selecting "Jim" will filter out all Jim's equipment.

Depending what you want to do you could also use Advanced Filter to
copy the filtered records somewhere else.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Fri, 7 Oct 2005 09:28:01 -0700, "nick"
wrote:

Hi
Thanks for your offer. i am not that good with VBA, but if you could help me
i would really appreciate it. Basically what i am trying to do is, i have a
huge main file with 10,000 Job numbers with data associated to it. But i have
some selected 3000-4000 jobs that i am trying to populate the data for, from
that huge main file. but the problem is some jobs are repated as i mentioned
before with different data but the same job #. I hope this explained the
process clearly.

Just seen Ashish's reply. If this meets your requirement excellent. If
not let me know and I'll suggest a VBA option.

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Hi,

Try this array formula (Ctrl+Shift+Enter)

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2))

$A$10 contains Jim. Now copy the formula down

A1:A7 would contain names. B2:B7 would contain company names (Dell, HP
etc.)

Hope this helps

Regards,

Ashish Mathur

"nick" wrote:

Hi,

I have a huge file with 10,000 records with like +25 rows and some of the
jobs are repeated several times. Using the Job as the reference in vlookup i
am trying to populate the data in a different worksheet, but vlookup just
takes the list row information of a perticular job but i want it to take the
data from all the lines for that job. EX:
Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want the data to populated in this way:
Jim dell
Jim toshiba
Jim lenovo
but its populating like:

Jim dell
Jim dell
Jim dell

Can someone help me on this? i found some formulas but those are for
relatively small data, but how wld i do it with such a huge data?

Hi Mathur,

If you dont mind can u explain the syntax that u used, cuz i need to use the
same syntax that u used with data taken from 2 different workbooks.

"Ashish Mathur" wrote:

Hi,

Try this array formula (Ctrl+Shift+Enter)

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2))

$A$10 contains Jim. Now copy the formula down

A1:A7 would contain names. B2:B7 would contain company names (Dell, HP
etc.)

Hope this helps

Regards,

Ashish Mathur

"nick" wrote:

Hi,

I have a huge file with 10,000 records with like +25 rows and some of the
jobs are repeated several times. Using the Job as the reference in vlookup i
am trying to populate the data in a different worksheet, but vlookup just
takes the list row information of a perticular job but i want it to take the
data from all the lines for that job. EX:
Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want the data to populated in this way:
Jim dell
Jim toshiba
Jim lenovo
but its populating like:

Jim dell
Jim dell
Jim dell

Can someone help me on this? i found some formulas but those are for
relatively small data, but how wld i do it with such a huge data?