Lookup duplicate items in a list
 

Hello, I hope that some1 can help me. I have a spreadsheet which looksup a
list in another sheet of the same workbook based on what account number is
entered in a particular cell. For example,
Sheet1
A B C D
1 Customer Destnation Rate Value
2 R0335 =vlookup..
3
4


Sheet2
A B C D
1 Customer Destination
2 A0022 Ireland
3 D1034 Scotland
4 R0335 Ireland
5 A0022 Wales
6 R0335 France
7 C0078 Spain
8 R0335 Germany

Basically, when I enter the a/c number R0335 I need cell B2 on sheet1 to
list the destinations that R0335 go to, so the operator can list the one that
is required and then cell C2 will look up the rate on B2 using the vlookup
function.

Any help would be gratefully appreciated as this is proving very hard for me
to keep calculating manually as there are about 250 per day.

Thanking you all in advance

Niall

Lookup duplicate items in a list
 

Source data in Sheet2 as posted, from row2 down

In Sheet1,
Input in A2 (Cust), eg: R0335

Put in B2:
=IF($A$2="","",IF(Sheet2!A2=$A$2,ROW(),""))
Leave B1 blank

Put in C2:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(Sheet2!B:B,SMAL L(B:B,ROWS($1:1))))
Copy B2:C2 down to cover the max expected extent of data in Sheet2, eg down
to C200? Minimize/hide col B. Col C will return all destinations for the
input in A2, neatly packed at the top. You can easily build on the other
lookups in adjacent cols pointing to the multiple returns in col C.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:54
xdemechanik
---
"Niall" wrote:
Hello, I hope that some1 can help me. I have a spreadsheet which looksup a
list in another sheet of the same workbook based on what account number is
entered in a particular cell. For example,
Sheet1
A B C D
1 Customer Destnation Rate Value
2 R0335 =vlookup..
3
4


Sheet2
A B C D
1 Customer Destination
2 A0022 Ireland
3 D1034 Scotland
4 R0335 Ireland
5 A0022 Wales
6 R0335 France
7 C0078 Spain
8 R0335 Germany

Basically, when I enter the a/c number R0335 I need cell B2 on sheet1 to
list the destinations that R0335 go to, so the operator can list the one that
is required and then cell C2 will look up the rate on B2 using the vlookup
function.

Any help would be gratefully appreciated as this is proving very hard for me
to keep calculating manually as there are about 250 per day.

Thanking you all in advance

Niall

Lookup duplicate items in a list
 

Max,
Many many thanks for the reply but I would say that I have lost it
somewhere. I entered the formulae where instructed but nothing happened - all
columns are blank when I enter a customer number. This is exactly the sheet1
and sheet2 layouts:

Sheet1 (Billing)

A B C D E
F G H
1 Date Docket No Account Customer Destination Radial Drops
Value
2 22-4-06 12345 R0335 Mr. J Blog (List of his destinations
should drop here)

Sheet2 (Site Radial)

A B C
1 Acc Destination Radial
2 B0015 Germany 32
3 C0723 France 28
4 R0335 Ireland 4
5 F0005 Spain 26
6 R0335 Italy 31
and so on.... with account number repeating with different destinations

I just needs the list of destinations to be on a drop down list when the acc
is entered so that the operator can select the desired destination.

Again thanks and let me know if it possible and if your first reply is right
where did I go wrong.


Kindest regards

Niall

"Max" wrote:

Source data in Sheet2 as posted, from row2 down

In Sheet1,
Input in A2 (Cust), eg: R0335

Put in B2:
=IF($A$2="","",IF(Sheet2!A2=$A$2,ROW(),""))
Leave B1 blank

Put in C2:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(Sheet2!B:B,SMAL L(B:B,ROWS($1:1))))
Copy B2:C2 down to cover the max expected extent of data in Sheet2, eg down
to C200? Minimize/hide col B. Col C will return all destinations for the
input in A2, neatly packed at the top. You can easily build on the other
lookups in adjacent cols pointing to the multiple returns in col C.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:54
xdemechanik
---
"Niall" wrote:
Hello, I hope that some1 can help me. I have a spreadsheet which looksup a
list in another sheet of the same workbook based on what account number is
entered in a particular cell. For example,
Sheet1
A B C D
1 Customer Destnation Rate Value
2 R0335 =vlookup..
3
4


Sheet2
A B C D
1 Customer Destination
2 A0022 Ireland
3 D1034 Scotland
4 R0335 Ireland
5 A0022 Wales
6 R0335 France
7 C0078 Spain
8 R0335 Germany

Basically, when I enter the a/c number R0335 I need cell B2 on sheet1 to
list the destinations that R0335 go to, so the operator can list the one that
is required and then cell C2 will look up the rate on B2 using the vlookup
function.

Any help would be gratefully appreciated as this is proving very hard for me
to keep calculating manually as there are about 250 per day.

Thanking you all in advance

Niall

Lookup duplicate items in a list
 

I'm not really sure what's happening

Here's a working sample based on your revised set-up:
http://freefilehosting.net/download/3m9fl
lookup multiple returns in another sheet.xls

Construct:
Source data in sheet: Site Radial
from row2 down, with key col: Cust acc#s in A2 down

In sheet: Billing,
Cust Acc input is in C2, eg: R0335

In E2:
=IF($C$2="","",IF('Site Radial'!A2=$C$2,ROW(),""))
Leave E1 blank

In F2:
=IF(ROWS($1:1)COUNT($E:$E),"",INDEX('Site
Radial'!B:B,SMALL($E:$E,ROWS($1:1))))
Copy F2:G2 down to cover the max expected extent of data in Site Radial, eg
down
to G200? Minimize/hide col E. Cols F & G will return all destinations &
radial for the
input in C2, neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:54
xdemechanik
---
"Niall" wrote:
Max,
Many many thanks for the reply but I would say that I have lost it
somewhere. I entered the formulae where instructed but nothing happened - all
columns are blank when I enter a customer number. This is exactly the sheet1
and sheet2 layouts:

Sheet1 (Billing)

A B C D E
F G H
1 Date Docket No Account Customer Destination Radial Drops
Value
2 22-4-06 12345 R0335 Mr. J Blog (List of his destinations
should drop here)

Sheet2 (Site Radial)

A B C
1 Acc Destination Radial
2 B0015 Germany 32
3 C0723 France 28
4 R0335 Ireland 4
5 F0005 Spain 26
6 R0335 Italy 31
and so on.... with account number repeating with different destinations

I just needs the list of destinations to be on a drop down list when the acc
is entered so that the operator can select the desired destination.

Again thanks and let me know if it possible and if your first reply is right
where did I go wrong.


Kindest regards

Niall

Lookup duplicate items in a list
 

On Sep 6, 1:24*am, Niall wrote:
Max,
Many many thanks for the reply but I would say that I have lost it
somewhere. I entered the formulae where instructed but nothing happened - all
columns are blank when I enter a customer number. *This is exactly the sheet1
and sheet2 layouts:

Sheet1 (Billing)

* * * * *A * * * * * * B * * * * * * C * * * * * * D * * * * * E * * * * * *
* *F * * * * * G * * * * *H
1 * Date * * *Docket No * Account * *Customer *Destination *Radial * Drops *
Value
2 * 22-4-06 *12345 * * * *R0335 * * *Mr. J Blog *(List of his destinations
should drop here)

Sheet2 (Site Radial)

* * * * *A * * * * * * B * * * * * * * C
1 * *Acc * * * Destination * *Radial
2 * *B0015 * Germany * * * * *32
3 * *C0723 * France * * * * * * 28
4 * *R0335 * Ireland * * * * * * 4
5 * *F0005 * Spain * * * * * * * 26
6 * *R0335 * Italy * * * * * * * * 31
and so on.... with account number repeating with different destinations

I just needs the list of destinations to be on a drop down list when the acc
is entered so that the operator can select the desired destination.

Again thanks and let me know if it possible and if your first reply is right
where did I go wrong.

Kindest regards

Niall

"Max" wrote:
Source data in Sheet2 as posted, from row2 down

In Sheet1,
Input in A2 (Cust), eg: R0335

Put in B2:
=IF($A$2="","",IF(Sheet2!A2=$A$2,ROW(),""))
Leave B1 blank

Put in C2:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(Sheet2!B:B,SMAL L(B:B,ROWS($1:1))))
Copy B2:C2 down to cover the max expected extent of data in Sheet2, eg down
to C200? Minimize/hide col B. Col C will return all destinations for the
input in A2, neatly packed at the top. You can easily build on the other
lookups in adjacent cols pointing to the multiple returns in col C.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:54
xdemechanik
---
"Niall" wrote:
Hello, I hope that some1 can help me. *I have a spreadsheet which looksup a
list in another sheet of the same workbook based on what account number is
entered in a particular cell. For example,
Sheet1
* * * * *A * * * * * * * B * * * * * * * C * * * * * * D
1 *Customer * *Destnation * *Rate * * * *Value
2 * R0335 * * * *=vlookup.. *
3
4

Sheet2
* * * * *A * * * * * * * B * * * * * * * C * * * * * * D
1 *Customer * *Destination
2 * A0022 * * * *Ireland
3 * D1034 * * * *Scotland
4 * R0335 * * * *Ireland
5 * A0022 * * * *Wales
6 * R0335 * * * *France
7 * C0078 * * * *Spain
8 * R0335 * * * *Germany

Basically, when I enter the a/c number R0335 I need cell B2 on sheet1 to
list the destinations that R0335 go to, so the operator can list the one that
is required and then cell C2 will look up the rate on B2 using the vlookup
function.

Any help would be gratefully appreciated as this is proving very hard for me
to keep calculating manually as there are about 250 per day.

Thanking you all in advance

Niall

Hi Niall,

Max's formulas work very nicely. I have used them on Sheet2 to produce
a list of destinations depending on the Account No.

The only problem is that you are wanting the list of destinations to
appear in a drop down list on Sheet1. You can't produce a drop down
list in a cell just by using formulas.

Try the following...

On Sheet2 in F2...
=IF($E$2="","",IF(A2=$E$2,ROW(),""))

and Sheet2 in G2...
=IF(ROWS($1:1)COUNT(F:F),"",INDEX(B:B,SMALL(F:F,R OWS($1:1))))

These are Max's formulas adjusted for working on Sheet2. Fill them
both down far enough so that they are able to work on all of the
Sheet2 data in columns A and B.

With those two formulas in place you will get a list of destinations
in Sheet2, starting at G2, depending on the Account No entered into
Sheet2 E2.
With the data supplied; R0335 in Sheet2 E2 results in Ireland in G2
and Italy in G3; while C0723 in Sheet2 E2 results in France in G2; etc
for the other Account numbers.

Put the heading "Destinations" into Sheet2 G1.
Select G1 then make the cells below this heading a Dynamic Named Range
named "Destinations" by going Insert|Name|Define to bring up the
Define Name dialog.

Into the Names in workbook: box type...

Destinations

Into the Refers to: box type this formula...

=OFFSET(Sheet2!$G$1,1,0,SUMPRODUCT(--(Sheet2!$G$2:$G$200<"")),1)

This formula will handle a list of up to 199 destinations. I am
guessing that this number of destinations is unlikely to be exceeded
by any of the Account numbers. If this is not the case then increase
the 200 in the Sheet2!$G$2:$G$200<"" part of the formula to a
suitably larger number.

Click the Add button then OK.

On Sheet1 select as many column E cells (Column E on Sheet1 is your
Destination column according to your last post) that you need to have
a data validation drop down for the applicable destinations.
Go Data|Validation to bring up the Data Validation dialog. In the
Allow: box on the Settings tab select List and in the Source: box
type...

=Destinations

then click OK.

The next thing you need is the tiniest bit of code in the Sheet1 code
module that detects which Sheet1 column E (Destination) cell has been
selected by the user so that the appropriate Account No can be entered
into Sheet2 E2 resulting in the appropriate destination values into
the drop down.

Copy this code (next 7 lines of text)...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E2:E" & _
Cells(Rows.Count, 3).End(xlUp).Row)) Is Nothing Then
Worksheets("Sheet2").Range("E2").Value = _
Target.Offset(0, -2).Value
End If
End Sub

then right click the Sheet1 tab and select "View Code" from the pop up
menu. Then paste the code into the Sheet1 code module. After saving go
File|Return to Microsoft Excel.

With this code in place you might have to change the level of Security
applied to the workbook. The highest level that can be used and have
macro code operate is Medium, and then when the user opens the
workbook they need to click the "Enable macros" button on the Security
dialog that pops up.

If you have any problems just email me (Look in my Profile) and I will
reply with an example workbook.

Ken Johnson

Lookup duplicate items in a list
 

On Sep 6, 11:52*am, Ken Johnson wrote:
On Sep 6, 1:24*am, Niall wrote:



Max,
Many many thanks for the reply but I would say that I have lost it
somewhere. I entered the formulae where instructed but nothing happened - all
columns are blank when I enter a customer number. *This is exactly the sheet1
and sheet2 layouts:

Sheet1 (Billing)

* * * * *A * * * * * * B * * * * * * C * * * * * * D * * * * * E * * * * * *
* *F * * * * * G * * * * *H
1 * Date * * *Docket No * Account * *Customer *Destination *Radial * Drops *
Value
2 * 22-4-06 *12345 * * * *R0335 * * *Mr. J Blog *(List of his destinations
should drop here)

Sheet2 (Site Radial)

* * * * *A * * * * * * B * * * * * * * C
1 * *Acc * * * Destination * *Radial
2 * *B0015 * Germany * * * * *32
3 * *C0723 * France * * * * * * 28
4 * *R0335 * Ireland * * * * * * 4
5 * *F0005 * Spain * * * * * * * 26
6 * *R0335 * Italy * * * * * * * * 31
and so on.... with account number repeating with different destinations

I just needs the list of destinations to be on a drop down list when the acc
is entered so that the operator can select the desired destination.

Again thanks and let me know if it possible and if your first reply is right
where did I go wrong.

Kindest regards

Niall

"Max" wrote:
Source data in Sheet2 as posted, from row2 down

In Sheet1,
Input in A2 (Cust), eg: R0335

Put in B2:
=IF($A$2="","",IF(Sheet2!A2=$A$2,ROW(),""))
Leave B1 blank

Put in C2:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(Sheet2!B:B,SMAL L(B:B,ROWS($1:1))))
Copy B2:C2 down to cover the max expected extent of data in Sheet2, eg down
to C200? Minimize/hide col B. Col C will return all destinations for the
input in A2, neatly packed at the top. You can easily build on the other
lookups in adjacent cols pointing to the multiple returns in col C.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:54
xdemechanik
---
"Niall" wrote:
Hello, I hope that some1 can help me. *I have a spreadsheet which looksup a
list in another sheet of the same workbook based on what account number is
entered in a particular cell. For example,
Sheet1
* * * * *A * * * * * * * B * * * * * * * C * * * * * * D
1 *Customer * *Destnation * *Rate * * * *Value
2 * R0335 * * * *=vlookup.. *
3
4

Sheet2
* * * * *A * * * * * * * B * * * * * * * C * * * * * * D
1 *Customer * *Destination
2 * A0022 * * * *Ireland
3 * D1034 * * * *Scotland
4 * R0335 * * * *Ireland
5 * A0022 * * * *Wales
6 * R0335 * * * *France
7 * C0078 * * * *Spain
8 * R0335 * * * *Germany

Basically, when I enter the a/c number R0335 I need cell B2 on sheet1 to
list the destinations that R0335 go to, so the operator can list the one that
is required and then cell C2 will look up the rate on B2 using the vlookup
function.

Any help would be gratefully appreciated as this is proving very hard for me
to keep calculating manually as there are about 250 per day.

Thanking you all in advance

Niall

Hi Niall,

Max's formulas work very nicely. I have used them on Sheet2 to produce
a list of destinations depending on the Account No.

The only problem is that you are wanting the list of destinations to
appear in a drop down list on Sheet1. You can't produce a drop down
list in a cell just by using formulas.

Try the following...

On Sheet2 in F2...
=IF($E$2="","",IF(A2=$E$2,ROW(),""))

and Sheet2 in G2...
=IF(ROWS($1:1)COUNT(F:F),"",INDEX(B:B,SMALL(F:F,R OWS($1:1))))

These are Max's formulas adjusted for working on Sheet2. Fill them
both down far enough so that they are able to work on all of the
Sheet2 data in columns A and B.

With those two formulas in place you will get a list of destinations
in Sheet2, starting at G2, depending on the Account No entered into
Sheet2 E2.
With the data supplied; R0335 in Sheet2 E2 results in Ireland in G2
and Italy in G3; while C0723 in Sheet2 E2 results in France in G2; etc
for the other Account numbers.

Put the heading "Destinations" into Sheet2 G1.
Select G1 then make the cells below this heading a Dynamic Named Range
named "Destinations" by going Insert|Name|Define to bring up the
Define Name dialog.

Into the Names in workbook: box type...

Destinations

Into the Refers to: box type this formula...

=OFFSET(Sheet2!$G$1,1,0,SUMPRODUCT(--(Sheet2!$G$2:$G$200<"")),1)

This formula will handle a list of up to 199 destinations. I am
guessing that this number of destinations is unlikely to be exceeded
by any of the Account numbers. If this is not the case then increase
the 200 in the Sheet2!$G$2:$G$200<"" part of the formula to a
suitably larger number.

Click the Add button then OK.

On Sheet1 select as many column E cells (Column E on Sheet1 is your
Destination column according to your last post) that you need to have
a data validation drop down for the applicable destinations.
Go Data|Validation to bring up the Data Validation dialog. In the
Allow: box on the Settings tab select List and in the Source: box
type...

=Destinations

then click OK.

The next thing you need is the tiniest bit of code in the Sheet1 code
module that detects which Sheet1 column E (Destination) cell has been
selected by the user so that the appropriate Account No can be entered
into Sheet2 E2 resulting in the appropriate destination values into
the drop down.

Copy this code (next 7 lines of text)...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E2:E" & _
Cells(Rows.Count, 3).End(xlUp).Row)) Is Nothing Then
Worksheets("Sheet2").Range("E2").Value = _
Target.Offset(0, -2).Value
End If
End Sub

then right click the Sheet1 tab and select "View Code" from the pop up
menu. Then paste the code into the Sheet1 code module. After saving go
File|Return to Microsoft Excel.

With this code in place you might have to change the level of Security
applied to the workbook. The highest level that can be used and have
macro code operate is Medium, and then when the user opens the
workbook they need to click the "Enable macros" button on the Security
dialog that pops up.

If you have any problems just email me (Look in my Profile) and I will
reply with an example workbook.

Ken Johnson

Oops!
Just read Max's last post and noticed I neglected to use your supplied
Sheet names.

The formula to use in the Refers to: box on the Define Names dialog
should have been...

=OFFSET('Site Radial'!$G$1,1,0,SUMPRODUCT(--('Site Radial'!$G$2:$G
$200<"")),1)

(That really didn't affect me since Excel automatically made the
necessary change when I changed the Sheet2 name to Site Radial.)

The code in the Sheet1 code module should be...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("E2:E" & _
Cells(Rows.Count, 3).End(xlUp).Row)) Is Nothing Then
Worksheets("Site Radial").Range("E2").Value = _
Target.Offset(0, -2).Value
End If
End Sub

Ken Johnson