Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop dns driver drivers error ethernet excel freeze gaming graphics hard drive hardware hdmi internet laptop malware memory monitor motherboard network printer problem ram registry repair router slow software sound trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Filtering a worksheet

Reply  
Thread Tools
PincivMa's Avatar
Member with 263 posts.
 
Join Date: Mar 2004
11-Nov-2009, 10:20 PM #1
Filtering a worksheet
Hi there

I know that this is a stupid question, since I can easly do it manually by filtering data. I have a client who is not computer savi. She wants me to do a program for her that filters her data. Now get this!! She wants to put the name of the data to be filtered in cell A1. Say that she wants to filter all the last names called "Smith". So in Cell A1, she types in Smith. All the last names are in column C. Then she wants to click on a button to run the macro that filters the data. Is there a macro that can pick up the word Smith in cell A1and somehow use that name to filter the data in column C??

Mario
Aj_old's Avatar
Computer Specs
Senior Member with 869 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
12-Nov-2009, 04:47 AM #2
It could be done, but:
1. She wanna always filter by last name?
2. She wanna filter only by one name or criteria, or it can be multiple names, multiple criteria?
3. What's the range to be filtered? Is it always the same?
4. The data structure is always the same?
5. Is it only one file, or she would like to use it for more files?
6. How about using auto filter, it is very easy and powerful?
__________________
“I hear, I know. I see, I remember. I do, I understand.” (Confucius 551 BC – 479)
PincivMa's Avatar
Member with 263 posts.
 
Join Date: Mar 2004
12-Nov-2009, 10:43 AM #3
She wants to type a first name in Cell A1 and a last name in Cell B1. These names change depending on which name she wants to filter. The list of first names are in Column C and the list of last names are in Column D. There is other data in columns E,F,G and H. She wants to filter by first name first and then by last name.

I did do a macro that copies sheet1, which has the list of names, into sheet2. Then in sheet two the macro continues to delete all of the names that are not associated with those she inputs into Cells A1 and B1. The problem is that it takes a very long time, since the list of names is very long.

Do you have a macro that can shorten the time element? If not, I'll tell her to bite the bullet and use the Excel auto filter.
Aj_old's Avatar
Computer Specs
Senior Member with 869 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
12-Nov-2009, 10:51 AM #4
So, you are talking about a single file, which I suppose, should always have the same structure.
To do a macro one needs to know the range that should be filtered, and another very important aspect, is if she wants the filtered data on the same sheet (to filter in place) or she wants the results on a different sheet for each filter operation. In the second case one needs to know what should be the name of that sheet.
__________________
“I hear, I know. I see, I remember. I do, I understand.” (Confucius 551 BC – 479)
Aj_old's Avatar
Computer Specs
Senior Member with 869 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
12-Nov-2009, 10:52 AM #5
By the way, if you can, please upload the file with any sensitive data removed, and replaced with some dummy date. We just need to know the structure of the data.
PincivMa's Avatar
Member with 263 posts.
 
Join Date: Mar 2004
12-Nov-2009, 03:30 PM #6
I did a ficticious worksheet with the structure in tact. Sheet1 has the original data while sheet2 has a copy of the original data starting in Cell C1. She puts the names to be filtered in Sheet2 in Cells A1 and B1. Then by running a macro the data in sheet2 gets filtered.

I forgot to put the original data in sheet2 starting in Cell C1. But you get the idea.
Attached Files
File Type: xls Testing.xls (15.5 KB, 38 views)
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
13-Nov-2009, 04:13 AM #7
Sheet 2 has nothing in it.
Aj_old's Avatar
Computer Specs
Senior Member with 869 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
13-Nov-2009, 04:32 AM #8
Take a look at the attached workbook.
Attached Files
File Type: xls Copy of Testing.xls (37.0 KB, 41 views)
PincivMa's Avatar
Member with 263 posts.
 
Join Date: Mar 2004
13-Nov-2009, 11:43 PM #9
Thanks Aj_old. WOW!!! That is some mean programming. I understand some of it but not all of it but it does work. I will try and break down the code line by line to make sense of it. If I get stuck on a line of code, I will get you to explain it to me, if you don't mind.

Thanks again.

Mario
Aj_old's Avatar
Computer Specs
Senior Member with 869 posts.
 
Join Date: Sep 2007
Location: Moldova
Experience: Intermediate
14-Nov-2009, 05:16 AM #10
You are welcome.
Of course if you have any questions, just ask, I'll be glad to help.
PincivMa's Avatar
Member with 263 posts.
 
Join Date: Mar 2004
15-Nov-2009, 07:36 PM #11
Hi Aj_old

I used your filtering macros and they worked real well on the names portion of my workbook. However, in the parts section of another workbook, I'm aving problems. Attached is the parts workbook with your macros. This workbook has 4 worksheets. The third worksheet works like a charm. The fourth worksheet does not work at all. First of all, I get a bug on the line that says "b.cells(1, 3).PasteSpecial xlPaste Value". I then realized that the copy area was from A1 to IV1, so the paste method failed. I then cleared the Receiving sheet from Columns H to Colums IV. When I ran the macro again, I got the message box. But I do know that CT and 0079558 does exist. Can you fix these two errors for me??

Thanks,

Mario
PincivMa's Avatar
Member with 263 posts.
 
Join Date: Mar 2004
15-Nov-2009, 07:45 PM #12
I will try to upload the file again!! Hop it works this time!!!
Attached Files
File Type: xls Filtering Data1.xls (82.0 KB, 33 views)
Reply

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 07:58 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.