Fromula meeting two conditions

August 19, 2010 at 08:33:20
Specs: Windows XP
How can I lookup for multiple values and then paste the data accordingly. It can be a formula or macro. For example,

A1 B1 C1 D1

Hispanic White
Male Female Male Female Male
50 100 5 165
I need to copy and paste Hispanic Male with the data in another table.


See More: Fromula meeting two conditions

Report •


#1
August 19, 2010 at 09:01:05
Hi,

Its not clear how your data is organized.

If column A is Origin e.g. Hispanic
column B is color and column C is sex,

then select the three columns, and apply the data filter - in Excel 2003 and earlier its Data - Filter - Auto Filter

Then select the criteria for each column using the drop-down lists at the top of each column that Auto Filter adds for you.

As you apply each filter - Column A, then B then C, you will be left with rows that show only the selected characteristics, and you can then copy and paste them.

Regards


Report •

#2
August 19, 2010 at 09:16:06
Thanks for the reply,

My data is organized as follows"

Cell A1 "Hispanic" takes two columns and is not merged.
Cell A2 "Males
Cell B2 "Females"
Cell A3 "Value" Eg. (10, 20 , 30, etc...)
Cell C1 "White" takes two columns and is not merged.
Cell C2 "Males"
Cell D2 "Females"
Cell C3 "Value" Eg. (10, 20 , 30, etc...)

I need to extract data to another table with the same column headers but need a formula to lookup the values that meets the criteria since column headers might not exist in previous table.


Report •

#3
August 19, 2010 at 09:29:11
Thanks for the reply,


Report •

Related Solutions

#4
August 19, 2010 at 11:18:57
Hi,

So your data looks like this:

	A	B	C	D
1	Hispanic	White	
2	Males	Females	Males	Females
3	50	100	5	165

You said How can I lookup for multiple values and then paste the data accordingly

but now you say I need to extract data to another table with the same column headers but need a formula to lookup the values that meets the criteria since column headers might not exist in previous table.

Questions:
1. When you say 'extract data to another table' - is this table a range of cells on this worksheet or somewhere else
2. Does this table already exist or is it just a range of cells with nothing in them.
3. What are the criteria for extracting the data
4. Do you have multiple rows of data in columns A to D.
5. Do you have data in more columns that you have not mentioned.

Regards


Report •

#5
August 19, 2010 at 13:29:39
Thanks Humar,

That is exactly how the table looks. The worksheet has two tables with the same headers one table from A1 to A4 and the other starts at A8 and so on.

1. When you say 'extract data to another table' - is this table a range of cells on this worksheet or somewhere else

The two tables are on the same worksheet one starts atA1 and the other one at A8. This later table would always have the same headers.

2. Does this table already exist or is it just a range of cells with nothing in them.

The table exists with the same headers but the table that starts at A1 sometimes does not have a race or only one sex therefore column headers change.

3. What are the criteria for extracting the data

The criteria is if there is a column with race and sex copy and paste that value on the respecive cell in the second table if not then value is zero.

4. Do you have multiple rows of data in columns A to D.

I have only 4 rows on each table and from A to Z columns on the first table and from A to Q in the second table.

5. Do you have data in more columns that you have not mentioned.

After each race column with male and female there is a total column for each race.

I hope this answers your questions. Too bad this site does not let you upload the file so you can see what I am working on.

Again thanks for looking into this.


Report •


Ask Question