Vlookup with multiple criteria

nick · 10-07-2005, 11:05 AM

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?

Richard Buttrey · 10-07-2005, 12:05 PM

On Fri, 7 Oct 2005 07:48:07 -0700, "nick"
<[email protected]> 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
__________________________

nick · 10-07-2005, 12:05 PM

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"
> <[email protected]> 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 · 10-07-2005, 01:05 PM

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"
<[email protected]> 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"
>> <[email protected]> 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
__________________________

nick · 10-07-2005, 01:05 PM

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"
> <[email protected]> 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"
> >> <[email protected]> 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
> __________________________
>

Ashish Mathur · 10-08-2005, 07:05 AM

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?
>

Richard Buttrey · 10-08-2005, 08:05 AM

On Fri, 7 Oct 2005 09:28:01 -0700, "nick"
<[email protected]> 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
__________________________

nick · 10-10-2005, 11:05 AM

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?
> >

nick · 10-10-2005, 11:05 AM

Hi Richard,

Can you plz explain the syntax that ashish used? Thanks

"Richard Buttrey" wrote:

> On Fri, 7 Oct 2005 09:28:01 -0700, "nick"
> <[email protected]> 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
> __________________________
>