Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Candice H.
 
Posts: n/a
Default Extracting/look up data from a list and select multiple instances

Hi all..

I have a situation that is a bit unique and interesting. I have a list of
account numbers that have invoices linked to them. From this vast list, I
have around 10 -15 different account numbers that I would like the invoice
numbers (and associated information) pulled from the list and put into
another area (on the same worksheet). The problem is that an account number
may appear multiple times in the list (have more than 1 invoice) and when I
do Vlookup or Vlookups it's only returning the first invoice. I would like
to have it look in the big list for the account number, and return all
instances associated with that account number.

Here's an example:

A B C D E
F G
1 090WEN111 111012 $20.00 Paper 4/25/05
090PAP112
2 090PAP112 112141 $100.00 Tissue 1/25/05
090TIM412
3 090PAP112 114571 $58.23 Paper 3/1/05
4 090TIM412 142577 $45.21 SANI 5/1/05

so in this example, columns A - E have all the info that comes from a
database report. Column G has a select list of accounts that I need the data
for. Instead of me having to go through hundreds of records, I would like to
have Excel find these records and place them into a separate area on the same
worksheet.

I know that this one is a bit long and drawn out but I really would
appreciate any help someone could offer... Thanks in advance!
  #2   Report Post  
Nick
 
Posts: n/a
Default

You could use the AutoFilter command on the Data menu
Or the advanced filter to copy the selected invoices to another range.

Or better still store this information a database, it's this sort of thing
MS Access was made for.

Nick

"Candice H." <Candice wrote in message
...
Hi all..

I have a situation that is a bit unique and interesting. I have a list of
account numbers that have invoices linked to them. From this vast list, I
have around 10 -15 different account numbers that I would like the invoice
numbers (and associated information) pulled from the list and put into
another area (on the same worksheet). The problem is that an account
number
may appear multiple times in the list (have more than 1 invoice) and when
I
do Vlookup or Vlookups it's only returning the first invoice. I would
like
to have it look in the big list for the account number, and return all
instances associated with that account number.

Here's an example:

A B C D E
F G
1 090WEN111 111012 $20.00 Paper 4/25/05
090PAP112
2 090PAP112 112141 $100.00 Tissue 1/25/05
090TIM412
3 090PAP112 114571 $58.23 Paper 3/1/05
4 090TIM412 142577 $45.21 SANI 5/1/05

so in this example, columns A - E have all the info that comes from a
database report. Column G has a select list of accounts that I need the
data
for. Instead of me having to go through hundreds of records, I would like
to
have Excel find these records and place them into a separate area on the
same
worksheet.

I know that this one is a bit long and drawn out but I really would
appreciate any help someone could offer... Thanks in advance!



  #3   Report Post  
Candice H.
 
Posts: n/a
Default

Thanks for replying. The only thing about using filters is that I need to
create a pivot table from the data and I would have multiple (like 15)
different accounts to filter by.

MsAccess could be an option but once again, I am creating a pivot table
based on the data.

"Nick" wrote:

You could use the AutoFilter command on the Data menu
Or the advanced filter to copy the selected invoices to another range.

Or better still store this information a database, it's this sort of thing
MS Access was made for.

Nick

"Candice H." <Candice wrote in message
...
Hi all..

I have a situation that is a bit unique and interesting. I have a list of
account numbers that have invoices linked to them. From this vast list, I
have around 10 -15 different account numbers that I would like the invoice
numbers (and associated information) pulled from the list and put into
another area (on the same worksheet). The problem is that an account
number
may appear multiple times in the list (have more than 1 invoice) and when
I
do Vlookup or Vlookups it's only returning the first invoice. I would
like
to have it look in the big list for the account number, and return all
instances associated with that account number.

Here's an example:

A B C D E
F G
1 090WEN111 111012 $20.00 Paper 4/25/05
090PAP112
2 090PAP112 112141 $100.00 Tissue 1/25/05
090TIM412
3 090PAP112 114571 $58.23 Paper 3/1/05
4 090TIM412 142577 $45.21 SANI 5/1/05

so in this example, columns A - E have all the info that comes from a
database report. Column G has a select list of accounts that I need the
data
for. Instead of me having to go through hundreds of records, I would like
to
have Excel find these records and place them into a separate area on the
same
worksheet.

I know that this one is a bit long and drawn out but I really would
appreciate any help someone could offer... Thanks in advance!




  #4   Report Post  
Nick
 
Posts: n/a
Default

Hi Candice

Using advanced filter and copying the data to a different range would be
ideal for this.
You could specify the different accounts in your criteria range and then run
the filter.
Base your pivot table on the copied, filtered data and that should work.

Nick


"Candice H." wrote in message
...
Thanks for replying. The only thing about using filters is that I need to
create a pivot table from the data and I would have multiple (like 15)
different accounts to filter by.

MsAccess could be an option but once again, I am creating a pivot table
based on the data.

"Nick" wrote:

You could use the AutoFilter command on the Data menu
Or the advanced filter to copy the selected invoices to another range.

Or better still store this information a database, it's this sort of
thing
MS Access was made for.

Nick

"Candice H." <Candice wrote in message
...
Hi all..

I have a situation that is a bit unique and interesting. I have a list
of
account numbers that have invoices linked to them. From this vast
list, I
have around 10 -15 different account numbers that I would like the
invoice
numbers (and associated information) pulled from the list and put into
another area (on the same worksheet). The problem is that an account
number
may appear multiple times in the list (have more than 1 invoice) and
when
I
do Vlookup or Vlookups it's only returning the first invoice. I would
like
to have it look in the big list for the account number, and return all
instances associated with that account number.

Here's an example:

A B C D E
F G
1 090WEN111 111012 $20.00 Paper 4/25/05
090PAP112
2 090PAP112 112141 $100.00 Tissue 1/25/05
090TIM412
3 090PAP112 114571 $58.23 Paper 3/1/05
4 090TIM412 142577 $45.21 SANI 5/1/05

so in this example, columns A - E have all the info that comes from a
database report. Column G has a select list of accounts that I need
the
data
for. Instead of me having to go through hundreds of records, I would
like
to
have Excel find these records and place them into a separate area on
the
same
worksheet.

I know that this one is a bit long and drawn out but I really would
appreciate any help someone could offer... Thanks in advance!






  #5   Report Post  
Domenic
 
Posts: n/a
Default

Assuming that Columns A through E contain your data, and that the first
row contains your headers/labels, try the following...

First, define the following reference...

Insert Name Define

Name: AcctNum

Refers to:

=Sheet1!$G$2:INDEX(Sheet1!$G$2:$G$65536,MATCH(REPT ("z",255),Sheet1!$G$2:$
G$65536))

This will allow you to enter one or more account numbers in Column G,
starting from G2, without having to adjust its range reference in the
formula.

Then, enter the following formulas...

H1: enter 0 (zero)

H2, copied down:

=IF((A2<"")*(ISNUMBER(MATCH(A2,AcctNum,0))),LOOKU P(9.99999999999999E+307
,$H$1:H1)+1,"")

I1:

=LOOKUP(9.99999999999999E+307,H:H)

J2, copied down:

=IF(ROW()-ROW(J$2)+1<=$I$1,MATCH(ROW()-ROW(J$2)+1,H:H,0),"")

K2, copied across and down:

=IF(N($J2),INDEX(A:A,$J2),"")

Note that Columns H, I, and J can be hidden, if you so wish.

Hope this helps!

In article ,
"Candice H." <Candice wrote:

Hi all..

I have a situation that is a bit unique and interesting. I have a list of
account numbers that have invoices linked to them. From this vast list, I
have around 10 -15 different account numbers that I would like the invoice
numbers (and associated information) pulled from the list and put into
another area (on the same worksheet). The problem is that an account number
may appear multiple times in the list (have more than 1 invoice) and when I
do Vlookup or Vlookups it's only returning the first invoice. I would like
to have it look in the big list for the account number, and return all
instances associated with that account number.

Here's an example:

A B C D E
F G
1 090WEN111 111012 $20.00 Paper 4/25/05
090PAP112
2 090PAP112 112141 $100.00 Tissue 1/25/05
090TIM412
3 090PAP112 114571 $58.23 Paper 3/1/05
4 090TIM412 142577 $45.21 SANI 5/1/05

so in this example, columns A - E have all the info that comes from a
database report. Column G has a select list of accounts that I need the data
for. Instead of me having to go through hundreds of records, I would like to
have Excel find these records and place them into a separate area on the same
worksheet.

I know that this one is a bit long and drawn out but I really would
appreciate any help someone could offer... Thanks in advance!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Validation list boxes Watson Excel Discussion (Misc queries) 4 May 26th 05 05:12 PM
Auto scroll down data validation list [email protected] Excel Discussion (Misc queries) 4 January 28th 05 07:44 PM
Pivot Tables, can I use an external list to select data items? Brian Lofquist Excel Worksheet Functions 0 January 4th 05 07:43 PM
Selecting data from a list based on entered values GrantM Excel Discussion (Misc queries) 1 December 20th 04 11:59 AM
select from a fragmented list AndrewB Excel Worksheet Functions 6 December 3rd 04 12:25 AM


All times are GMT +1. The time now is 09:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"