Sorting/Filtering Data in excel onto a summary tab

[HolyLemon]

I have an excel 2007 spreadsheet at my work, its about 30 colums across and contains a couple of thousand clients, one client per row.

I'm pretty happy with it at the moment Ive done some (what I would consider) nifty stuff when it comes to summarising the data on there, stuff per month, that kind of thing.

What I need to do is create a new worksheet, on which is a little box where you can enter the members number and either hit enter or a button or something and below the number you have entered and all of the information that is on that line will show up, preferably in an order that I choose. a "data extract".

I am a VBA/macro novice, I know how to open VBA, that is it. I used it alot in school but cant remember it at all.

Any help would be so amazingly appreciated.

NB, Some of the clients have Scheme codes to say if they are in a different country whereas UK clients dont so it would need to take that into account aswell.

If you could help me I would be so grateful

Thanks again.

Harry

 

[hiker95]

HolyLemon,


Sample worksheet with nothing in cell B4:

Excel Workbook
	A	B	C	D	E	F	G
1	Member search
2
3	Scheme Code
4	Member Number
5
6
7	Name					MGA (Assessment)Info
8	Current MG Category					Name of Assessor
9	Date of Referral					Date Assessment Completed
10	Referral Route					Assessment Score
11	Agreed PRMP?					Outcome of MGA
12	Retired/Deferred?					Condition1	?
13	For Assessment?					Condition2	?
14	For reassessment?					Condition3	?
15						Condition4	?
16
17	Date MG01 Sent?
18	Type of letter sent?
19	Date letter sent?
20
Member Search

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9	1. / Formula is =MOD(ROW(),2)=0	Abc
G9	1. / Formula is =MOD(ROW(),2)=0	Abc
B17	1. / Formula is =MOD(ROW(),2)=0	Abc
B19	1. / Formula is =MOD(ROW(),2)=0	Abc

I have changed the names on worksheet "Proposed MGP3" for the following two examples:

Excel Workbook
	A	B	C	D	E	F	G
1	Member search
2
3	Scheme Code	MAM
4	Member Number	212893
5
6
7	Name	hiker95				MGA (Assessment)Info
8	Current MG Category	Standard Risk				Name of Assessor	PWM
9	Date of Referral	2/1/2001				Date Assessment Completed	8/24/2007
10	Referral Route	MCD				Assessment Score	100
11	Agreed PRMP?	Yes				Outcome of MGA	Active
12	Retired/Deferred?	Y				Condition1	?
13	For Assessment?	Y				Condition2	?
14	For reassessment?	N				Condition3	?
15						Condition4	?
16
17	Date MG01 Sent?	2/1/2001
18	Type of letter sent?	No letter
19	Date letter sent?	5/21/2010
20
Member Search

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9	1. / Formula is =MOD(ROW(),2)=0	Abc
G9	1. / Formula is =MOD(ROW(),2)=0	Abc
B17	1. / Formula is =MOD(ROW(),2)=0	Abc
B19	1. / Formula is =MOD(ROW(),2)=0	Abc

Excel Workbook
	A	B	C	D	E	F	G
1	Member search
2
3	Scheme Code
4	Member Number	118118
5
6
7	Name	HolyLemon				MGA (Assessment)Info
8	Current MG Category					Name of Assessor
9	Date of Referral	1/19/2010				Date Assessment Completed	8/30/2007
10	Referral Route	MCD				Assessment Score
11	Agreed PRMP?					Outcome of MGA	Active
12	Retired/Deferred?					Condition1	?
13	For Assessment?	Y				Condition2	?
14	For reassessment?	Y				Condition3	?
15						Condition4	?
16
17	Date MG01 Sent?	1/19/2010
18	Type of letter sent?
19	Date letter sent?	5/27/2010
20
Member Search

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9	1. / Formula is =MOD(ROW(),2)=0	Abc
G9	1. / Formula is =MOD(ROW(),2)=0	Abc
B17	1. / Formula is =MOD(ROW(),2)=0	Abc
B19	1. / Formula is =MOD(ROW(),2)=0	Abc

There are question marks for the four "Conditions". I did not know what columns in worksheet "Proposed MGP3" to reference.

 

[HolyLemon]

Wow, you have done an amazing amount of work, I need to look into that lol, PS, the Conditions 1,2,3,4

They refer to the outcome 1,2,3,4 columns, sorry, my mistake there.

 

[HolyLemon]

Thank you so so much, ive put it in and thats amazing. I have one question about the formula though, I understand the string of arguements you used in it. I may seem ungrateful but im really not, is there a way to make it check for a scheme code aswell as the membership number, the problem being this:

Sometimes there is a member number lets say 50 if there is no scheme code this is for the uk, but round the world, in another country, the membership numbers start from scratch again, so on the spreadsheet, there could be 2 50's, one of them would have a scheme code for the uk, one could have one for america, one for australia, one for hong kong there could be 4 numbers, the only difference being the scheme code. Is it possible to make that another point of entry so it can filter between the numbers with/without the various scheme codes?

 

[HolyLemon]

Sorry about multiple posts. Ive been looking at this and a couple of other spreadsheets today and ive managed to edit it a little with some of your formulas.

The new document can be found here ---> http://www.box.net/shared/p3enk90t00

Thanks for your help, and if anyone can think of a way to sort out the scheme code problem I would be really grateful.

 

[hiker95]

HolyLemon,


Sample worksheet with nothing in cell B4:

Excel Workbook
	A	B	C	D	E	F	G
1	Member search
2
3	Scheme Code
4	Member Number
5
6
7	Name					MGA (Assessment)Info	?
8	Current MG Category					Name of Assessor
9	Date of Referral					Date Assessment Completed
10	Referral Route					Assessment Score
11	Agreed PRMP?					Outcome of MGA
12	Retired/Deferred?					Condition1
13	For Assessment?					Condition2
14	For reassessment?					Condition3
15						Condition4
16
17	Date MG01 Sent?
18	Type of letter sent?
19	Date letter sent?
20
Member Search

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9	1. / Formula is =MOD(ROW(),2)=0	Abc
G9	1. / Formula is =MOD(ROW(),2)=0	Abc
B17	1. / Formula is =MOD(ROW(),2)=0	Abc
B19	1. / Formula is =MOD(ROW(),2)=0	Abc

For several "Member Number", including on worksheet "Proposed MGP3" the pulldowns for "Scheme Code":

Excel Workbook
	A	B	C	D	E	F	G
1	Member search
2
3	Scheme Code	MAM
4	Member Number	212893
5
6
7	Name	hiker95				MGA (Assessment)Info	?
8	Current MG Category	Standard Risk				Name of Assessor	PWM
9	Date of Referral	2/1/2001				Date Assessment Completed	8/24/2007
10	Referral Route	MCD				Assessment Score	100
11	Agreed PRMP?	Yes				Outcome of MGA	Active
12	Retired/Deferred?	Y				Condition1	Member Risk Assessment
13	For Assessment?	Y				Condition2	N/A
14	For reassessment?	N				Condition3	Inactive
15						Condition4	Medical Records Audit
16
17	Date MG01 Sent?	2/1/2001
18	Type of letter sent?	No letter
19	Date letter sent?	5/21/2010
20
Member Search

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9	1. / Formula is =MOD(ROW(),2)=0	Abc
G9	1. / Formula is =MOD(ROW(),2)=0	Abc
B17	1. / Formula is =MOD(ROW(),2)=0	Abc
B19	1. / Formula is =MOD(ROW(),2)=0	Abc

Excel Workbook
	A	B	C	D	E	F	G
1	Member search
2
3	Scheme Code	CSA
4	Member Number	118118
5
6
7	Name	HolyLemon				MGA (Assessment)Info	?
8	Current MG Category	Inactive				Name of Assessor	IS
9	Date of Referral	1/19/2010				Date Assessment Completed	8/30/2007
10	Referral Route	MCD				Assessment Score	100
11	Agreed PRMP?	No				Outcome of MGA	Active
12	Retired/Deferred?	Y				Condition1	Condition
13	For Assessment?	Y				Condition2	Condition
14	For reassessment?	Y				Condition3	MTA
15						Condition4	CCP
16
17	Date MG01 Sent?	1/19/2010
18	Type of letter sent?	Other
19	Date letter sent?	5/27/2010
20
Member Search

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B9	1. / Formula is =MOD(ROW(),2)=0	Abc
G9	1. / Formula is =MOD(ROW(),2)=0	Abc
B17	1. / Formula is =MOD(ROW(),2)=0	Abc
B19	1. / Formula is =MOD(ROW(),2)=0	Abc

For data to be filled in on worksheet "Member Search", worksheet "Proposed MGP3" must have data in the respective field/column (manually entered like "Assessment Score", or choices for the data validation fields).

is there a way to make it check for a scheme code aswell

Done.


What column on worksheet "Proposed MGP3" does "MGA (Assessment)Info" refer to?

 

[HolyLemon]

Thanks very much, what you've done is fantastic, I think I must have explained myself poorly though. I need to be able to search using both the Scheme Code and the Member Number, problem is they are in different boxes on both forms. I dont know if an And/If function would do it, but I have no idea how to properly edit your work.

As always, your help would be very much appreciated.

 

[hiker95]

HolyLemon,

Your cell A23 displays a 0 if cell B4 is blank.

You may want to change the worksheet "Member Search" formula in the merged cell A23 to:

=IF($B$4="","",IF(INDEX('Proposed MGP3'!$AD:$AD,MATCH($B$4,'Proposed MGP3'!$F:$F,0))=0,"",INDEX('Proposed MGP3'!$AD:$AD,MATCH($B$4,'Proposed MGP3'!$F:$F,0))))

 

[hiker95]

HolyLemon,

I need to be able to search using both the Scheme Code and the Member Number, problem is they are in different boxes on both forms.

Please explain in more detail.

 

[HolyLemon]

This spreadsheet is a list of worldwide members, each member has a member number (column F). If a member is in a country other that England or the United Kingdom, they have a Scheme Code (column E) it is possible for there to be two or more members with the same number, but one of them wont have a scheme code and the others will.

On the spreadsheet I uploaded, search for 123456

It will come up with Rachmaninov with a scheme code of SA.

In the Proposed MGP3 tab there are infact two members with the membership number 123456, the other is Mozart who doesnt have a scheme code because "he" would be based in the UK.

If it could be possible to search the "Proposed MGP3" by both columns E and F it would eliminate the problem.

Your help would be very much appreciated (again) lol

thanks

 

[hiker95]

HolyLemon,

The approach I was taking worked, until you just mentioned duplicates for Member Number.

You could probalby use VLOOKUP, or VLOOKUPNTH, if you changed the structure of worksheet Proposed MGP3 (swapping columns D and F).

Function VlookupNth
by jonmo1
http://www.mrexcel.com/board2/viewtopic.php?t=302681&sid=81cc9a966bb7e6a4ca2c63523b1396b7


Click on Post Reply and just enter BUMP and someone else will assist you.