1 Vlookup multiple results in different columns
 

Now that I have my vlookup working I now have a new situation

I have a customer number which I use my vlookup to get data such as
Cust name
renewal date
product code
product description

Now the new problem is if I have one customer ID that has 5 different
products, how can I get all 5 products into my result and each in a different
cell. each customer will have no more than at the most 10 part numbers per
customer number

Thanks in advance







--
Neall

1 Vlookup multiple results in different columns
 

Neall wrote:
Now that I have my vlookup working I now have a new situation

I have a customer number which I use my vlookup to get data such as
Cust name
renewal date
product code
product description

Now the new problem is if I have one customer ID that has 5 different
products, how can I get all 5 products into my result and each in a different
cell. each customer will have no more than at the most 10 part numbers per
customer number

Thanks in advance




You would need to describe exactly how you want the results to look for a
complete recommendation, but here is something from a previous post that could
be useful.

Name your list of Customers "Customers" and your list of Products "Products".

To create a list of Customers, enter the following array formula (commit with
CTRL+SHIFT+ENTER) in A2 of another worksheet and copy down until blanks are
returned:

=IF(ROWS($1:1)SUM(1/COUNTIF(Customers,Customers)),"",
INDEX(Customers,MATCH(0,COUNTIF($A$1:A1,Customers) ,0)))

The first associated Product for the Customer is returned by entering the
following in B2 and copying down as needed:

=IF(A2="","",INDEX(Products,MATCH($A2,Customers,0) ))

The rest of the associated codes are obtained by entering this array formula
(commit with CTRL+SHIFT+ENTER) in C2 and copying across and down as needed:

=IF(COLUMN(B$1)COUNTIF(Customers,$A2),"",
INDEX(INDIRECT("'"&CELL("filename",Products)&
"'!R"&MATCH($A2&B2,Customers&Products,0)+ROW(Produ cts)&
"C"&COLUMN(Products)&":R"&ROWS(Products)+ROW(Produ cts)-1&
"C"&COLUMN(Products),FALSE),
MATCH($A2,INDIRECT("'"&CELL("filename",Customers)&
"'!R"&MATCH($A2&B2,Customers&Products,0)+ROW(Custo mers)&
"C"&COLUMN(Customers)&":R"&ROWS(Customers)+ROW(Cus tomers)-1&
"C"&COLUMN(Customers),FALSE),0)))

1 Vlookup multiple results in different columns
 

OK it might be easier to explain

I have sheet 1 which I have pulled all the customer data I need

On sheet 2 I have created a "display" section where you enter a customer
number or use the drop menu to select the customer number (A6)

When A6 has a customer number in it I then need to display the following
information about that customer pulled from sheet1 (data sheet) onto sheet 2

Part numbers the customer has separately (in A13,14,15,16 etc) the
description of the part numbers (in B13,14,15,16 etc) and the quantity (in
C13,14,15,16 etc)

Hope that helps







--
Neall


"Glenn" wrote:

Neall wrote:
Now that I have my vlookup working I now have a new situation

I have a customer number which I use my vlookup to get data such as
Cust name
renewal date
product code
product description

Now the new problem is if I have one customer ID that has 5 different
products, how can I get all 5 products into my result and each in a different
cell. each customer will have no more than at the most 10 part numbers per
customer number

Thanks in advance




You would need to describe exactly how you want the results to look for a
complete recommendation, but here is something from a previous post that could
be useful.

Name your list of Customers "Customers" and your list of Products "Products".

To create a list of Customers, enter the following array formula (commit with
CTRL+SHIFT+ENTER) in A2 of another worksheet and copy down until blanks are
returned:

=IF(ROWS($1:1)SUM(1/COUNTIF(Customers,Customers)),"",
INDEX(Customers,MATCH(0,COUNTIF($A$1:A1,Customers) ,0)))

The first associated Product for the Customer is returned by entering the
following in B2 and copying down as needed:

=IF(A2="","",INDEX(Products,MATCH($A2,Customers,0) ))

The rest of the associated codes are obtained by entering this array formula
(commit with CTRL+SHIFT+ENTER) in C2 and copying across and down as needed:

=IF(COLUMN(B$1)COUNTIF(Customers,$A2),"",
INDEX(INDIRECT("'"&CELL("filename",Products)&
"'!R"&MATCH($A2&B2,Customers&Products,0)+ROW(Produ cts)&
"C"&COLUMN(Products)&":R"&ROWS(Products)+ROW(Produ cts)-1&
"C"&COLUMN(Products),FALSE),
MATCH($A2,INDIRECT("'"&CELL("filename",Customers)&
"'!R"&MATCH($A2&B2,Customers&Products,0)+ROW(Custo mers)&
"C"&COLUMN(Customers)&":R"&ROWS(Customers)+ROW(Cus tomers)-1&
"C"&COLUMN(Customers),FALSE),0)))

1 Vlookup multiple results in different columns
 

The layout of your data on sheet 1 is the key to the formula, I believe.

You could send me a sample workbook with additional instructions of hat you
want to happen and how and where.

I have a vlookup formula in mind that may do your stuff.

Regards,
Howard


"Neall" wrote in message
...
Now that I have my vlookup working I now have a new situation

I have a customer number which I use my vlookup to get data such as
Cust name
renewal date
product code
product description

Now the new problem is if I have one customer ID that has 5 different
products, how can I get all 5 products into my result and each in a
different
cell. each customer will have no more than at the most 10 part numbers
per
customer number

Thanks in advance







--
Neall

1 Vlookup multiple results in different columns
 

Part numbers the customer has separately
(in A13,14,15,16 etc) the description of the
part numbers (in B13,14,15,16 etc) and the
quantity (in C13,14,15,16 etc)

Ok, so where's the customer name? In order to find all that data about the
customer you first have to find the customer.


--
Biff
Microsoft Excel MVP


"Neall" wrote in message
...
OK it might be easier to explain

I have sheet 1 which I have pulled all the customer data I need

On sheet 2 I have created a "display" section where you enter a customer
number or use the drop menu to select the customer number (A6)

When A6 has a customer number in it I then need to display the following
information about that customer pulled from sheet1 (data sheet) onto sheet
2

Part numbers the customer has separately (in A13,14,15,16 etc) the
description of the part numbers (in B13,14,15,16 etc) and the quantity (in
C13,14,15,16 etc)

Hope that helps







--
Neall


"Glenn" wrote:

Neall wrote:
Now that I have my vlookup working I now have a new situation

I have a customer number which I use my vlookup to get data such as
Cust name
renewal date
product code
product description

Now the new problem is if I have one customer ID that has 5 different
products, how can I get all 5 products into my result and each in a
different
cell. each customer will have no more than at the most 10 part numbers
per
customer number

Thanks in advance




You would need to describe exactly how you want the results to look for a
complete recommendation, but here is something from a previous post that
could
be useful.

Name your list of Customers "Customers" and your list of Products
"Products".

To create a list of Customers, enter the following array formula (commit
with
CTRL+SHIFT+ENTER) in A2 of another worksheet and copy down until blanks
are
returned:

=IF(ROWS($1:1)SUM(1/COUNTIF(Customers,Customers)),"",
INDEX(Customers,MATCH(0,COUNTIF($A$1:A1,Customers) ,0)))

The first associated Product for the Customer is returned by entering the
following in B2 and copying down as needed:

=IF(A2="","",INDEX(Products,MATCH($A2,Customers,0) ))

The rest of the associated codes are obtained by entering this array
formula
(commit with CTRL+SHIFT+ENTER) in C2 and copying across and down as
needed:

=IF(COLUMN(B$1)COUNTIF(Customers,$A2),"",
INDEX(INDIRECT("'"&CELL("filename",Products)&
"'!R"&MATCH($A2&B2,Customers&Products,0)+ROW(Produ cts)&
"C"&COLUMN(Products)&":R"&ROWS(Products)+ROW(Produ cts)-1&
"C"&COLUMN(Products),FALSE),
MATCH($A2,INDIRECT("'"&CELL("filename",Customers)&
"'!R"&MATCH($A2&B2,Customers&Products,0)+ROW(Custo mers)&
"C"&COLUMN(Customers)&":R"&ROWS(Customers)+ROW(Cus tomers)-1&
"C"&COLUMN(Customers),FALSE),0)))

1 Vlookup multiple results in different columns
 

Neall wrote:
OK it might be easier to explain

I have sheet 1 which I have pulled all the customer data I need

On sheet 2 I have created a "display" section where you enter a customer
number or use the drop menu to select the customer number (A6)

When A6 has a customer number in it I then need to display the following
information about that customer pulled from sheet1 (data sheet) onto sheet 2

Part numbers the customer has separately (in A13,14,15,16 etc) the
description of the part numbers (in B13,14,15,16 etc) and the quantity (in
C13,14,15,16 etc)

Hope that helps





Might be easier yet if I could see the actual workbook. If possible, post a
copy of it to www.savefile.com and I'll take a look.