Pulling Values From A List

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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

5 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
you can use ficititious data and post a small extract
Column 1: Dog, Dog, Dog, Cat, Dog, Dog, Bird, Dog, Cat, Bat, Dog, Dog
Columns 2: Pitt, Chihuahua, Schnauzer, <blank>, Basenji, Weimerauner, <blank>, Poodle, <blank>, <blank>, Bulldog, Rottweiler

I need just the names of the dog breeds in a nice list with no blanks.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.
I'm not so concerned with the "simplest" method, though. I'm trying to set this up for someone who doesn't have a clue how to use Excel except to get basic numbers out of it. Is there no way to do it?
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.
I'm not good with VBA and macros, so go easy on me. I'm fine with cutting and pasting and some modification as long as you can comment the sections that would need to be modified.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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?.
Ok, let me be more clear:

This spreadsheet is updated on a daily basis, often several times per day. It is linked to a PowerPoint presentation that is automatically updated whenever new data is entered into the worksheets. To copy and paste the information every single time I need to reference it would kill most of the automation I'm doing, which defeats the point of the spreadsheet in the first place.

So, in summary, I need a formula or a macro or a magical incantation that will go down one column until it finds the text string "Dog" and then return the value of the related column "Breed" and put it into a nice list with no gaps on rows that don't match.

I want it automated so that it can also update my PowerPoint presentation when the list is created. I am trying to make Office do as much of the work as possible with as little input from me or other users as possible once I've finished my part of the project. It seems, more and more, that I will have to result to black magic and the sacrificing of many young goats to accomplish this, but I'd rather just do it in Excel and be done with it.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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.