Macro to make list from data

[mnewns76]

Hi,

I have a sheet with data on customer deliveries by day, it has twenty some columns with information and covers several hundred rows of information relating to individual deliveries.

On a separate sheet in the workbook I would like to summarise deliveries by a date - ie select/type in a date one week ago and get a list of deliveries with chosen columns of data. a bit like a vlookup but a bit cleverer as there will be varying amounts of data per day.

Is there a simple way through macro/formula that I could return this list of data?

many thanks in advance
Mat

 

[borgem]

Have you looked into simply using the autofilter or pivot table functions built into excel? Unless you are looking to automate the process, either of these might suit your needs

 

[texasalynn]

Welcome to MrExcel Board....

here is a sample of how to setup a sheet for getting a formula to return multiple values of one criteria

Excel Workbook
	A	B	C	D	E
1	Department	Name	Address	Phone	Email
2	Accounting	Joe Smith	123 A Street	281-555-7171	Smith.J@abc.com
3	Accounting	Steve Gates	3356 City Avenue	713-555-1124	Gates.S@abc.com
4	Accounting	Debbie Anston	7517 County Line	832-555-4004	Anston.D@abc.com
5	Collections	Hooker Mannis	311 Loveland Ave	713-555-1123	Mannis.H@abc.com
6	Collections	Tiger Woods	1697 Constance Dr	832-555-4003	Woods.T@abc.com
7	Collections	Tom Longfellow	4143 A Street	281-555-7177	Longfellow.T@abc.com
8	Customer Service	Frank Ling	350 City Court Ln	281-555-7172	Ling.F@abc.com
9	Customer Service	Arnold Palmer	3312 Loveland Ave	713-555-1125	Palmer.A@abc.com
10	Customer Service	Phil Perry	8670 Constance Dr	832-555-4005	Perry.P@abc.com
11	Executive	Bill Jobs	1124 A Street	281-555-7173	Jobs.B@abc.com
12	Executive	Carl Reins	4357 City Avenue	713-555-1126	Reins.C@abc.com
13	Executive	Bonnie Rath	6522 County Line	832-555-4006	Rath.B@abc.com
14	Finance	Karl Reiner	355 City Avenue	713-555-1122	Reiner.K@abc.com
15	Finance	Mary Pierce	15700 County Line	832-555-4002	Pierce.M@abc.com
16	Finance	Debbie Winston	2350 City Court Ln	281-555-7176	Winston.D@abc.com
17	HR	Carol Lovely	1666 Constance Dr	832-555-4001	Lovely.C@abc.com
18	HR	Barbra Wings	1525 A Street	281-555-7175	Wings.B@abc.com
19	HR	Terri Mercer	7315 City Avenue	713-555-1128	Mercer.T@abc.com
20	Outside Sales	Bob Ricker	1515 County Line	832-555-4000	Ricker.B@abc.com
21	Outside Sales	Connie Gaston	2351 City Court Ln	281-555-7174	Gaston.C@abc.com
22	Outside Sales	Ken Smith	5310 Loveland Ave	713-555-1127	Smith.K@abc.com
Open

Excel 2003

Cell Formulas
Range	Formula
E2	=RIGHT(B2,LEN(B2)-FIND(" ",B2))&"."&LEFT(B2,1)&"@abc.com"
E3	=RIGHT(B3,LEN(B3)-FIND(" ",B3))&"."&LEFT(B3,1)&"@abc.com"
E4	=RIGHT(B4,LEN(B4)-FIND(" ",B4))&"."&LEFT(B4,1)&"@abc.com"
E5	=RIGHT(B5,LEN(B5)-FIND(" ",B5))&"."&LEFT(B5,1)&"@abc.com"
E6	=RIGHT(B6,LEN(B6)-FIND(" ",B6))&"."&LEFT(B6,1)&"@abc.com"
E7	=RIGHT(B7,LEN(B7)-FIND(" ",B7))&"."&LEFT(B7,1)&"@abc.com"
E8	=RIGHT(B8,LEN(B8)-FIND(" ",B8))&"."&LEFT(B8,1)&"@abc.com"
E9	=RIGHT(B9,LEN(B9)-FIND(" ",B9))&"."&LEFT(B9,1)&"@abc.com"
E10	=RIGHT(B10,LEN(B10)-FIND(" ",B10))&"."&LEFT(B10,1)&"@abc.com"
E11	=RIGHT(B11,LEN(B11)-FIND(" ",B11))&"."&LEFT(B11,1)&"@abc.com"
E12	=RIGHT(B12,LEN(B12)-FIND(" ",B12))&"."&LEFT(B12,1)&"@abc.com"
E13	=RIGHT(B13,LEN(B13)-FIND(" ",B13))&"."&LEFT(B13,1)&"@abc.com"
E14	=RIGHT(B14,LEN(B14)-FIND(" ",B14))&"."&LEFT(B14,1)&"@abc.com"
E15	=RIGHT(B15,LEN(B15)-FIND(" ",B15))&"."&LEFT(B15,1)&"@abc.com"
E16	=RIGHT(B16,LEN(B16)-FIND(" ",B16))&"."&LEFT(B16,1)&"@abc.com"
E17	=RIGHT(B17,LEN(B17)-FIND(" ",B17))&"."&LEFT(B17,1)&"@abc.com"
E18	=RIGHT(B18,LEN(B18)-FIND(" ",B18))&"."&LEFT(B18,1)&"@abc.com"
E19	=RIGHT(B19,LEN(B19)-FIND(" ",B19))&"."&LEFT(B19,1)&"@abc.com"
E20	=RIGHT(B20,LEN(B20)-FIND(" ",B20))&"."&LEFT(B20,1)&"@abc.com"
E21	=RIGHT(B21,LEN(B21)-FIND(" ",B21))&"."&LEFT(B21,1)&"@abc.com"
E22	=RIGHT(B22,LEN(B22)-FIND(" ",B22))&"."&LEFT(B22,1)&"@abc.com"

Excel Workbook
	A	B	C	D	E
1	Match Dept	HR	3
2	Department	Name	Address	Phone	Email
3	HR	Carol Lovely	1666 Constance Dr	832-555-4001	Lovely.C@abc.com
4	HR	Barbra Wings	1525 A Street	281-555-7175	Wings.B@abc.com
5	HR	Terri Mercer	7315 City Avenue	713-555-1128	Mercer.T@abc.com
6
7
8
Sheet2

Excel 2003

Cell Formulas
Range	Formula
C1	=COUNTIF(Open!$A$2:$A$22,B1)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[borgem]

Hi,

I have a sheet with data on customer deliveries by day, it has twenty some columns with information and covers several hundred rows of information relating to individual deliveries.

On a separate sheet in the workbook I would like to summarise deliveries by a date - ie select/type in a date one week ago and get a list of deliveries with chosen columns of data. a bit like a vlookup but a bit cleverer as there will be varying amounts of data per day.

Is there a simple way through macro/formula that I could return this list of data?

many thanks in advance
Mat

I set up a macro to create an autofilter that selects the header row only and then all contiguous data below is filtered based on user inputs. You would obviously want to tweak it a bit, but maybe this will get you going in the right direction? Here is the link for that post. The copy and paste bit probably doesn't apply as much to you, in this instance.