Vlookup to Return a Range of Data

[Rob]

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

 

[Nick Hodge]

You should look at setting up an autofilter.

Data>Filter>Autofilter and filter on the store#

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[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

 

[Biff]

How big is "the dump" ?

x columns by x rows

Biff

 

[Rob]

The dump of data starts in A1 and goes to E4000

How big is "the dump" ?

x columns by x rows

Biff

 

[Biff]

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]

Hi Biff,

Here are the answers to your questions ;

*Typically, there will probably be about 30-40 lines of data for a specific
store.

*Over the course of a year, there will be 200 different stores that need to
be analyzed.

*The "Dump" of data can be sorted anyway in which it needs to be (Store #)

*The "Dump" of data will be updated every month.

-Basically, as we open new stores we need to analyze all data for that new
store. We update this particular file every month - and add new stores (tabs)
as required.

Hope this helps.
Thanks!
Rob

 

[Dave Peterson]

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

 

[Biff]

Ok......

Here's how to do this using formulas. You can play around with it and see if
it'll fit your needs. If not, see Dave's reply.

We'll use just the small sample of data you posted.

A B C D
1 Store # Account # Date Amount
2 2885 120000 05/17/06 100.00
3 2950 130405 06/17/06 50.00
4 2885 130402 04/20/04 200.00
5 2950 126210 08/17/05 50.00

Assume that data is in a sheet named Dump in the range A1:B5, A11 being
the column headers.

Create a sheet and name it 2885 for store # 2885.

In A1 enter this formula:

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

This will return the sheet name 2885. This will also come into play later on
when creating sheets for the other stores.

In B1 enter this formula:

=COUNTIF(Dump!A:A,A1)

This will return the number of rows of data that there are in sheet Dump for
store # 2885.

Enter these column headers in A3:C3: Account #, Date, Amount

Enter this formula in A4. This is an array formula and MUST be entered using
the key combination of CTRL,SHIFT,ENTER:

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

Copy across to C4 then down. You'll need to copy the formula to enough rows
that all the data for store # 2885 is returned. In your reply you say this
typically about 30 to 40 rows, so, copy down maybe 50 rows.

That's it for store # 2885.

Now, let's create sheets for the other stores. This is pretty cool.........

Select sheet 2885.

Right click on the sheet tab.

Select Move or Copy....

Select Create a copy

OK

Select the copied sheet that has the name 2885 (2)

Right click the sheet tab and select Rename.

Rename the sheet 2950 (based on the store numbers of your posted sample
data)

That's it for store # 2950

Now, just repeat this process for the other store #'s.

Biff

 

[Rob]

Hi Biff,

Thanks for your input - I can now see how this works.
The only formula that did not work is this :

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

If I manually keyed in 2885, and followed all the other steps, the data does
come through properly - so if I can get that formula to work, you have saved
me !

Thanks!
Rob

Ok......

Here's how to do this using formulas. You can play around with it and see if
it'll fit your needs. If not, see Dave's reply.

We'll use just the small sample of data you posted.

A B C D
1 Store # Account # Date Amount
2 2885 120000 05/17/06 100.00
3 2950 130405 06/17/06 50.00
4 2885 130402 04/20/04 200.00
5 2950 126210 08/17/05 50.00

Assume that data is in a sheet named Dump in the range A1:B5, A11 being
the column headers.

Create a sheet and name it 2885 for store # 2885.

In A1 enter this formula:

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

This will return the sheet name 2885. This will also come into play later on
when creating sheets for the other stores.

In B1 enter this formula:

=COUNTIF(Dump!A:A,A1)

This will return the number of rows of data that there are in sheet Dump for
store # 2885.

Enter these column headers in A3:C3: Account #, Date, Amount

Enter this formula in A4. This is an array formula and MUST be entered using
the key combination of CTRL,SHIFT,ENTER:

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

Copy across to C4 then down. You'll need to copy the formula to enough rows
that all the data for store # 2885 is returned. In your reply you say this
typically about 30 to 40 rows, so, copy down maybe 50 rows.

That's it for store # 2885.

Now, let's create sheets for the other stores. This is pretty cool.........

Select sheet 2885.

Right click on the sheet tab.

Select Move or Copy....

Select Create a copy

OK

Select the copied sheet that has the name 2885 (2)

Right click the sheet tab and select Rename.

Rename the sheet 2950 (based on the store numbers of your posted sample
data)

That's it for store # 2950

Now, just repeat this process for the other store #'s.

Biff

Hi Biff,

Here are the answers to your questions ;

*Typically, there will probably be about 30-40 lines of data for a
specific
store.

*Over the course of a year, there will be 200 different stores that need
to
be analyzed.

*The "Dump" of data can be sorted anyway in which it needs to be (Store #)

*The "Dump" of data will be updated every month.

-Basically, as we open new stores we need to analyze all data for that new
store. We update this particular file every month - and add new stores
(tabs)
as required.

Hope this helps.
Thanks!
Rob

 

[Rob]

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(Dump!$A$2:$A$5=$A$1&A2+0,ROW(Dump!B$2:B$5)-ROW(Dump!B$2)+1),ROWS($1:1))),"")

THANKS!
Rob

 

[Biff]

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]

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]

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 store:

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(Dump!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

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