J
james
Previously you responded to a question concerning sorting data by project
number. Can this be done for a title, ie XXX-contract1, XXX-contract2,
YYY-Contract1 or some variation of this
Subject: Re: Vlookup to Return a Range of Data 5/29/2006 5:57 PM PST
By: Biff In: microsoft.public.excel.misc
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.
Assume that data is in a sheet named Dump in the range A1:B5, A1
1 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
number. Can this be done for a title, ie XXX-contract1, XXX-contract2,
YYY-Contract1 or some variation of this
Subject: Re: Vlookup to Return a Range of Data 5/29/2006 5:57 PM PST
By: Biff In: microsoft.public.excel.misc
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.
Assume that data is in a sheet named Dump in the range A1:B5, A1
1 beingthe 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