Pulling Values From A ListClosed

Question

I have an open-ended Excel SS that I need to do a few things that I can't quite figure out.  Due to the uses of it, I can't post it here, but I'll attempt to thoroughly describe what I want it to do:

First, it lists names, dates, dob, animal types, and breeds based on the type (for example, if column M=dog, then column N=dogbreed or M=cat, N=catbreed, etc).  I need to find a way that I can have Excel sort through all entered data and only pull out the dog breeds or cat breeds or whatever breeds and put them into a list with no blank cells in between each other.  I've been looking and thinking for two days and can't find a way to do it.  For security reasons, my environment isn't really macro-friendly, but if I must use them I will.  The list will be used in a PowerPoint slide that is directly linked to the table that is created by this formula/macro.

Second, using the same spreadsheet, I'd like to perform a selective mail merge and send out letters to people based on a cells information. For example, if Column A="Yes", then I want mash a button and export the useful data into an already-created Word form letter and print it off.  (I know I have to hit the "print" button and I'm ok with that, but you get the idea....)

There are other, smaller problems that I'm working on, but these are the two I just can't find answers to and I've been looking for a week.

Thanks in advance!

-- jason

venkat1926 · Answer

you can use ficititious data and post a small extract

venkat1926 · Answer

simplest method is copy column B in some other non adjacent column for.e.g from E1 down. sort columns E ONLY descending order with no headers . Blanks will come  last and you will not know.

venkat1926 · Answer

if your user cannot do this simplest procedure how can she do complicated procedures.  Of course a macro can be written and button created to which this macro can be assigned. How is she going to incorporate the macro and create a button unless you do that and send it to her.  Whenever a user use excel she should learn simplest methods and procdeures. 

anyhow if you are comfortable with macro and button post back.

venkat1926 · Answer

I shall try to help you in giving you step by step procedure I have told already.
I am assuming the cells you have marked as <nlank> are really blanks and the word blank is not entred there 

1. Highlight the data in column B
2. click edit-copy
3. select cell E1.
4.click edit-paste 
5. Highlight the data in column E from E1 down. (remember E1 is data and not header).
6.go to DATA(menu bar)-click sort 
7. in sort window sort by column E must be already there
8. in sort window "descending" by the side of this 
9.choose "no header row" in the bottom
10.click ok 

do you get what you want?.

venkat1926 · Answer

your data is like this from A1 to B12

Dog	Pitt
 Dog	 Chihuahua
 Dog	 Schnauzer
 Cat	
 Dog	 Basenji
 Dog	 Weimerauner
 Bird	
 Dog	 Poodle
 Cat	
 Bat	
 Dog	 Bulldog
 Dog 	 Rottweiler 

try this macro which copies the breeds of dogs in column E. if you want to change the location copying modify the macro (the macro first clears the column E.
data in column is not having any blank. (B can have)

the macro is 

Sub dest()
Dim r As Range, c As Range
Dim j As Integer
Columns("E:E").Delete
j = 0
Set r = Range(Range("A1"), Range("A1").End(xlDown))
For Each c In r
If Trim(c) = "Dog" Then
c.Offset(0, 1).Copy
Range("e1").Offset(j, 0).PasteSpecial
j = j + 1
End If
Next c
End Sub

you can create a button from form toolbar and assign this macro to that button.

Pulling Values From A List

5 responses

Similar discussions

Subscribe To Our Newsletter!