Enormous list of column headers and multiple criteria sort

[DSH]

I am looking to use a macro to go through a spreadsheet and find over 72 phrases (column headers) that are indicated positive in each row.

To use an example lets say we zoo names which are indicated by a column header. Continuing across the top row of headers are animal names. I want to find 72 specific animal names, even though there may be over 100 names, that are indicated positive. I am using the number one to indicate that an animal would be present at this zoo.

What I want the macro to do is create a new column header at the end of the existing column headers and indicate a positive using the number 1 if any of the 72 animals were present for that particular zoo on the row that the search is being done on and continue on down the list.

Is it possible to list 72 headers for a search and if so how would I go about creating a macro like this? Thanks

 

[mirabeau]

Is it possible to list 72 headers for a search and if so how would I go about creating a macro like this? Thanks

Yes! It should be very possible.

But would you like to post an simplified example of the data layout for your problem, and the desired result?

 

[DSH]

Here is a very small spreadsheet. I am looking to add the column in red. For this example if tigers, elephants, or giraffes are present (1) then a 1 is shown in the new column. I need to do this for 72 different header names. Thanks.

Excel Workbook
	A	B	C	D	E	F	G	H
1		Lions	Tigers	Bears	Alligators	Elephants	Giraffes	Present
2	Boise	1	1	1		1		1
3	Columbus	1	1	1	1		1	1
4	Raleigh	1		1
5	Bismarck	1	1		1			1
6	Sarasota		1		1			1
7	Topeka	1		1	1
8	Minnesota		1		1	1	1	1
9	Sacramento	1		1	1
Sheet1

 

[mirabeau]

Here is a very small spreadsheet. I am looking to add the column in red. For this example if tigers, elephants, or giraffes are present (1) then a 1 is shown in the new column. I need to do this for 72 different header names. Thanks.

Why tigers, elephants and giraffes, and not say lions, bears and giraffes?
Are you wanting to look at all possible permutations of the header names?

Or are there other criteria for selecting these?

 

[DSH]

I am looking for specific headers and whether any of those specific headers have a 1 in it for each row. The other headers mean nothing to me. The only way to identify them is by the header name as I am not sure that each time I use a different sheet that the order will be the same.

 

[mirabeau]

What I understand by "headers" are the items (text/numbers) in the top row of each column.

In your example these seem to be Lions, Tigers, Bears ... Present.

None of these headers has a 1 in it, so I don't see what your'e getting at.

Do you mean something else by "headers"?

Or, if you are referring to a 1 occurring anywhere in any of the cells in any of the columns, you just want a 1 put in that row in the "Present" column??

 

[DSH]

The header is not going to have a number. The header is the name of an animal as shown in row 1 in the example.

Of the 6 headers, I want to know if the 3 specific headers (tigers, elephants or giraffes) have a 1 in it by looking across the row below the header. This I want to do for each row looking across.

After adding a new header to the end of the current headers (Present), If any of the 3 headers have a 1 in it for a row then I want to put a 1 in the new column for that row that is being looked at.

With the example given, Raleigh and Sacramento have no 1 placed in the new column (Present) because although there were 1's in those rows looking across they were not under any of the headers of interest.

I need to do this for 72 different headers in a spreadsheet of over 100 possibilites, down a large number of rows.

 

[mirabeau]

So, you have a list of 100 animals which are the headers of columns 2 to 101. Yes?

You have 1's scattered in many of the cells in a lot of rows beneath these headers (and blanks otherwise)? Yes?

You somehow make a selection of 72 animals from the 100? Selection made randomly? taken from a list either somewhere in your workbook (where?), listed in an array specified in the macro? inputted by input box or similar? all possible ways of selecting 72 items from 100 (now that would be a significant problem)? other means of deciding on the 72? Yes? No?

And if a 1 appears in any of the columns below any one of the somehow chosen 72 headers then you want a 1 put in that row in a 102nd column that is headed Present?

Is any or all of this what you want?

 

[DSH]

Yes you have it correct for what I am looking for.

I am wondering if there is a way to list the 72 animal headers in a macro. My thought was to list the headers in an array in the macro but unsure if 72 is possible.

 

[mirabeau]

OK. Good that I seem to now have it right.

I can't do anything for next 3-4 hours (am otherwise tied up) and I'll post a macro for you then if nobody else provides an adequate one in the meantime.