Hello,

Heres my issue - I have a single piece of criteria (ie - Store #), and given
this, I need to look at a large dump of data, and return all the values for
this particular store number.

Example :
Here is the data dump :
A B C D
Store # Account # Date Amount
1 2885 120000 05/17/06 100.00
2 2950 130405 06/17/06 50.00
3 2885 130402 04/20/04 200.00
4 2950 126210 08/17/05 50.00

Now, If I am running a report on Store #2885 - what I need the formula to do
is give me all pieces of information that relate to that store. Here is what
the end result of the formula should be :

Store # Account # Date Amount
2885 120000 05/17/06 100.00
2885 130402 04/20/04 200.00

Can anybody help me ?

Thanks!
Rob

You should look at setting up an autofilter.

DataFilterAutofilter and filter on the store#

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Rob" wrote in message
...
Hello,

Heres my issue - I have a single piece of criteria (ie - Store #), and
given
this, I need to look at a large dump of data, and return all the values
for
this particular store number.

Example :
Here is the data dump :
A B C D
Store # Account # Date Amount
1 2885 120000 05/17/06 100.00
2 2950 130405 06/17/06 50.00
3 2885 130402 04/20/04 200.00
4 2950 126210 08/17/05 50.00

Now, If I am running a report on Store #2885 - what I need the formula to
do
is give me all pieces of information that relate to that store. Here is
what
the end result of the formula should be :

Store # Account # Date Amount
2885 120000 05/17/06 100.00
2885 130402 04/20/04 200.00

Can anybody help me ?

Thanks!
Rob

The only issue is this - the "dump" of data is located on a different tab.

Example - tab #1 is the dump of data. Tab #2 is a specific store (2885), and
tab #3 is another store - and so on.

So I basically need to get all the individual store data on a specific tab
(based on the criteria).

Thanks!
Rob

"Nick Hodge" wrote:

You should look at setting up an autofilter.

DataFilterAutofilter and filter on the store#

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Rob" wrote in message
...
Hello,

Heres my issue - I have a single piece of criteria (ie - Store #), and
given
this, I need to look at a large dump of data, and return all the values
for
this particular store number.

Example :
Here is the data dump :
A B C D
Store # Account # Date Amount
1 2885 120000 05/17/06 100.00
2 2950 130405 06/17/06 50.00
3 2885 130402 04/20/04 200.00
4 2950 126210 08/17/05 50.00

Now, If I am running a report on Store #2885 - what I need the formula to
do
is give me all pieces of information that relate to that store. Here is
what
the end result of the formula should be :

Store # Account # Date Amount
2885 120000 05/17/06 100.00
2885 130402 04/20/04 200.00

Can anybody help me ?

Thanks!
Rob

How big is "the dump" ?

x columns by x rows

Biff

"Rob" wrote in message
...
The only issue is this - the "dump" of data is located on a different tab.

Example - tab #1 is the dump of data. Tab #2 is a specific store (2885),
and
tab #3 is another store - and so on.

So I basically need to get all the individual store data on a specific tab
(based on the criteria).

Thanks!
Rob

"Nick Hodge" wrote:

You should look at setting up an autofilter.

DataFilterAutofilter and filter on the store#

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Rob" wrote in message
...
Hello,

Heres my issue - I have a single piece of criteria (ie - Store #), and
given
this, I need to look at a large dump of data, and return all the values
for
this particular store number.

Example :
Here is the data dump :
A B C D
Store # Account # Date Amount
1 2885 120000 05/17/06 100.00
2 2950 130405 06/17/06 50.00
3 2885 130402 04/20/04 200.00
4 2950 126210 08/17/05 50.00

Now, If I am running a report on Store #2885 - what I need the formula
to
do
is give me all pieces of information that relate to that store. Here
is
what
the end result of the formula should be :

Store # Account # Date Amount
2885 120000 05/17/06 100.00
2885 130402 04/20/04 200.00

Can anybody help me ?

Thanks!
Rob

The dump of data starts in A1 and goes to E4000

"Biff" wrote:

How big is "the dump" ?

x columns by x rows

Biff

"Rob" wrote in message
...
The only issue is this - the "dump" of data is located on a different tab.

Example - tab #1 is the dump of data. Tab #2 is a specific store (2885),
and
tab #3 is another store - and so on.

So I basically need to get all the individual store data on a specific tab
(based on the criteria).

Thanks!
Rob

"Nick Hodge" wrote:

You should look at setting up an autofilter.

DataFilterAutofilter and filter on the store#

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Rob" wrote in message
...
Hello,

Heres my issue - I have a single piece of criteria (ie - Store #), and
given
this, I need to look at a large dump of data, and return all the values
for
this particular store number.

Example :
Here is the data dump :
A B C D
Store # Account # Date Amount
1 2885 120000 05/17/06 100.00
2 2950 130405 06/17/06 50.00
3 2885 130402 04/20/04 200.00
4 2950 126210 08/17/05 50.00

Now, If I am running a report on Store #2885 - what I need the formula
to
do
is give me all pieces of information that relate to that store. Here
is
what
the end result of the formula should be :

Store # Account # Date Amount
2885 120000 05/17/06 100.00
2885 130402 04/20/04 200.00

Can anybody help me ?

Thanks!
Rob

Ok, one more question, well, maybe two or three.....

Out of those 4000 rows approximately how many will typically be associated
with store # 2885?

How many different store #'s are there? I assume you want to extract the
data for each different store to its own sheet?

Can you sort the "dump" sheet by store # ?

Is the "dump" static or does it change on a regular basis (a new "dump"
everyday?) Is the size of the "dump" always the same?

A lot of questions but this is the type of info that determines your best
approach to this. This *could* be done with formulas but it probably isn't
the best approach. If you do this regularly I'd turn on the macro recorder
then apply a filter and start filtering on the store #'s, copy/paste to the
appropriate sheets. Do it once manually and thereafter you have the macro to
do it for you automatically.

Biff

"Rob" wrote in message
...
The dump of data starts in A1 and goes to E4000

"Biff" wrote:

How big is "the dump" ?

x columns by x rows

Biff

"Rob" wrote in message
...
The only issue is this - the "dump" of data is located on a different
tab.

Example - tab #1 is the dump of data. Tab #2 is a specific store
(2885),
and
tab #3 is another store - and so on.

So I basically need to get all the individual store data on a specific
tab
(based on the criteria).

Thanks!
Rob

"Nick Hodge" wrote:

You should look at setting up an autofilter.

DataFilterAutofilter and filter on the store#

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Rob" wrote in message
...
Hello,

Heres my issue - I have a single piece of criteria (ie - Store #),
and
given
this, I need to look at a large dump of data, and return all the
values
for
this particular store number.

Example :
Here is the data dump :
A B C D
Store # Account # Date Amount
1 2885 120000 05/17/06 100.00
2 2950 130405 06/17/06 50.00
3 2885 130402 04/20/04 200.00
4 2950 126210 08/17/05 50.00

Now, If I am running a report on Store #2885 - what I need the
formula
to
do
is give me all pieces of information that relate to that store.
Here
is
what
the end result of the formula should be :

Store # Account # Date Amount
2885 120000 05/17/06 100.00
2885 130402 04/20/04 200.00

Can anybody help me ?

Thanks!
Rob

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

Rob wrote:

Hello,

Heres my issue - I have a single piece of criteria (ie - Store #), and given
this, I need to look at a large dump of data, and return all the values for
this particular store number.

Example :
Here is the data dump :
A B C D
Store # Account # Date Amount
1 2885 120000 05/17/06 100.00
2 2950 130405 06/17/06 50.00
3 2885 130402 04/20/04 200.00
4 2950 126210 08/17/05 50.00

Now, If I am running a report on Store #2885 - what I need the formula to do
is give me all pieces of information that relate to that store. Here is what
the end result of the formula should be :

Store # Account # Date Amount
2885 120000 05/17/06 100.00
2885 130402 04/20/04 200.00

Can anybody help me ?

Thanks!
Rob

--

Dave Peterson

Thanks BIFF !
Disregad my last message - once I saved the file to my computer the formula
worked.

Last question - would it be possible to return values if searching for two
pieces of criteria instead of one ? In our example, we were looking at a
particular store (#2885) - can we incorporate this number, and another number
?

When we are creating new stores, we assign a project number at inception.
Once the store opens, its assigned a store number - therefore we can have
data in both the project number and store number.

Can the formula you provided me with incorporate two numbers ?

=IF(ROWS($1:1)<=$B$1,INDEX(Dump!B$2:B$5,SMALL(IF(D ump!$A$2:$A$5=$A$1&A2+0,ROW(Dump!B$2:B$5)-ROW(Dump!B$2)+1),ROWS($1:1))),"")

THANKS!
Rob

"Dave Peterson" wrote:

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

Rob wrote:

Hello,

Heres my issue - I have a single piece of criteria (ie - Store #), and given
this, I need to look at a large dump of data, and return all the values for
this particular store number.

Example :
Here is the data dump :
A B C D
Store # Account # Date Amount
1 2885 120000 05/17/06 100.00
2 2950 130405 06/17/06 50.00
3 2885 130402 04/20/04 200.00
4 2950 126210 08/17/05 50.00

Now, If I am running a report on Store #2885 - what I need the formula to do
is give me all pieces of information that relate to that store. Here is what
the end result of the formula should be :

Store # Account # Date Amount
2885 120000 05/17/06 100.00
2885 130402 04/20/04 200.00

Can anybody help me ?

Thanks!
Rob

--

Dave Peterson

once I saved the file to my computer the formula worked.

Yeah, that's how that formula works. I had assumed the file already existed.
A file doesn't exist until it's saved with a file name and the formula
parses the file name to get the sheet name. If there's no file name then the
formula errors.

Ok......

We can probably get this to work based on either a store number or a project
number. I'd need to when/how a project number is assigned a store number.
For example, if column A is for the store # and column ?? contains the
project number, I assume the store number cell is empty until the project is
completed then the store # is assigned. So, does that mean there is a sheet
for that project number? If so, then I also assume that once the store # is
assigned you then change the sheet name from the project number to the store
# ? Once the project is completed and a store # is assigned does that mean
you go back to all the empty cells in column A for that project and then
fill in a store # ?

Let me know!

Biff

"Rob" wrote in message
...
Thanks BIFF !
Disregad my last message - once I saved the file to my computer the
formula
worked.

Last question - would it be possible to return values if searching for two
pieces of criteria instead of one ? In our example, we were looking at a
particular store (#2885) - can we incorporate this number, and another
number
?

When we are creating new stores, we assign a project number at inception.
Once the store opens, its assigned a store number - therefore we can have
data in both the project number and store number.

Can the formula you provided me with incorporate two numbers ?

=IF(ROWS($1:1)<=$B$1,INDEX(Dump!B$2:B$5,SMALL(IF(D ump!$A$2:$A$5=$A$1&A2+0,ROW(Dump!B$2:B$5)-ROW(Dump!B$2)+1),ROWS($1:1))),"")

THANKS!
Rob

"Dave Peterson" wrote:

You may want to look at the way Ron de Bruin and Debra Dalgleish
approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list;
macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

Rob wrote:

Hello,

Heres my issue - I have a single piece of criteria (ie - Store #), and
given
this, I need to look at a large dump of data, and return all the values
for
this particular store number.

Example :
Here is the data dump :
A B C D
Store # Account # Date Amount
1 2885 120000 05/17/06 100.00
2 2950 130405 06/17/06 50.00
3 2885 130402 04/20/04 200.00
4 2950 126210 08/17/05 50.00

Now, If I am running a report on Store #2885 - what I need the formula
to do
is give me all pieces of information that relate to that store. Here
is what
the end result of the formula should be :

Store # Account # Date Amount
2885 120000 05/17/06 100.00
2885 130402 04/20/04 200.00

Can anybody help me ?

Thanks!
Rob

--

Dave Peterson

HI Biff,
This actually can be done one of two ways - what ever is easier for you :

Option 1 - the "Dump" of data will contain both Store numbers and Project
numbers in the same column.

(ie - Store 2885 and Project 408465 are for the same thing).

Or
Option 2 - I can arrange it so that there are two "dumps" of data - one for
the store information, and one for the project information.

Whatever makes it easier.
Let me know your thoughts.
Thanks!
Rob

"Biff" wrote:

once I saved the file to my computer the formula worked.

Yeah, that's how that formula works. I had assumed the file already existed.
A file doesn't exist until it's saved with a file name and the formula
parses the file name to get the sheet name. If there's no file name then the
formula errors.

Ok......

We can probably get this to work based on either a store number or a project
number. I'd need to when/how a project number is assigned a store number.
For example, if column A is for the store # and column ?? contains the
project number, I assume the store number cell is empty until the project is
completed then the store # is assigned. So, does that mean there is a sheet
for that project number? If so, then I also assume that once the store # is
assigned you then change the sheet name from the project number to the store
# ? Once the project is completed and a store # is assigned does that mean
you go back to all the empty cells in column A for that project and then
fill in a store # ?

Let me know!

Biff

"Rob" wrote in message
...
Thanks BIFF !
Disregad my last message - once I saved the file to my computer the
formula
worked.

Last question - would it be possible to return values if searching for two
pieces of criteria instead of one ? In our example, we were looking at a
particular store (#2885) - can we incorporate this number, and another
number
?

When we are creating new stores, we assign a project number at inception.
Once the store opens, its assigned a store number - therefore we can have
data in both the project number and store number.

Can the formula you provided me with incorporate two numbers ?

=IF(ROWS($1:1)<=$B$1,INDEX(Dump!B$2:B$5,SMALL(IF(D ump!$A$2:$A$5=$A$1&A2+0,ROW(Dump!B$2:B$5)-ROW(Dump!B$2)+1),ROWS($1:1))),"")

THANKS!
Rob

"Dave Peterson" wrote:

You may want to look at the way Ron de Bruin and Debra Dalgleish
approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list;
macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

Rob wrote:

Hello,

Heres my issue - I have a single piece of criteria (ie - Store #), and
given
this, I need to look at a large dump of data, and return all the values
for
this particular store number.

Example :
Here is the data dump :
A B C D
Store # Account # Date Amount
1 2885 120000 05/17/06 100.00
2 2950 130405 06/17/06 50.00
3 2885 130402 04/20/04 200.00
4 2950 126210 08/17/05 50.00

Now, If I am running a report on Store #2885 - what I need the formula
to do
is give me all pieces of information that relate to that store. Here
is what
the end result of the formula should be :

Store # Account # Date Amount
2885 120000 05/17/06 100.00
2885 130402 04/20/04 200.00

Can anybody help me ?

Thanks!
Rob

--

Dave Peterson

Ok.....keeping with the "theme" of automation (or, as much of it as
possible)

Option 1 - the "Dump" of data will contain both Store numbers and Project
numbers in the same column.
(ie - Store 2885 and Project 408465 are for the same thing).

Option 2 would lead to extra work and having multiple data sources really
complicates things. So, option 1 it is!

Something I don't understand is how you know a project # and a store # are
related. I guess that would be called "local knowledge"!

Let's assume the store/proj numbers look like this at this time:

408465
2095
408465

*YOU* know that 408465 is a project number and 2095 is a store number but to
Excel they're equal in that they're just numbers. At a later time project
408465 is completed and now becomes store number 2885. So, now the list
might look like this:

408465
2095
408465
2885
2885

We need to come up with some method to determine when a project graduates
into a store so we can extract both categories of data. I assume you
have/want a sheet for project 408465 data and when 408465 graduates into
2885 to also extract that data.

So.............

Here's what I came up with..........

You create the project sheet and name it 408465. When 408465 graduates into
store number 2885 you then rename the sheet like this: 2885-408465.

To extract all the data for both the project and the sto

On sheet 408465:

A1 = header = Store #
A2 = header = Project #
A3 = header = Total Records

B1 = formula = will return the store # from the sheet name tab

=IF(ISERROR(FIND("-",MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))),"",--LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),FIND("-",MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))-1))

B2 = formula = will return the project # from the sheet name tab

=IF(ISERROR(FIND("-",MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))),--MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),--MID(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),FIND("-",MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))+1,255))

B3 = formula = will return the total number of records

=SUMPRODUCT(COUNTIF(Dump!A:A,B1:B2))

When the status is still in the project phase and the sheet name is just the
project number only that value will appear in cells B1 and B2: Sheet name =
408465

..............A......................B
1......Store #....................
2......Project #............408465

When the project graduates into a store and you rename the sheet:
2885-408465, then the store number will appear in B1:

..............A......................B
1......Store #................2885
2......Project #.............408465

Note: I'm assuming that both project numbers and store numbers will only
contain numeric digits. That's how I wrote the formulas to extract from the
sheet name.

Now, the formula to extract the data:

A5:C5 = headers = Account #, Date, Amount

Array entered formula in A6:

=IF(ROWS($1:1)<=$B$3,INDEX(Dump!B$2:B$10,SMALL(IF( ISNUMBER(MATCH(Dump!$A$2:$A$10,$B$1:$B$2,0)),ROW(D ump!B$2:B$10)-ROW(Dump!B$2)+1),ROWS($1:1))),"")

Copy across to C6 then down (as before). You'll have to reformat B6:Bn as
DATE.

Biff

" wrote in message
...
HI Biff,
This actually can be done one of two ways - what ever is easier for you :

Option 1 - the "Dump" of data will contain both Store numbers and Project
numbers in the same column.

(ie - Store 2885 and Project 408465 are for the same thing).

Or
Option 2 - I can arrange it so that there are two "dumps" of data - one
for
the store information, and one for the project information.

Whatever makes it easier.
Let me know your thoughts.
Thanks!
Rob

"Biff" wrote:

once I saved the file to my computer the formula worked.

Yeah, that's how that formula works. I had assumed the file already
existed.
A file doesn't exist until it's saved with a file name and the formula
parses the file name to get the sheet name. If there's no file name then
the
formula errors.

Ok......

We can probably get this to work based on either a store number or a
project
number. I'd need to when/how a project number is assigned a store number.
For example, if column A is for the store # and column ?? contains the
project number, I assume the store number cell is empty until the project
is
completed then the store # is assigned. So, does that mean there is a
sheet
for that project number? If so, then I also assume that once the store #
is
assigned you then change the sheet name from the project number to the
store
# ? Once the project is completed and a store # is assigned does that
mean
you go back to all the empty cells in column A for that project and then
fill in a store # ?

Let me know!

Biff

"Rob" wrote in message
...
Thanks BIFF !
Disregad my last message - once I saved the file to my computer the
formula
worked.

Last question - would it be possible to return values if searching for
two
pieces of criteria instead of one ? In our example, we were looking at
a
particular store (#2885) - can we incorporate this number, and another
number
?

When we are creating new stores, we assign a project number at
inception.
Once the store opens, its assigned a store number - therefore we can
have
data in both the project number and store number.

Can the formula you provided me with incorporate two numbers ?

=IF(ROWS($1:1)<=$B$1,INDEX(Dump!B$2:B$5,SMALL(IF(D ump!$A$2:$A$5=$A$1&A2+0,ROW(Dump!B$2:B$5)-ROW(Dump!B$2)+1),ROWS($1:1))),"")

THANKS!
Rob

"Dave Peterson" wrote:

You may want to look at the way Ron de Bruin and Debra Dalgleish
approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to
create
separate sheet of orders for each sales rep visible in a filtered
list;
macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with
current.
AdvFilterCity.xls 55 kb

Rob wrote:

Hello,

Heres my issue - I have a single piece of criteria (ie - Store #),
and
given
this, I need to look at a large dump of data, and return all the
values
for
this particular store number.

Example :
Here is the data dump :
A B C D
Store # Account # Date Amount
1 2885 120000 05/17/06 100.00
2 2950 130405 06/17/06 50.00
3 2885 130402 04/20/04 200.00
4 2950 126210 08/17/05 50.00

Now, If I am running a report on Store #2885 - what I need the
formula
to do
is give me all pieces of information that relate to that store.
Here
is what
the end result of the formula should be :

Store # Account # Date Amount
2885 120000 05/17/06 100.00
2885 130402 04/20/04 200.00

Can anybody help me ?

Thanks!
Rob

--

Dave Peterson