Vlookup multiple value

[deen]

Is there any way to lookup multiple answers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.

Here's an example.


Part No. Work Order
123 6000
123 6010
130 7000
135 7050


Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?

Pls help Me

 

[Ron Coderre]

With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2

Then
D1: (a part number)

This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")

Adjust range references to suit your situation.
Copy that formula down as far as you need.

Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Ron Coderre]

DRAT! My fingers not typing what I'm thinking!

Formula 1 should be:
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),INDEX($B$2:$B$5,SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1))),"")

Formula 2 (the array formula) should be:
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1))),"")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2

Then
D1: (a part number)

This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")

Adjust range references to suit your situation.
Copy that formula down as far as you need.

Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Is there any way to lookup multiple answers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.

Here's an example.


Part No. Work Order
123 6000
123 6010
130 7000
135 7050


Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?

Pls help Me

 

[deen]

With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2

Then
D1: (a part number)

This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")

Adjust range references to suit your situation.
Copy that formula down as far as you need.

Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Is there any way to lookupmultipleanswers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?

Pls help Me

Dear Ron,

Thx for your quick reply, i am not able to understand the formula,

Eg:

In Sheet1 i have data like

Code No Amt

F0001 100
F0001 10
F0001 20
F0002 50
F0002 60
F0002 70
F0002 200


I Need result in sheet2 like,

Code No Amt
F0001 100
10
20

F0002 50
60
70
200

Pls Help Me,

Regards
Deen

 

[deen]

DRAT! My fingers not typing what I'm thinking!

Formula 1 should be:
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),INDEX($B$2:$B$5,SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1))),"")

Formula 2 (the array formula) should be:
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1))),"")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2

Then
D1: (a part number)

This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")

Adjust range references to suit your situation.
Copy that formula down as far as you need.

Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

"deen" wrote:

Pls kindly sugess other formula is not properly

 

[Ron Coderre]

It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden.

Here are a couple options:

1)Use a Pivot Table on Sheet2 with these settings:
ROW: Code No, Amt
DATA: Sum of Amt

Double-click on the "Code No" field and set Totals to: None
Table options: UNcheck Grand Totals for Rows and Columns

The final Pivot Table will look like this:
Sum of Amt
Code No Amt Total
F0001 100 100
10 10
20 20
F0002 50 50
60 60
70 70
200 200

The last step is to hide the Total column.

or
2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted)
Enter formulas on Sheet2 that simply refer to the cells on Sheet1

Example (on Sheet2):
A1: =Sheet1!A1
B1: =Sheet1!B1

Then....
Select from A2 through the last data cell in Col_A
<format><conditional formatting>
Formula is: =(A1=A2)
Click the [format] button and set the font color to white

Due to the CF, the duplicate values will be "invisible", because of the
white fonts, and look like this:
Code No Amt
F0001 100
10
F0001 20
50
F0002 60
70
200

Alternatively, you might be able to just copy the original data onto Sheet2
and use the Conditional Formatting.

I hope that helps.

***********
Regards,
Ron

XL2002, WinXP

With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2

Then
D1: (a part number)

This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")

Adjust range references to suit your situation.
Copy that formula down as far as you need.

Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Is there any way to lookupmultipleanswers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?

Pls help Me

Dear Ron,

Thx for your quick reply, i am not able to understand the formula,

Eg:

In Sheet1 i have data like

Code No Amt

F0001 100
F0001 10
F0001 20
F0002 50
F0002 60
F0002 70
F0002 200


I Need result in sheet2 like,

Code No Amt
F0001 100
10
20

F0002 50
60
70
200

Pls Help Me,

Regards
Deen

 

[deen]

It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden.

Here are a couple options:

1)Use a Pivot Table on Sheet2 with these settings:
ROW: Code No, Amt
DATA: Sum of Amt

Double-click on the "Code No" field and set Totals to: None
Table options: UNcheck Grand Totals for Rows and Columns

The final Pivot Table will look like this:
Sum of Amt
Code No Amt Total
F0001 100 100
10 10
20 20
F0002 50 50
60 60
70 70
200 200

The last step is to hide the Total column.

or
2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted)
Enter formulas on Sheet2 that simply refer to the cells on Sheet1

Example (on Sheet2):
A1: =Sheet1!A1
B1: =Sheet1!B1

Then....
Select from A2 through the last data cell in Col_A
<format><conditional formatting>
Formula is: =(A1=A2)
Click the [format] button and set the font color to white

Due to the CF, the duplicate values will be "invisible", because of the
white fonts, and look like this:
Code No Amt
F0001 100
10
F0001 20
50
F0002 60
70
200

Alternatively, you might be able to just copy the original data onto Sheet2
and use the Conditional Formatting.

I hope that helps.

***********
Regards,
Ron

XL2002, WinXP

With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2
Then
D1: (a part number)
This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")
Adjust range references to suit your situation.
Copy that formula down as far as you need.
Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
:
Is there any way to lookupmultipleanswers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.
Here's an example.
Part No. Work Order
123 6000
123 6010
130 7000
135 7050
Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?
Pls help Me

Dear Ron,

Thx for your quick reply, i am not able to understand the formula,

In Sheet1 i have data like

Code No Amt

F0001 100
F0001 10
F0001 20
F0002 50
F0002 60
F0002 70
F0002 200

I Need result in sheet2 like,

Code No Amt
F0001 100
10
20

F0002 50
60
70
200

Pls Help Me,

Regards
Deen

Thx Mr.Ron,

With use of macro they have any possible

 

[deen]

Hi Ron,

your are not able to undersatand what i was say,

with ur permission shall i send excel sheet , that given clear
structure.

It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden.

Here are a couple options:

1)Use a Pivot Table on Sheet2 with these settings:
ROW: Code No, Amt
DATA: Sum of Amt

Double-click on the "Code No" field and set Totals to: None
Table options: UNcheck Grand Totals for Rows and Columns

The final Pivot Table will look like this:
Sum of Amt
Code No Amt Total
F0001 100 100
10 10
20 20
F0002 50 50
60 60
70 70
200 200

The last step is to hide the Total column.

or
2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted)
Enter formulas on Sheet2 that simply refer to the cells on Sheet1

Example (on Sheet2):
A1: =Sheet1!A1
B1: =Sheet1!B1

Then....
Select from A2 through the last data cell in Col_A
<format><conditional formatting>
Formula is: =(A1=A2)
Click the [format] button and set the font color to white

Due to the CF, the duplicate values will be "invisible", because of the
white fonts, and look like this:
Code No Amt
F0001 100
10
F0001 20
50
F0002 60
70
200

Alternatively, you might be able to just copy the original data onto Sheet2
and use the Conditional Formatting.

I hope that helps.

XL2002, WinXP

On Apr 5, 9:02 pm, Ron Coderre <[email protected]>
wrote:
With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2
Then
D1: (a part number)
This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")
Adjust range references to suit your situation.
Copy that formula down as far as you need.
Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
:
Is there any way to lookupmultipleanswers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.
Here's an example.
Part No. Work Order
123 6000
123 6010
130 7000
135 7050
Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?
Pls help Me
Dear Ron,
Thx for your quick reply, i am not able to understand the formula,
Eg:
In Sheet1 i have data like
Code No Amt
F0001 100
F0001 10
F0001 20
F0002 50
F0002 60
F0002 70
F0002 200
I Need result in sheet2 like,
Code No Amt
F0001 100
10
20
F0002 50
60
70
200
Pls Help Me,
Regards
Deen

Thx Mr.Ron,

With use of macro they have any possible

 

[Domenic]

Assuming that A2:B5 contains the data, try the following...

Let D1 contains the part number of interest, such as 123

E2:

=COUNTIF(A2:A5,D2)

F2, copied down:

=IF(ROWS(F$2:F2)<=$E$2,INDEX($B$2:$B$5,SMALL(IF($A$2:$A$5=$D$2,ROW($A$2:$
A$5)-ROW($A$2)+1),ROWS(F$2:F2))),"")

Hope this helps!

 

[Ron Coderre]

Hi, Deen

Please post the file to one of the free file hosting services. Then post
its location here. That way the file will be available to anyone interested
in working on a solution for you.

Here are some free file hosting options:
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php


***********
Regards,
Ron

XL2002, WinXP

Hi Ron,

your are not able to undersatand what i was say,

with ur permission shall i send excel sheet , that given clear
structure.

It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden.

Here are a couple options:

1)Use a Pivot Table on Sheet2 with these settings:
ROW: Code No, Amt
DATA: Sum of Amt

Double-click on the "Code No" field and set Totals to: None
Table options: UNcheck Grand Totals for Rows and Columns

The final Pivot Table will look like this:
Sum of Amt
Code No Amt Total
F0001 100 100
10 10
20 20
F0002 50 50
60 60
70 70
200 200

The last step is to hide the Total column.

or
2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted)
Enter formulas on Sheet2 that simply refer to the cells on Sheet1

Example (on Sheet2):
A1: =Sheet1!A1
B1: =Sheet1!B1

Then....
Select from A2 through the last data cell in Col_A
<format><conditional formatting>
Formula is: =(A1=A2)
Click the [format] button and set the font color to white

Due to the CF, the duplicate values will be "invisible", because of the
white fonts, and look like this:
Code No Amt
F0001 100
10
F0001 20
50
F0002 60
70
200

Alternatively, you might be able to just copy the original data onto Sheet2
and use the Conditional Formatting.

I hope that helps.

XL2002, WinXP

:
On Apr 5, 9:02 pm, Ron Coderre <[email protected]>
wrote:
With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2

Then
D1: (a part number)

This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")

Adjust range references to suit your situation.
Copy that formula down as far as you need.

Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

:
Is there any way to lookupmultipleanswers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?

Pls help Me

Dear Ron,

Thx for your quick reply, i am not able to understand the formula,

In Sheet1 i have data like

Code No Amt

F0001 100
F0001 10
F0001 20
F0002 50
F0002 60
F0002 70
F0002 200

I Need result in sheet2 like,

Code No Amt
F0001 100
10
20

F0002 50
60
70
200

Pls Help Me,

Regards
Deen

Thx Mr.Ron,

With use of macro they have any possible

 

[deen]

Hi, Deen

Please post the file to one of the free file hosting services. Then post
its location here. That way the file will be available to anyone interested
in working on a solution for you.

Here are some free file hosting options:http://www.flypicture.com/http://cjoint.com/index.phphttp://www.savefile.com/index.php

***********
Regards,
Ron

XL2002, WinXP

Hi Ron,

your are not able to undersatand what i was say,

with ur permission shall i send excel sheet , that given clear
structure.

On Apr 6, 4:48 pm, Ron Coderre <[email protected]>
wrote:
It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden.
Here are a couple options:
1)Use a Pivot Table on Sheet2 with these settings:
ROW: Code No, Amt
DATA: Sum of Amt
Double-click on the "Code No" field and set Totals to: None
Table options: UNcheck Grand Totals for Rows and Columns
The final Pivot Table will look like this:
Sum of Amt
Code No Amt Total
F0001 100 100
10 10
20 20
F0002 50 50
60 60
70 70
200 200
The last step is to hide the Total column.
or
2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted)
Enter formulas on Sheet2 that simply refer to the cells on Sheet1
Example (on Sheet2):
A1: =Sheet1!A1
B1: =Sheet1!B1
Then....
Select from A2 through the last data cell in Col_A
<format><conditional formatting>
Formula is: =(A1=A2)
Click the [format] button and set the font color to white
Due to the CF, the duplicate values will be "invisible", because of the
white fonts, and look like this:
Code No Amt
F0001 100
10
F0001 20
50
F0002 60
70
200
Alternatively, you might be able to just copy the original data onto Sheet2
and use the Conditional Formatting.
I hope that helps.
***********
Regards,
Ron
XL2002, WinXP
:
On Apr 5, 9:02 pm, Ron Coderre <[email protected]>
wrote:
With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2
Then
D1: (a part number)
This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")
Adjust range references to suit your situation.
Copy that formula down as far as you need.
Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
:
Is there any way to lookupmultipleanswers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.
Here's an example.
Part No. Work Order
123 6000
123 6010
130 7000
135 7050
Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?
Pls help Me
Dear Ron,
Thx for your quick reply, i am not able to understand the formula,
Eg:
In Sheet1 i have data like
Code No Amt
F0001 100
F0001 10
F0001 20
F0002 50
F0002 60
F0002 70
F0002 200
I Need result in sheet2 like,
Code No Amt
F0001 100
10
20
F0002 50
60
70
200
Pls Help Me,
Regards
Deen
Thx Mr.Ron,
With use of macro they have any possible

Dear Ron,

As per your instruction i has been send the file thru below

http://www.savefile.com/files/615770


Regards
Deen

 

[Ron Coderre]

Hi, Deen

I looked at the file you made available. See if this works for you....

Using the sample file you posted (Eg_Data_vlookup.xls)

On sheet 'F16'
L2: =IF(TRIM(I2)="Serical No",L1+1,L1)
Copy that formula down as far as you need

A2: =IF(AND(ISNUMBER(D2),B2<>"Total"),L2,"")
Copy that formula down as far as you need

On Sheet 'Data'
I2:
=IF(A2>MAX('F16'!$A$1:$A$200),"",SMALL(IF(('F16'!$A$1:$A$200=Sheet1!A2),ROW('F16'!$A$1:$A$200)),COUNTIF($A$1:$A2,A2)))

Note: For that ARRAY FORMULA, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].
Copy I2 and paste into I3 and down as far as you need

A2: 1
A3: =A2+(COUNTIF($A$1:$A2,A2)=COUNTIF('F16'!$A$1:$A$200,Sheet1!A2))
Copy that formula down as far as you need

D2: =INDEX('F16'!$B$1:$B$200,$I2)
E2: =INDEX('F16'!$D$1:$D$200,$I2)
F2: =INDEX('F16'!$E$1:$E$200,$I2)
Copy those formulas down as far as you need

Those formulas will populate the 'Sr. No', 'Date on', 'TDS', and 'Rate at'
fields.
Using your sample file, these values were returned:
Sr.No Date on TDS Rate at
1 22/04/05 8.602 2.2
1 13/05/05 51.634 2.2
1 13/06/05 40.876 2.2
1 08/07/05 67.782 2.2
1 12/08/05 24.24 2.02
2 07.01.06 121.2 2.02
2 12/08/05 68.175 2.02
2 09/09/05 88.88 2.02
2 08/10/05 82.82 2.02

(I'm guessing you have other sources for the name & address field and the
'Name' field)

I hope that helps.

***********
Regards,
Ron

XL2002, WinXP

Hi, Deen

Please post the file to one of the free file hosting services. Then post
its location here. That way the file will be available to anyone interested
in working on a solution for you.

Here are some free file hosting options:http://www.flypicture.com/http://cjoint.com/index.phphttp://www.savefile.com/index.php

***********
Regards,
Ron

XL2002, WinXP

Hi Ron,

your are not able to undersatand what i was say,

with ur permission shall i send excel sheet , that given clear
structure.

On Apr 6, 4:48 pm, Ron Coderre <[email protected]>
wrote:

It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden.

Here are a couple options:

1)Use a Pivot Table on Sheet2 with these settings:
ROW: Code No, Amt
DATA: Sum of Amt

Double-click on the "Code No" field and set Totals to: None
Table options: UNcheck Grand Totals for Rows and Columns

The final Pivot Table will look like this:
Sum of Amt
Code No Amt Total
F0001 100 100
10 10
20 20
F0002 50 50
60 60
70 70
200 200

The last step is to hide the Total column.

or
2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted)
Enter formulas on Sheet2 that simply refer to the cells on Sheet1

Example (on Sheet2):
A1: =Sheet1!A1
B1: =Sheet1!B1

Then....
Select from A2 through the last data cell in Col_A
<format><conditional formatting>
Formula is: =(A1=A2)
Click the [format] button and set the font color to white

Due to the CF, the duplicate values will be "invisible", because of the
white fonts, and look like this:
Code No Amt
F0001 100
10
F0001 20
50
F0002 60
70
200

Alternatively, you might be able to just copy the original data onto Sheet2
and use the Conditional Formatting.

I hope that helps.

XL2002, WinXP

:
On Apr 5, 9:02 pm, Ron Coderre <[email protected]>
wrote:
With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2

Then
D1: (a part number)

This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")

Adjust range references to suit your situation.
Copy that formula down as far as you need.

Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

:
Is there any way to lookupmultipleanswers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?

Pls help Me

Dear Ron,

Thx for your quick reply, i am not able to understand the formula,

In Sheet1 i have data like

Code No Amt

F0001 100
F0001 10
F0001 20
F0002 50
F0002 60
F0002 70
F0002 200

I Need result in sheet2 like,

Code No Amt
F0001 100
10
20

F0002 50
60
70
200

Pls Help Me,

Thx Mr.Ron,

With use of macro they have any possible

Dear Ron,

As per your instruction i has been send the file thru below

http://www.savefile.com/files/615770


Regards
Deen

 

[deen]

Hi, Deen

I looked at the file you made available. See if this works for you....

Using the sample file you posted (Eg_Data_vlookup.xls)

On sheet 'F16'
L2: =IF(TRIM(I2)="Serical No",L1+1,L1)
Copy that formula down as far as you need

A2: =IF(AND(ISNUMBER(D2),B2<>"Total"),L2,"")
Copy that formula down as far as you need

On Sheet 'Data'
I2:
=IF(A2>MAX('F16'!$A$1:$A$200),"",SMALL(IF(('F16'!$A$1:$A$200=Sheet1!A2),ROW('F16'!$A$1:$A$200)),COUNTIF($A$1:$A2,A2)))

Note: For that ARRAY FORMULA, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].
Copy I2 and paste into I3 and down as far as you need

A2: 1
A3: =A2+(COUNTIF($A$1:$A2,A2)=COUNTIF('F16'!$A$1:$A$200,Sheet1!A2))
Copy that formula down as far as you need

D2: =INDEX('F16'!$B$1:$B$200,$I2)
E2: =INDEX('F16'!$D$1:$D$200,$I2)
F2: =INDEX('F16'!$E$1:$E$200,$I2)
Copy those formulas down as far as you need

Those formulas will populate the 'Sr. No', 'Date on', 'TDS', and 'Rate at'
fields.
Using your sample file, these values were returned:
Sr.No Date on TDS Rate at
1 22/04/05 8.602 2.2
1 13/05/05 51.634 2.2
1 13/06/05 40.876 2.2
1 08/07/05 67.782 2.2
1 12/08/05 24.24 2.02
2 07.01.06 121.2 2.02
2 12/08/05 68.175 2.02
2 09/09/05 88.88 2.02
2 08/10/05 82.82 2.02

(I'm guessing you have other sources for the name & address field and the
'Name' field)

I hope that helps.

***********
Regards,
Ron

XL2002, WinXP

Hi, Deen
Please post the file to one of the free file hosting services. Then post
its location here. That way the file will be available to anyone interested
in working on a solution for you.
Here are some free file hosting options:http://www.flypicture.com/http://cjoint.com/index.phphttp://www.savef...
***********
Regards,
Ron
XL2002, WinXP
:
Hi Ron,
your are not able to undersatand what i was say,
with ur permission shall i send excel sheet , that given clear
structure.
On Apr 6, 4:48 pm, Ron Coderre <[email protected]>
wrote:
It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden.
Here are a couple options:
1)Use a Pivot Table on Sheet2 with these settings:
ROW: Code No, Amt
DATA: Sum of Amt
Double-click on the "Code No" field and set Totals to: None
Table options: UNcheck Grand Totals for Rows and Columns
The final Pivot Table will look like this:
Sum of Amt
Code No Amt Total
F0001 100 100
10 10
20 20
F0002 50 50
60 60
70 70
200 200
The last step is to hide the Total column.
or
2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted)
Enter formulas on Sheet2 that simply refer to the cells on Sheet1
Example (on Sheet2):
A1: =Sheet1!A1
B1: =Sheet1!B1
Then....
Select from A2 through the last data cell in Col_A
<format><conditional formatting>
Formula is: =(A1=A2)
Click the [format] button and set the font color to white
Due to the CF, the duplicate values will be "invisible", because of the
white fonts, and look like this:
Code No Amt
F0001 100
10
F0001 20
50
F0002 60
70
200
Alternatively, you might be able to just copy the original data onto Sheet2
and use the Conditional Formatting.
I hope that helps.
***********
Regards,
Ron
XL2002, WinXP
:
On Apr 5, 9:02 pm, Ron Coderre <[email protected]>
wrote:
With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2
Then
D1: (a part number)
This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")
Adjust range references to suit your situation.
Copy that formula down as far as you need.
Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
:
Is there any way to lookupmultipleanswers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.
Here's an example.
Part No. Work Order
123 6000
123 6010
130 7000
135 7050
Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?
Pls help Me
Dear Ron,
Thx for your quick reply, i am not able to understand the formula,
Eg:
In Sheet1 i have data like
Code No Amt
F0001 100
F0001 10
F0001 20
F0002 50
F0002 60
F0002 70
F0002 200
I Need result in sheet2 like,
Code No Amt
F0001 100
10
20
F0002 50
60
70
200
Pls Help Me,
Regards
Deen
Thx Mr.Ron,
With use of macro they have any possible

Dear Ron,

As per your instruction i has been send the file thru below

Regards
Deen

Hi Ron,

Can pls send Eg: excel sheet, bcoz that's help me to under stand
comfort

 

[deen]

Hi, Deen

I looked at the file you made available. See if this works for you....

Using the sample file you posted (Eg_Data_vlookup.xls)

On sheet 'F16'
L2: =IF(TRIM(I2)="Serical No",L1+1,L1)
Copy that formula down as far as you need

A2: =IF(AND(ISNUMBER(D2),B2<>"Total"),L2,"")
Copy that formula down as far as you need

On Sheet 'Data'
I2:
=IF(A2>MAX('F16'!$A$1:$A$200),"",SMALL(IF(('F16'!$A$1:$A$200=Sheet1!A2),ROW('F16'!$A$1:$A$200)),COUNTIF($A$1:$A2,A2)))

Note: For that ARRAY FORMULA, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].
Copy I2 and paste into I3 and down as far as you need

A2: 1
A3: =A2+(COUNTIF($A$1:$A2,A2)=COUNTIF('F16'!$A$1:$A$200,Sheet1!A2))
Copy that formula down as far as you need

D2: =INDEX('F16'!$B$1:$B$200,$I2)
E2: =INDEX('F16'!$D$1:$D$200,$I2)
F2: =INDEX('F16'!$E$1:$E$200,$I2)
Copy those formulas down as far as you need

Those formulas will populate the 'Sr. No', 'Date on', 'TDS', and 'Rate at'
fields.
Using your sample file, these values were returned:
Sr.No Date on TDS Rate at
1 22/04/05 8.602 2.2
1 13/05/05 51.634 2.2
1 13/06/05 40.876 2.2
1 08/07/05 67.782 2.2
1 12/08/05 24.24 2.02
2 07.01.06 121.2 2.02
2 12/08/05 68.175 2.02
2 09/09/05 88.88 2.02
2 08/10/05 82.82 2.02

(I'm guessing you have other sources for the name & address field and the
'Name' field)

I hope that helps.

***********
Regards,
Ron

XL2002, WinXP

Hi, Deen
Please post the file to one of the free file hosting services. Then post
its location here. That way the file will be available to anyone interested
in working on a solution for you.
Here are some free file hosting options:http://www.flypicture.com/http://cjoint.com/index.phphttp://www.savef...
***********
Regards,
Ron
XL2002, WinXP
:
Hi Ron,
your are not able to undersatand what i was say,
with ur permission shall i send excel sheet , that given clear
structure.
On Apr 6, 4:48 pm, Ron Coderre <[email protected]>
wrote:
It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden.
Here are a couple options:
1)Use a Pivot Table on Sheet2 with these settings:
ROW: Code No, Amt
DATA: Sum of Amt
Double-click on the "Code No" field and set Totals to: None
Table options: UNcheck Grand Totals for Rows and Columns
The final Pivot Table will look like this:
Sum of Amt
Code No Amt Total
F0001 100 100
10 10
20 20
F0002 50 50
60 60
70 70
200 200
The last step is to hide the Total column.
or
2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted)
Enter formulas on Sheet2 that simply refer to the cells on Sheet1
Example (on Sheet2):
A1: =Sheet1!A1
B1: =Sheet1!B1
Then....
Select from A2 through the last data cell in Col_A
<format><conditional formatting>
Formula is: =(A1=A2)
Click the [format] button and set the font color to white
Due to the CF, the duplicate values will be "invisible", because of the
white fonts, and look like this:
Code No Amt
F0001 100
10
F0001 20
50
F0002 60
70
200
Alternatively, you might be able to just copy the original data onto Sheet2
and use the Conditional Formatting.
I hope that helps.
***********
Regards,
Ron
XL2002, WinXP
:
On Apr 5, 9:02 pm, Ron Coderre <[email protected]>
wrote:
With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2
Then
D1: (a part number)
This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")
Adjust range references to suit your situation.
Copy that formula down as far as you need.
Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
:
Is there any way to lookupmultipleanswers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.
Here's an example.
Part No. Work Order
123 6000
123 6010
130 7000
135 7050
Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?
Pls help Me
Dear Ron,
Thx for your quick reply, i am not able to understand the formula,
Eg:
In Sheet1 i have data like
Code No Amt
F0001 100
F0001 10
F0001 20
F0002 50
F0002 60
F0002 70
F0002 200
I Need result in sheet2 like,
Code No Amt
F0001 100
10
20
F0002 50
60
70
200
Pls Help Me,
Regards
Deen
Thx Mr.Ron,
With use of macro they have any possible

Dear Ron,

As per your instruction i has been send the file thru below

Regards
Deen

Dear Ron,

Yes its great,

But i need the result in F16, in the data sheet all data has been
cotain,

f16 is the certificate i want provide to the customer,

i need the result in F16 sheet pls help. fyi

http://www.savefile.com/files/615770


Details: In Data sheet i already have a data, i need produced the
result in F16 sheet in same format,

Eg:

in F16 have Sr.No in column A:, once i enter sr.no: in column a:25,a:
77,a:130, weather they result produce in the column D:25-D:42 (based
on how many record in data sheet) and E:25-E:42(based on how many
record in data sheet)

pls make ur formula complete reverse.


sorry for distrub U.


Regards,

Deen

 

[Ron Coderre]

Thanks for clarifying that. I inferred from the file you posted that you
might want the form filled in from the table, but your original post
indicated differently.

Based on that file, it appears that you are building a separate form for
each person. That is a very inefficient way to proceed. I suggest building a
master form which contains formulas to complete ALL fields by using an amount
activity list AND other lists or cells. Otherwise, every time you add a
person, you'll need to create a new form.

In addition to the amount activity list, you'll need a list of your
company's static information and a Contractor list that includes this
information:
ID Num
Name
Address
PAN/GIN Num
Tax ID
etc

Once that is done, it should be relatively easy to just select a person
from a drop-down list and have all applicable fields in the form fill-in via
formulas. Then post back for assistance with those formulas.

***********
Regards,
Ron

XL2002, WinXP

Hi, Deen

I looked at the file you made available. See if this works for you....

Using the sample file you posted (Eg_Data_vlookup.xls)

On sheet 'F16'
L2: =IF(TRIM(I2)="Serical No",L1+1,L1)
Copy that formula down as far as you need

A2: =IF(AND(ISNUMBER(D2),B2<>"Total"),L2,"")
Copy that formula down as far as you need

On Sheet 'Data'
I2:
=IF(A2>MAX('F16'!$A$1:$A$200),"",SMALL(IF(('F16'!$A$1:$A$200=Sheet1!A2),ROW('F16'!$A$1:$A$200)),COUNTIF($A$1:$A2,A2)))

Note: For that ARRAY FORMULA, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].
Copy I2 and paste into I3 and down as far as you need

A2: 1
A3: =A2+(COUNTIF($A$1:$A2,A2)=COUNTIF('F16'!$A$1:$A$200,Sheet1!A2))
Copy that formula down as far as you need

D2: =INDEX('F16'!$B$1:$B$200,$I2)
E2: =INDEX('F16'!$D$1:$D$200,$I2)
F2: =INDEX('F16'!$E$1:$E$200,$I2)
Copy those formulas down as far as you need

Those formulas will populate the 'Sr. No', 'Date on', 'TDS', and 'Rate at'
fields.
Using your sample file, these values were returned:
Sr.No Date on TDS Rate at
1 22/04/05 8.602 2.2
1 13/05/05 51.634 2.2
1 13/06/05 40.876 2.2
1 08/07/05 67.782 2.2
1 12/08/05 24.24 2.02
2 07.01.06 121.2 2.02
2 12/08/05 68.175 2.02
2 09/09/05 88.88 2.02
2 08/10/05 82.82 2.02

(I'm guessing you have other sources for the name & address field and the
'Name' field)

I hope that helps.

***********
Regards,
Ron

XL2002, WinXP

On Apr 6, 8:22 pm, Ron Coderre <[email protected]>
wrote:
Hi, Deen

Please post the file to one of the free file hosting services. Then post
its location here. That way the file will be available to anyone interested
in working on a solution for you.

Here are some free file hosting options:http://www.flypicture.com/http://cjoint.com/index.phphttp://www.savef...

XL2002, WinXP

:
Hi Ron,

your are not able to undersatand what i was say,

with ur permission shall i send excel sheet , that given clear
structure.

On Apr 6, 4:48 pm, Ron Coderre <[email protected]>
wrote:

It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden.

Here are a couple options:

1)Use a Pivot Table on Sheet2 with these settings:
ROW: Code No, Amt
DATA: Sum of Amt

Double-click on the "Code No" field and set Totals to: None
Table options: UNcheck Grand Totals for Rows and Columns

The final Pivot Table will look like this:
Sum of Amt
Code No Amt Total
F0001 100 100
10 10
20 20
F0002 50 50
60 60
70 70
200 200

The last step is to hide the Total column.

or
2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted)
Enter formulas on Sheet2 that simply refer to the cells on Sheet1

Example (on Sheet2):
A1: =Sheet1!A1
B1: =Sheet1!B1

Then....
Select from A2 through the last data cell in Col_A
<format><conditional formatting>
Formula is: =(A1=A2)
Click the [format] button and set the font color to white

Due to the CF, the duplicate values will be "invisible", because of the
white fonts, and look like this:
Code No Amt
F0001 100
10
F0001 20
50
F0002 60
70
200

Alternatively, you might be able to just copy the original data onto Sheet2
and use the Conditional Formatting.

I hope that helps.

XL2002, WinXP

:
On Apr 5, 9:02 pm, Ron Coderre <[email protected]>
wrote:
With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2

Then
D1: (a part number)

This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")

Adjust range references to suit your situation.
Copy that formula down as far as you need.

Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

:
Is there any way to lookupmultipleanswers to a lookup formula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.

Here's an example.

Part No. Work Order
123 6000
123 6010
130 7000
135 7050

Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and find ALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?

Pls help Me

Dear Ron,

Thx for your quick reply, i am not able to understand the formula,

In Sheet1 i have data like

Code No Amt

F0001 100
F0001 10
F0001 20
F0002 50
F0002 60
F0002 70
F0002 200

I Need result in sheet2 like,

Code No Amt
F0001 100
10
20

F0002 50
60
70
200

Pls Help Me,

Thx Mr.Ron,

With use of macro they have any possible

Dear Ron,

As per your instruction i has been send the file thru below

Regards
Deen

Dear Ron,

Yes its great,

But i need the result in F16, in the data sheet all data has been
cotain,

f16 is the certificate i want provide to the customer,

i need the result in F16 sheet pls help. fyi

http://www.savefile.com/files/615770


Details: In Data sheet i already have a data, i need produced the
result in F16 sheet in same format,

Eg:

in F16 have Sr.No in column A:, once i enter sr.no: in column a:25,a:
77,a:130, weather they result produce in the column D:25-D:42 (based
on how many record in data sheet) and E:25-E:42(based on how many
record in data sheet)

pls make ur formula complete reverse.


sorry for distrub U.


Regards,

Deen

 

[deen]

Thanks for clarifying that. I inferred from the file you posted that you
might want the form filled in from the table, but your original post
indicated differently.

Based on that file, it appears that you are building a separate form for
each person. That is a very inefficient way to proceed. I suggest building a
master form which contains formulas to complete ALL fields by using an amount
activity list AND other lists or cells. Otherwise, every time you add a
person, you'll need to create a new form.

In addition to the amount activity list, you'll need a list of your
company's static information and a Contractor list that includes this
information:
ID Num
Name
Address
PAN/GIN Num
Tax ID
etc

Once that is done, it should be relatively easy to just select a person
from a drop-down list and have all applicable fields in the form fill-in via
formulas. Then post back for assistance with those formulas.

***********
Regards,
Ron

XL2002, WinXP

Hi, Deen
I looked at the file you made available. See if this works for you....
Using the sample file you posted (Eg_Data_vlookup.xls)
On sheet 'F16'
L2: =IF(TRIM(I2)="Serical No",L1+1,L1)
Copy that formula down as far as you need
A2: =IF(AND(ISNUMBER(D2),B2<>"Total"),L2,"")
Copy that formula down as far as you need
On Sheet 'Data'
I2:
=IF(A2>MAX('F16'!$A$1:$A$200),"",SMALL(IF(('F16'!$A$1:$A$200=Sheet1!A2),ROW('F16'!$A$1:$A$200)),COUNTIF($A$1:$A2,A2)))
Note: For that ARRAY FORMULA, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].
Copy I2 and paste into I3 and down as far as you need
A2: 1
A3: =A2+(COUNTIF($A$1:$A2,A2)=COUNTIF('F16'!$A$1:$A$200,Sheet1!A2))
Copy that formula down as far as you need
D2: =INDEX('F16'!$B$1:$B$200,$I2)
E2: =INDEX('F16'!$D$1:$D$200,$I2)
F2: =INDEX('F16'!$E$1:$E$200,$I2)
Copy those formulas down as far as you need
Those formulas will populate the 'Sr. No', 'Date on', 'TDS', and 'Rate at'
fields.
Using your sample file, these values were returned:
Sr.No Date on TDS Rate at
1 22/04/05 8.602 2.2
1 13/05/05 51.634 2.2
1 13/06/05 40.876 2.2
1 08/07/05 67.782 2.2
1 12/08/05 24.24 2.02
2 07.01.06 121.2 2.02
2 12/08/05 68.175 2.02
2 09/09/05 88.88 2.02
2 08/10/05 82.82 2.02
(I'm guessing you have other sources for the name & address field andthe
'Name' field)
I hope that helps.
***********
Regards,
Ron
XL2002, WinXP
:
On Apr 6, 8:22 pm, Ron Coderre <[email protected]>
wrote:
Hi, Deen
Please post the file to one of the free file hosting services. Then post
its location here. That way the file will be available to anyone interested
in working on a solution for you.
Here are some free file hosting options:http://www.flypicture.com/http://cjoint.com/index.phphttp://www.savef...
***********
Regards,
Ron
XL2002, WinXP
:
Hi Ron,
your are not able to undersatand what i was say,
with ur permission shall i send excel sheet , that given clear
structure.
On Apr 6, 4:48 pm, Ron Coderre <[email protected]>
wrote:
It seems like you only want a copy of the original data, but with duplicate
consecutive CodeNo values hidden.
Here are a couple options:
1)Use a Pivot Table on Sheet2 with these settings:
ROW: Code No, Amt
DATA: Sum of Amt
Double-click on the "Code No" field and set Totals to: None
Table options: UNcheck Grand Totals for Rows and Columns
The final Pivot Table will look like this:
Sum of Amt
Code No Amt Total
F0001 100 100
10 10
20 20
F0002 50 50
60 60
70 70
200 200
The last step is to hide the Total column.
or
2)Use Conditional Formatting (this assumes the data on Sheet1 is sorted)
Enter formulas on Sheet2 that simply refer to the cells on Sheet1
Example (on Sheet2):
A1: =Sheet1!A1
B1: =Sheet1!B1
Then....
Select from A2 through the last data cell in Col_A
<format><conditional formatting>
Formula is: =(A1=A2)
Click the [format] button and set the font color to white
Due to the CF, the duplicate values will be "invisible", because of the
white fonts, and look like this:
Code No Amt
F0001 100
10
F0001 20
50
F0002 60
70
200
Alternatively, you might be able to just copy the original data onto Sheet2
and use the Conditional Formatting.
I hope that helps.
***********
Regards,
Ron
XL2002, WinXP
:
On Apr 5, 9:02 pm, Ron Coderre <[email protected]>
wrote:
With
Col_A containing PartNums, beginning in Cell A2
and
Col_B containing WorkOrders, beginning in cell B2
Then
D1: (a part number)
This regular formula returns the WorkOrders associated with that PartNum
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(INDEX(($A$2:$A$5=$D$1)*ROW($A$2:$A$5)+($A$2:$A$5<>$D$1)*10^99,0),ROWS($E$1:$E1)),"")
Adjust range references to suit your situation.
Copy that formula down as far as you need.
Alternatively, you could use this ARRAY FORMULA
(committed with [ctrl]+[shift]+[enter].....instead of just [enter])
E1:
=IF(COUNTIF($A$2:$A$5,$D$1)>=ROWS($E$1:$E1),SMALL(IF($A$2:$A$5=$D$1,ROW($A$2:$A$5)),ROWS($E$1:$E1)),"")
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
:
Is there any way to lookupmultipleanswers to a lookupformula.
Normally,
Excel will stop at the first match it finds and return that as the
answer
when, in fact, there may be more than one answer. I want to find the
other
matches as well and list them separately.
Here's an example.
Part No. Work Order
123 6000
123 6010
130 7000
135 7050
Part No. 123 has 2 (production) work orders associated with it: 6000
and
6010. I want to do a lookup of Part No. 123 and findALL the work
orders
associated with it (listed separately, of course). Is there any way
to do
I want Data Excel sheet2?
Pls help Me
Dear Ron,
Thx for your quick reply, i am not able to understand theformula,
Eg:
In Sheet1 i have data like
Code No Amt
F0001 100
F0001 10
F0001 20
F0002 50
F0002 60
F0002 70
F0002 200
I Need result in sheet2 like,
Code No Amt
F0001 100
10
20
F0002 50
60
70
200
Pls Help Me,
Regards
Deen
Thx Mr.Ron,
With use of macro they have any possible
Dear Ron,
As per your instruction i has been send the file thru below
http://www.savefile.com/files/615770
Regards
Deen

Dear Ron,

Yes its great,

But i need the result in F16, in the data sheet all data has been
cotain,

f16 is the certificate i want provide to the customer,

i need the result in F16 sheet pls help. fyi

Details: In Data sheet i already have a data, i need produced the
result in F16 sheet in same format,

in F16 have Sr.No in column A:, once i

...

read more »

Hi Ron,

can pls sugess there any way do like yesterday we discuss,
bcoz f16 i want take printout one time, bcoz of i have more than 4000
data.

pls help me with EG: excel sheet.

regards,
Deen